In SQL Server, you can set the increment of an identity column other than 1.
In what situation would you do that?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityIn SQL Server, you can set the increment of an identity column other than 1.
In what situation would you do that?
I have used identity increment other than one, especially in MERGE REPLICATION in different regions.
E.g. We have Publisher in NY and Subscriber in LD involved in Merge replication.
We set Publisher's Identity increment to ODD and LD server's identity increment to EVEN. This way you dont get conflicts where MERGE agent runs and tries to merge the changes.
I have talked here with a script that helps determine identity.
Identity Increment Column property really just gives you more control on the designing aspect to allow you to suite a particular need you may have (all based on architects methods of design) it may also be used to synchronize data in the future if you ever find a need for that, although there are more effective and user friendly ways to do that..
The IDENTITY SEED = 1 is default assuming the table has no data (rows) attached to it, but the Identity Seed is simply which ID (PK) to start off with, then the Increment starts off based on the SEED property.
To give you an example with column properties > Indentity Specification (IS Identity) YES Identity Increment (5) Identity seed (20)
INSERT INTO TRAFFIC_LIGHT_TAB (COLUMN2, COLUMN3)
VALUES ('GREENLIGHT','GO')
INSERT INTO TRAFFIC_LIGHT_TAB (COLUMN2, COLUMN3)
VALUES ('YELLOWLIGHT','SLOWDOWN')
INSERT INTO TRAFFIC_LIGHT_TAB (COLUMN2, COLUMN3)
VALUES ('REDLIGHT','STOP')
THE OUTPUT WOULD BE:
COLUMN1 | COLUMN2 | COLUMN3
20 | GREENLIGHT | GO
25 | YELLOWLIGHT | SLOWDOWN
30 | REDLIGHT| | STOP