Altering an int column to idenity in existing published table

Question

Altering an int column to idenity in existing published table

DKlein

SSCrazy

Points: 2351
More actions
September 25, 2013 at 12:32 pm

#283631

Can I alter an existing int column to an identity in a table that is already published in transactional replication?
Thanks,
DK

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

DKlein (9/25/2013)
Can I alter an existing int column to an identity in a table that is already published in transactional replication?
Thanks,
DK

Technically speaking you can't change the identity property of an existing column (regardless of replication). If you use the UI for this the script behind the scenes actually creates a new column, copies all of the data, drops the original column and finally renames the new column to the same name as the original.

I have never tried this on a replicated table so I am not certain what might happen there. I would certainly NOT do this in production as a test. Try this on a dev server. I am curious myself what happens here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DKlein SSCrazy Points: 2351 More actions · Answer 2

Sean,

Thanks for the reply. I decided against going forward with this as every option seemed to involve reinitialization of the subscriber. On top of that Microsoft does not support adding an identity column to a published table:

"Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows."

Thanks again,

DK