Question

How do I add a column to an existing table in a SQL Server database?

In my scenario I am adding a column of type int with a default value of 0.

2 Answers

Here is the generic script you are looking for.

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

In your scenario you may have something like this:

ALTER TABLE [User]
ADD [LoginCount] INT NOT NULL
CONSTRAINT LoginCountConstraint DEFAULT 0

Alternatively, use SQL Server Management Studio. Simply right click on the table and select Design. Then add the column to the list with a default value.

This is the simplest way in my view.