There’s a limit to what changes you can make to tables and other objects in a database which is being replicated. Fortunately the restrictions aren’t that great; here’s a quick reference to what is and is not supported.
– Supported syntax
alter table Test1 alter column RowValue varchar(20) — Changing column definition
alter table Test1 add NewColumn int null — Adding a nullable column to the end of the table
alter table Test1 drop column NewColumn — Dropping a column
alter table Test1 add NewColumn int not null default 0 — Adding a non-nullable column with a default
alter table Test1 drop constraint DF__Test1__NewColumn__5DCAEF64 — Dropping a default constraint
alter table Test1 drop column NewColumn — Dropping a column after dropping its default constraint
alter view vwTest1 as select RowID from Test1 — Changing a view definition
alter procedure uspTest1 as select RowID, RowValue from Test1 — Changing a stored procedure definition
– alter function and alter trigger are also supported
– Unsupported syntax
alter table Test1 add NewColumn int not null — Adding a not-null column with no default
alter table Test1 add NewColumn int identity — Adding an identity column (Also an identity column cannot be dropped)
alter table Test1 drop constraint PK__Test1__FFEE74517F60ED59 — Dropping a primary key
alter table Test1 add constraint PK_Test1 primary key clustered (RowID) — Adding a primary key
Some things to remember:
- Writing your own ALTER statement will allow you to do more than generating a script from the table designer (which often likes to create a temp table, move the data into it, drop the table then rename the temp).
- Always execute your code on the publisher, not the subscriber!
