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<O>

</O>