| Author |
Topic |
|
MangoSkin
Starting Member
22 Posts |
 Posted - 2010-10-19 : 12:23:50
|
| I have a table which has an Identity column. Now I want to add another column which should do identity increment at each insert.This table is used in a merge replication as well. How can I approach this?Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 12:25:42
|
You can't have two identity columns.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MangoSkin
Starting Member
22 Posts |
Posted - 2010-10-19 : 12:34:30
|
| I want a column which is say 1000,1001,1002.....The current Identity column is gives me inconsistent values based on the database in the merge replication.database A gives me Id values ranging from 4001,4002,...database B gives me Id values ranging from 20001,20002,....I need a column which increments by 1 starting from 1000 and all participating replication databases should follow this rule. Each database should not make its own identity range. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-10-19 : 12:40:18
|
| its not possible to have more than one identity column in same table. you might need to use a computed column for getting the consistent set of values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MangoSkin
Starting Member
22 Posts |
Posted - 2010-10-19 : 13:25:47
|
quote:
Originally posted by tkizer
For merge replication, you set aside the identity values for each one. You do not need a computed column. So you start the identity option at the predetermined range. You have to be careful of collisions, so ensure you've accounted for many months and years of data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Suppose I have a publisher A and two subscriber B,C.What should be a sample range on each if I want increments from 1000? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MangoSkin
Starting Member
22 Posts |
Posted - 2010-10-19 : 17:11:47
|
| I will explain this a bit more clearly.If I have a single database situation.I can use a single table and use an identity column with 1000 as start and 1 as increment.I want exactly the same data in the table when I have n number of databases in merge replication.So if I insert the first record at database A the record will have 1000 as ID and the next insert happens at some nth database and it should insert 1001 as the identity.Every time an insert happens it should insert with an increment of 1 to the highest existing ID value regardless of location.Is this possible using an Identity column with more than 10 databases in merge replication? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Identity column