SQL Server : primary key auto increment - what about deleted rows and free key values?
<p>I'm kind of new to SQL and databases and there's one thing that bothers me.</p>

<p>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.</p>

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

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

<p>Thank you for answers.</p>