I am replying after a looooooooooooong time

On the first method the author is using a not so optimized query.  He is using different methods in each queries (sub query vs outer join and Min vs top 1).  More than that,  Should we declare a variable, get the result into the variable and select the variable?

I tried with Left outer join and the results are really better.  (I tried with both statistics time and statistics IO ON)

Select top 1 t1.IDENTITYCOL + IDENT_INCR('OrderHeader') As NextIdentityValue

from OrderHeader t1 Left Outer join OrderHeader t2

ON T1.IDENTITYCOL+IDENT_INCR('OrderHeader')=t2.IDENTITYCOL

Where t2.IDENTITYCOL is null

This is the result:

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 2 ms.

2210

Table 'OrderHeader'. Scan count 2210, logical reads 4450, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

   CPU time = 26 ms,  elapsed time = 26 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Later I introduced a variable to get the Indentity increment first the the results are even better

Declare @Increment int

Select @Increment= IDENT_INCR('OrderHeader')

Select top 1 t1.IDENTITYCOL + @Increment As NextIdentityValue

from OrderHeader t1 Left Outer join OrderHeader t2

ON T1.IDENTITYCOL + @Increment = t2.IDENTITYCOL

Where t2.IDENTITYCOL is null

The results are slightly better:

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 2 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

2210

Table 'OrderHeader'. Scan count 2136, logical reads 4439, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

   CPU time = 7 ms,  elapsed time = 7 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Finally,  I changed the min into top 1.  the results are almost to my previous results:

SELECT top 1 IDENTITYCOL + IDENT_INCR('OrderHeader') As NextIdentityValue

FROM OrderHeader t1

WHERE IDENTITYCOL BETWEEN IDENT_SEED('OrderHeader') AND 32766

    AND NOT EXISTS (SELECT * FROM OrderHeader t2

        WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('OrderHeader'))

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 3 ms, elapsed time = 3 ms.

2210

Table 'OrderHeader'. Scan count 2210, logical reads 4440, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

   CPU time = 15 ms,  elapsed time = 26 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.