1

In SQL Server, you can set the increment of an identity column other than 1.

In what situation would you do that?

enter image description here

| improve this question | |
  • 5
    When you want to increment by more than 1? – Mark Sinkinson Jul 17 '15 at 13:05
  • 4
    In master-master replication you can set increment value to count of nodes and then assign each node different start, so each one will generate unique sequence. – jkavalik Jul 17 '15 at 13:13
  • 3
    For whatever reason you want...It's your system! This question is a matter of opinion and is off-topic for this site. – Mark Sinkinson Jul 17 '15 at 13:24
  • 3
    Another reason might be to prevent guessing of the next value. E.g. if I create a new account, and I see my ID value somewhere, I might be able to guess the next account number. Not if the increment is 42 or 7 or 13, and if a script changes it randomly as well. This is security through obscurity, for sure, and relies on there being some exposure via only ID values (which should never happen), but people do worse... – Aaron Bertrand Jul 17 '15 at 14:37
  • 3
    It could also be to leave gaps for future growth (let's say people want their categories to be both numerically and alphabetically increasing), or to allow for greater precision (a calendar table that has IDs for days, that later expands to support 8-hour shifts). – Aaron Bertrand Jul 17 '15 at 14:39
5

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.

| improve this answer | |
  • 1
    Well, to be pedantic, you'd have to set the seed differently (one odd, one even, e.g. 1 and 2) but the increment the same (e.g. 2), otherwise you'll get collisions. I'm sure that's what you meant... – Aaron Bertrand Jul 17 '15 at 14:36
  • @AaronBertrand Yeh .. thats what I meant. The Identity seed should be different. – Kin Shah Jul 17 '15 at 14:43
0

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
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.