So I reread what I wrote, and there's actually two issues. The most important one actually has nothing to do with using tables or table variables (you handle this correctly).

Depending on the query plan used, the identity values assigned to rows you insert may or may not be in order, as described in this article:

http://support.microsoft.com/?kbid=273586

You have handled this case by using the maxdop hint and explicitly ordering your rows on each insert.

The second issue is that identity values are assigned outside of the transaction, so within a given transaction the identities are not guaranteed to be sequential if there's ever any data modification against the table. This won't occur if you're using table variables, but can if you're using tables that other queries have access to.

Try running the following queries simultaneously:

Setup

CREATE TABLE foo (fooID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, data1 TIMESTAMP, data2 INT, data3 CHAR(250))

GO

INSERT INTO foo(data2, data3)

SELECT CHECKSUM(name), name

FROM syscolumns

UNION ALL

SELECT CHECKSUM(name+'foo'), name+'foo'

FROM syscolumns

first connection:

SET IMPLICIT_TRANSACTIONS OFF

SET LOCK_TIMEOUT 5

GO

DECLARE @maxID BIGINT, @minID BIGINT

SELECT @maxID = MAX(id), @minID = MIN(ID) FROM sysobjects

INSERT INTO foo (data2, data3)

SELECT TOP(1) CHECKSUM(id), name

FROM sysobjects WHERE id >= (RAND()*(@maxID-@minID))+@minID

GO 20000

Second connection:

DECLARE @sid AS INT;

WITH CTE AS

(

SELECT ROW_NUMBER() OVER (ORDER BY name) AS row,

name

FROM sys.columns

)

INSERT INTO foo (data2, data3)

SELECT row, 'foo123'+name

FROM cte

ORDER BY row DESC

OPTION(MAXDOP 1)

SELECT @sid = SCOPE_IDENTITY()

SELECT fooid, fooid + data2 - @sid, data2

FROM foo

WHERE data3 LIKE 'foo123%'

AND fooid + data2 - @sid <> 1

If you run the second query by itself, it'll return no rows. When I started the first query, and immediately started the second before the first finished, I get identities that no longer can be computed from the scope identity.