By: Armando Prato
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
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
