When would you ever set the increment value on a database identity field?

Question

Given the table:

CREATE TABLE Table1
(
    UniqueID int IDENTITY(1,1)
    ...etc
)

Now why would you ever set the increment to something other than 1?

I can understand setting the initial seed value differently. For example if, say, you're creating one database table per month of data (e.g. Table1_082009, Table1_092009) and want to start the UniqueID of the new table where the old one left off. (I probably wouldn't use that strategy myself, but hey, I can see people doing it).

But for the increment? I can only imagine it being of any use in really odd situations, for example:

• after the initial data is inserted, maybe later someone will want to turn identity insert on and insert new rows in the gaps, but for efficient lookup on the index will want the rows to be close to each other?
• if you're looking up ids based directly off a URL, and want to make it harder for people to arbitrarily access the other items (for example, instead of the user being able to work out that changing the URL suffix from /GetData?id=1000 to /GetData?id=1001, you set an increment of 437 so that the next url is actually /GetData?id=1437)? Of course if this is your "security" then you're probably already in trouble...

I can't think of anything else. Has anyone used an increment that wasn't 1, and why? I'm really just curious.

Answer 1

One idea might be using this to facilitate partitionnement of data (though there might be more "automated" ways to do that) :

• Considering you have two servers :
  • On one server, you start at 1 and increment by 2
  • On the other server, you start at 2 and increment by 2.
• Then, from your application, your send half inserts to one server, and the other half to the second server
  • some kind of software load-balancing

This way, you still have the ability to identify your entries : the "UniqueID" is still unique, even if the data is split on two servers / tables.

But that's only a wild idea -- there are probably some other uses to that...

Answer 2

Only for entertainment. And you have to be REALLY bored.