Add Column in SQL Server

Question

I need to add a column to a SQL Server table. Can I do this without losing data, do I already have it?

Answer 1

Sure! Just use ALTER TABLE... syntax.

Example

 ALTER TABLE YourTable ADD Foo INT NULL /*Adds a new int column existing rows will be given a NULL value for the new column*/ 

or

 ALTER TABLE YourTable ADD Bar INT NOT NULL DEFAULT(0) /*Adds a new int column existing rows will be given the value zero*/ 

In SQL Server 2008, the first of these is a change in metadata only. The second will update all rows.

In SQL Server 2012+ Enterprise, the second option only changes metadata .

Answer 2

Use this query:

 ALTER TABLE tablename ADD columname DATATYPE(size); 

And here is an example:

 ALTER TABLE Customer ADD LastName VARCHAR(50); 

Answer 3

Adding a column using SSMS or ALTER TABLE .. ADD will not delete existing data.

Answer 4

Add a new column to the table

 ALTER TABLE [table] ADD Column1 Datatype 

eg

 ALTER TABLE [test] ADD ID Int 

If the user wants him to increase automatically,

 ALTER TABLE [test] ADD ID Int IDENTITY(1,1) NOT NULL 

Answer 5

Add a new column to the table with the default value.

 ALTER TABLE NAME_OF_TABLE ADD COLUMN_NAME datatype DEFAULT DEFAULT_VALUE