Query Optimizer not Selecting Index on a Computed Column – Performs Table Scan

5 pts.
Tags:
SQL Query
SQL Server 2008
SQL Server 2008 64-bit
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
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: