Query Optimizer not Selecting Index on a Computed Column – Performs Table Scan
Hi Denny,
we are using the checksum function much like the description in your blog to create a interger column to represent a nvarchar(max) column and use this column to filter our query by. The computed column is not persisted and there is a non-cluster index on it (the only index on the table).
When I execute this command:
Select 1 from table where computed_column = checksum(text_value), the execution plan does not use the non_clusterd index on the computed_column but instead performs a table scan.
Do you know what causes thishow to fix this?
Thank you,
Steve -
Software/Hardware used:
Windows Advanced Sever 2008, SQL Server 2008 EE x64
we are using the checksum function much like the description in your blog to create a interger column to represent a nvarchar(max) column and use this column to filter our query by. The computed column is not persisted and there is a non-cluster index on it (the only index on the table).
When I execute this command:
Select 1 from table where computed_column = checksum(text_value), the execution plan does not use the non_clusterd index on the computed_column but instead performs a table scan.
Do you know what causes thishow to fix this?
Thank you,
Steve -
Software/Hardware used:
Windows Advanced Sever 2008, SQL Server 2008 EE x64
ASKED:
July 14, 2011 6:27 PM
UPDATED:
March 31, 2012 9:25 PM
Answer Wiki:
You've got a couple of options on how to fix this.
1. Declare a variable, and put the checksum in the variable then use the variable in the query.
<pre>declare @checksum int = checksum('text_value')
select 1 from table where computed_column = @checksum</pre>
2. Use an index hint to force the SQL Server to use the index.
<pre>Select 1 from table with (index(YourIndexName)) where computed_column = checksum(text_value)</pre>
Personally I would recommend #1.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
