FIX: Optimizer Chooses Table Scan Instead of Index Seek for Simple SELECT Involving @@identity

When you include @@IDENTITY in the WHERE clause of a SELECT statement, the Query Optimizer chooses to perform a table scan rather than using the appropriate index.

@@IDENTITY is not being recognized as a run-time constant.

Declare a variable, set it equal to @@IDENTITY, and then use the variable in the SELECT statement.

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:For more information, contact your primary support provider.