Adding new columns to tables in SQL Server is a common task for anyone developing and/or maintaining databases, usually driven by the need for new features on an application/script procedure.

As ever, before you get started with MSSQL changes, have a look at the relevant Microsoft Documentation – Add Columns to a Table

If the SQL Server instance has Replication configured have a look at MS Docs: Considerations for Schema Changes & Replicate Schema Changes
By default, if we add a column it will be propagated through to Subscribers. If you are adding default values you should be mindful of Replication Latency.

The following is included in this post:
# Add Column to Table
# Add Column to Table with Default Value

Add Column to Table MSSQL

Adding a column to a table is done using the ALTER TABLE statement. Here is an example of adding a column with the VARCHAR Data Type. The new column must allow NULL values or include a default constraint.

-- Add column to table ms sql
ALTER TABLE sqlBlog ADD tag VARCHAR(60);

We can run a quick SELECT TOP 1 * on the table to verify the new column exists. If this table contains sensitive data then we can also query sys.columns.

Add Column to Table with Default Value

As mentioned above in this post, if the SQL Server instance has Replication configured we should refer to Microsoft Docs prior to making this change. If you are adding columns with default values, you should also be mindful of Replication Latency and Transaction Log file sizes.

The following shows adding a column with the default INT value of ‘12345’. Before doing this I inserted some more rows into the demo table.

-- Add column to table with default value
-- CONSTRAINT = Optional constraint name
ALTER TABLE sqlBlog ADD epmv INT CONSTRAINT epmv_def DEFAULT 12345 WITH VALUES;

Each existing row has had this ‘epmv’ value added as displayed above.