I have a need to add a new column to a published table. On SQL 2016 (sp2 - cu17).
I remember the sp from a while back sp_repaddcolumn. That is showing as deprecated.
MS doco shows that simply Alter Table Add [columnname> type null would work and automatically send that to subscriber.
1 - My reading indicates best to make it NUllable.
2 - Are there any gotcha's with this Alter Table approach for replication that you have had?
Replies on 1 and 2 would help immensly.
SQL ServerA family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,660 questions Sign in to follow 1 comment Hide comments for this question Report a concern I have the same question I have the same question 0Hi @Mark Gordon , Agree with Pituach.
If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
I remember the sp from a while back sp_repaddcolumn. That is showing as deprecated.
True. By the way, I would not recommend to use it but it should work even in SQL Server 2019 if the data type of the column is one that was supported in 2000 :-)
MS doco shows that simply Alter Table Add [columnname> type null would work and automatically send that to subscriber.
1 - My reading indicates best to make it NUllable.
note accurate :-) The only reason that you need the column to be nullable is since existing rows will have no value in that column. You can add new column with default value instead of nullable as well. The basic idea is the same in simple table without any replication.
use tempdb GO drop table if exists T GO CREATE TABLE T (id1 int null) GO insert T(id1) values (2),(4) GO ALTER TABLE T ADD id2 int NOT NULL GO -- ERROR: ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'id2' cannot be added to non-empty table 'T' because it does not satisfy these conditions. -- This should work well: ALTER TABLE T ADD id2 int NOT NULL default 0 GO When using a simple table without replication the limitation can be more flexible since it does not depend on the replicated table, but the basic idea is the same: if you add column which configured as NOT NULL to existing table, then it can be problematic if there are already rows with data since the value in the new column cannot be NULL, so what should the server fill ? The answer is that using default solve the issue since it configure the value which will be filled in the existing rows.
2 - Are there any gotcha's with this Alter Table approach for replication that you have had?
It should works well if you keep the simple rules. Check the link above to the documentation for limitations, but simply adding a column (with default value or NULL should works by deafult)
