CODEWITHSUNDEEP
sql-serverentity-frameworksql-server-2012
Dandry
Dandry

Reputation: 515

SQL Server : primary key auto increment - what about deleted rows and free key values?

I'm kind of new to SQL and databases and there's one thing that bothers me.

I'm using SQL Server for my ASP.NET MVC project and my database and its tables were auto-generated by Entity Framework using a code-first approach.

I have a table for book collections - just CollectionId and Name columns. During development I've made many inserts and deletes in this table and right now it has 10 rows with Id's 1 to 10 (the initial entries). But when I add a new one it has the Id set to 37. Obviously in the past there were entries with Id up to 36, but there are now gone and these numbers seem to be free.

Then why a new entry does not have the Id set to 11? Is it a kind of limitation or maybe a security feature?

Thank you for answers.

Upvotes: 0

Views: 294

Answers (2)

Mike
Mike

Reputation: 3311

In addition to the other answer, it also has to do with performance of the server. The server typically cache's a group of ID's in memory to make assignment much faster, since the next number has to be stored on disk somewhere. So if the server allocates 100 numbers at a time, it only has to write to disk 1 out of every 100 usages (inserts) of the identity.

Trying to maintain gaps in the sequence would suck up a lot of time.

If you create a new table, insert a single row, kill the server and restart, you'll find the next insert will most likely contain a gap of whatever that number of cached values contains.

Upvotes: 0

Sandesh
Sandesh

Reputation: 1044

This is default behavior when we define identity column. Whenever we perform delete operations there will be gaps in records for identity column.

Remarks from MSDN

If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to ensure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON.

IDENTITY

Upvotes: 1

Related Questions