Sequential numbers for many rows inserted with auto-increment key
<ul>
<li>SQL Server 2008 R2 </li>
<li>A table with auto-increment key</li>
<li>Many different threads have to batch insert rows in the table.</li>
</ul>

<p>I would like to know how (if even it is possible) to insert the rows in a way that I'm absolutely sure that the keys of one thread's inserted rows will get sequential numbers.</p>

<p>For example if 2 threads are executing at the same time:</p>

<ul>
<li>Thread #1 insert 5 rows and get keys 1,2,3,4,5</li>
<li>Thread #2 insert 5 rows and get keys 6,7,8,9,10</li>
</ul>

<p>I must be sure not to get:</p>

<ul>
<li>Thread #1 rows get keys 1,3,4,8,9</li>
<li>Thread #2 rows get keys 2,5,6,7,10</li>
</ul>

<p>Obviously if each thread do some kind of loop and execute 5 times "INSERT INTO..." command it won't work because another thread can insert between.</p>

<p>But even if a thread use only one INSERT command to insert many rows, is it enough to guarantee the keys will be sequential ?</p>

<p>If yes, could you help me find where it is documented ? because I didn't.</p>

<p>If no, how is it possible to ensure that ?</p>

<hr>

<p>EDIT Why I care of getting consecutive numbers:</p>

<p>It is mainly a performance concern, we actually have one integer not auto-increment column in a row, each thread lock the row, manually increment the column value by any number it needs, than release the row. </p>

<p>The problem with that is that only one thread can insert at a time, by testing we found that inserting rows in a new table auto-increment column, letting sql server manage identity assignment, was much faster.
Locking the entire table is not an option because it will result in the same problem as locking a common row.</p>

<p>The reason why I want to ensure that inserted row ids are sequential for a single thread insert is to reduce the need for code refactoring which actually works by keeping only the first number and the count, so the code can deduce what are the other numbers.</p>

<p>It is not a business concern to have the numbers being consecutives, so if it is impossible to do we will just have to keep each row number in an array instead but there is more code to refactor this way so I'm trying to avoid it if possible.</p>

<p>Please keep in mind that i'm totally aware that the design may not be ideal, but i'm working with a legacy "big ball of mud" system that I can't redesign much.</p>