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.
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);
Run a SELECT TOP 1 * on the table to verify as I’m doing above, or you can look at sys.columns.
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;
Each existing row has had this ‘epmv’ value added as displayed above.