Dinesh wrote:

As per the output, there are 20086 logical reads and it has taken 200 ms for the first method. But in second method there are only 19 logical reads and the execution time is less considerable.

That’s why I selected to continue in my way. But there may be a side that I have not seen but you can see. So, try on this and see whether how this T-SQL solution suit for you.

Actually, I think that you are not considering two things:

1. The overhead of creating a table of 30000 rows is much greater than the difference between the two queries. If you prefer to store this table permanently, you have to consider that it's size will be added to each backup and that you need to adjust the number of rows as more data will be added in the main table.

2. The example from Books Online doesn't assume that the seed and the interval are 1, instead it determines them and this is the main cause of the many logical reads. Try:

SELECT MIN(IDENTITYCOL) + 1 as NextIdentityValue
FROM OrderHeader t1
WHERE IDENTITYCOL BETWEEN 1 AND 32766
     AND NOT EXISTS (SELECT * FROM OrderHeader t2
        WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + 1)

You will get only about 120 logical reads (instead of about 20000) and the execution time is much better.

Razvan Socol>

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.