Menu & Search
Adding Columns to a Table in SQL Server

Adding Columns to a Table in SQL Server

Adding new columns to tables in SQL Server is a common task for anyone developing 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

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
ALTER TABLE sqlBlog ADD tag VARCHAR(60);
SQL Server Add Column

Run a SELECT TOP 1 * on the table to verify as I’m doing above, or you can look at sys.columns.

Add Column to Table with Default Value

As mentioned above in this post, if the SQL Server instance has Replication configured have a look at MS Docs. If you are adding columns with default values you should also be mindful of Replication Latency and TLog file size.

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;
Add Column to Table with Default Value

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

Share

0 Comments