mssqltips logo

SQL Server Index Not Being Used



By:

Overview

Queries that are issued will attempt to use available indexes since they provide queries with a mechanism to return your results as quickly as possible. However, there are subtle ways the use of an index can be nullified if we're not careful.

Explanation

Let's look at a couple of common scenarios I've come across. The first scenario is a classic one where an indexed column in a WHERE clause is wrapped within a function. When an indexed column is wrapped within a function, SQL Server will not use any available index on the column. In this example, there is an available index on the AccountNumber column of Sales.Customer but this search on AccountNumber will scan. My SQL Server database is using a case insensitive collation so the UPPER function is not necessary at all. If checking for upper case values were necessary, we could either force the database to store all AccountNumber values as upper case or we could create a companion column that stores the upper case representation.

declare @AccountNumber varchar(10)
set @AccountNumber = 'AW00000424'
select * 
from Sales.Customer
where UPPER(AccountNumber) = @AccountNumber

Queries that are issued will attempt to use available indexes since they provide queries with a mechanism to return your results as quickly as possible

This next scenario involves data type precedence and passing the wrong type of data to a query. In the Customer table, the AccountNumber is defined as varchar(10). In the following query, a variable of nvarchar(10) has been defined and used as the search argument. Since the data types differ, the data type of higher precedence will be used. Since nvarchar has higher precedence than varchar, the AccountNumber column is implicitly converted and the same query plan is produced as in the previous example. Consistency is key here; be diligent in making sure the data types of the arguments you pass to your queries are consistent with the data types of the columns being searched.

declare @AccountNumber nvarchar(10)
set @AccountNumber = 'AW00000424'
select * 
from Sales.Customer
where AccountNumber = @AccountNumber

Additional Information


Last Update: 9/10/2011




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools