SQL Server and Identity column

Question

Hi,

I just noticed that if I have an identity column in a table, when I insert new rows SQL Server 2008 is automatically filling up the sequence if there are discontinuity. I mean, if in my identity column I have 1,2,5,6 if I insert other two rows in the table the system puts automatically 3,7 in the identity column.

Do you know how to control this behavior?

THANKS

Answer 1

That is the defined and documented SQL Server behavior, and there's really not much you can do about changing it. What did you want to change about it??

IDENTITY columns will guarantee unique, ever-increasing ID's (as long as you don't mess around with them) - they don't guarantee anything else.

SQL Server will not go through the trouble of spotting "gaps" in your sequence and filling them up. I don't think that would be a good idea, anyway - what if you did have a record with ID=3, and then deleted it? Do you really want a next record to suddenly "recycle" that ID?? Not a good idea, in my opinion.