MSSQL%20Interview%20Questions%20and%20Answers

Question: How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD" in MS SQL Server?

Answer: If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD" statement. The tutorial script below shows you a good example:

ALTER TABLE test ADD author VARCHAR(40)
GO

sp_columns test
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo test id int ...
dbo test subject varchar ...
dbo test description varchar ...
dbo test create_date datetime ...
dbo test author datetime ...

SELECT * FROM test
GO
id subject description create_date author

This SQL script added a new column called "author" to the "test" table. NULL values were added to this column on all existing data rows.