Adding a column to a replicated table.

[TheBugSlayer]

Please help me elucidate the below:

1.
Imagine you need to add a column to a table, but the table is replicated too: do you execute the normal ALTER TABLE statement and then SP_REPLADDCOLUMN to add the column to the table first then to the replication (article?), or does SP_REPLADDCOLUMN alone take care of both?

2.
In a situation where you also need to add a constraint to the column you just added, I don' t see how SP_REPLADDCOLUMN would add that constraint, which makes me lean towards executing both statements.

3.
According to BOL, SP_REPLADDCOLUMN "adds a column to an existing table article that has been published"...What is not clear to me is whether an article is the same table that is published- with a row in the SYSARTICLES system table, or whether it sits separately as another table.

Thanks for your help.

 

[SQLBill]

I do this frequently. Just run the sp_repladdcolumn on the publisher and it will also add it to the subscribers. As for the constraint, I would have to know what type of constraint. Normally you wouldn't want a constraint on a subscriber. For example, you have a default where GETDATE() is added. You want that done on the publisher, but not on the subscriber since you want the value on the publisher to be replicated.

So normally, you would run sp_repladdcolumn with just the basic information. Then on JUST the publisher run the ALTER statement to add the constraint to only the publisher.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[TheBugSlayer]

Thank you SQLBill.

So the constraint needs to be added just on the publisher. What if you also have CRUD on the subscriber (merge replication comes to mind)?

I gather the table becomes the article once it is publish, correct? So no other table is created, it is the same table but an entry is created in the sysarticles table and all other places relevant to the replication.

The constraint is a DEFAULT one.