Verena_Techie_59 (12/3/2012)

---

There are several reasons for congiguous identity keys.

1) It's much simpler to keep ID keys in sync between production and testing systems.

2) Coding can be simpler and more reliable. In testing I can add the rows, then delete where ID > (the max key before additions) then reset the ID key again. This is probably the best reason. When I am adding rows to a table. Of course no one else better be adding rows at the same time or it won't work anyway and I'll have to code to find the records I just added and then select the max key to reset back to.

3) It's clean.

4) What if your id keys don't go up by 1? You could run out of ID keys fast, having very large gaps and an application may not be able to handle keys that are larger than x.

5) Some designs depend on certain values for identity fields, probably a poor design mind you....

6) Some gaps are better left. Especially when cleaning up some data that has been live. There may be history attached to it that will be kept. I don't like to reuse keys in case someone coded against the ID's and some programmers have....

FYI: to do this for Sybase:

Select max(id) key from table (either before inserts or after deletions) and then use sp_chgattribute to reset the identity key back.

select max(table1_id) from database..table1

-- example 300

use database1

sp_chgattribute table1, 'identity_burn_max', 0, '300'

7) Reason 7 is for us OCD types!