Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Altering an int column to idenity in existing published table Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 12:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:41 AM
Points: 114, Visits: 903
Can I alter an existing int column to an identity in a table that is already published in transactional replication?

Thanks,
DK
Post #1498540
Posted Wednesday, September 25, 2013 1:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 13,253, Visits: 12,087
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1498576
Posted Thursday, September 26, 2013 12:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:41 AM
Points: 114, Visits: 903
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
Post #1499012
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse