Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

SQL Server Data Type Precedence

MSSQLTips author Armando Prato By:   |   Read Comments (10)   |   Related Tips: More > Data Types
Problem

I am executing a simple query/stored procedure from my application against a large table and it's taking a long time to execute. The column I'm using in my WHERE clause is indexed and it's very selective. The search column is not wrapped in a function so that's not the issue. It's like the optimizer doesn't even know an index exists! What could be going wrong?

Solution

A subtlety that can cause an issue like this to arise occurs when the data type of a query search parameter you've defined in your application or declared in your stored procedure doesn't match the same data type of the column that will be searched by the query. In these cases, SQL Server will implicitly convert the data type of either the search column or the search parameter by converting the one with the lower precedence data type to the data type of higher precedence. If the search column becomes the victim of conversion, the result can be a scan (as opposed to a seek) to satisfy the query request. Let's look at a couple of examples. In this first example (the data is from AdventureWorks), we will attempt to query the Sales.Customer table by looking up a customer by their AccountNumber. The AccountNumber is varchar(10) and has a unique index assigned to it. Running a query and examining the resulting execution plan reveals an index seek using the index available on the AccountNumber as we'd expect:

create procedure dbo.PrecedenceTest
(
 @AccountNumber varchar(10)
)
as
begin
 set nocount on
 select * 
 from Sales.Customer
 where AccountNumber = @AccountNumber
end
go
exec dbo.PrecedenceTest 'AW00030113'
go

Running a query and examining the resulting execution plan reveals an index seek using the index available on the AccountNumber as we'd expect

Let's make a small change to the @AccountNumber parameter; we'll change it from varchar to nvarchar, re-execute the procedure, and re-examine the execution plan.

alter procedure dbo.PrecedenceTest
(
 @AccountNumber nvarchar(10)
)
as
begin
 set nocount on
 select * 
 from Sales.Customer
 where AccountNumber = @AccountNumber
end
go
exec dbo.PrecedenceTest 'AW00030113'
go

The resulting plan shows that a scan was chosen by the optimizer using the index on TerritoryID:

a scan was chosen by the optimizer using the index on TerritoryID

Examining the Filter operator, we can see the AccountNumber column was implicitly converted to match the data type of the search parameter since it's data type (varchar) has a lower precedence than the data type of the search column (nvarchar) which nullifies the index.


Examining the Filter operator

Now let's examine conversion occurring in reverse where the data type of the search parameter has lower precedence than the data type of the search column. In this case, the LastName column of the Person.Person table is nvarchar and an available index exists for it. The @LastName parameter of the stored procedure will be declared as varchar:

alter procedure dbo.PrecedenceTest(
 @LastName varchar(50)
)
as
begin
 set nocount on
 select * 
 from Person.Person
 where LastName = @LastName
end
go
exec dbo.PrecedenceTest 'Tamburello'
go

The resulting plan shows that an index seek using the available index was chosen by the optimizer:

an index seek using the available index was chosen by the optimizer

Drilling in further, we see that conversion occurred on the passed in search parameter as opposed to the search column since the data type for LastName is nvarchar which has a higher precedence than the data type of the search parameter which is defined as varchar:

Since the indexed column was not affected by conversion, the optimizer was free to choose an optimal plan

Since the indexed column was not affected by conversion, the optimizer was free to choose an optimal plan.

Whether you're defining query parameters in your application logic or declaring within them within stored procedures, prevent potential index scanning and other conversion issues by making sure the data types of all query parameters match the data types of the columns of your query.

Next Steps


Last Update: 8/15/2012


About the author
MSSQLTips author Armando Prato
Armando Prato has over 24 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, August 15, 2012 - 9:50:56 AM - Mike Read The Tip

Seems like something similiar was discussed before > http://www.sqlservercentral.com/blogs/never_say_never/2010/02/21/data-type-precedence-and-implicit-conversions/


Wednesday, August 15, 2012 - 11:57:48 AM - Armando Prato Read The Tip

Thanks for the link; I hadn't seen that one.

I ran into this issue a few years back while troubleshooting a Java application.   There was a clustered index on a large table with the clustered key on a varchar type.   The query parameter was bound as an nvarchar type.  For the life of me, I couldn't figure out why the query was scanning until I discovered the data type precedence rules.   Very subtle and caused me a lot of grief. 


Saturday, August 18, 2012 - 11:42:15 PM - Balaji Read The Tip

Hi Armando,

 

How about Harcoded search parameters in a query? Example

--ACCOUNTNO is NVARCHAR(25) Data Type

Select ACCOUNTNO FROM ACCOUNTS_MASTER WHERE ACCOUNTNO = '123456'

How would we handle this case?


Sunday, August 19, 2012 - 8:46:56 AM - Armando Prato Read The Tip

Add the N prefix ie N'123456'


Tuesday, September 04, 2012 - 3:07:37 PM - Kevin G. Boles Read The Tip

This issue has nothing at all to do with "datatype precedence".  It is simply a sloppy developer not using the CORRECT datatype.  I say sloppy because when code is written the SCHEMA IS KNOWN - there is NEVER a reason for a developer to not use the exactly correct datatype when they are coding, regardless of how much time pressure they are under to complete a given task.  

Also, your statement that IMPLICIT CONVERSION "nullifies the index" is also incorrect.  A nonclustered index can still be used - it just cannot be SEEKed.  The index will be SCANNED intead, which is not only a performance hit, but it can also be a huge CONCURRENCY hit because while all those index pages are being scanned the entire index is LOCKED for shared access, thus preventing ALL INSERTS/UPDATES/DELETES.

This is one of the most common mistakes I see in my work as a SQL Server consultant.  There are MANY other "sneaky" issues that can rob an application of performance/scalability/concurrency!!

Kevin


Friday, September 07, 2012 - 12:41:56 AM - Armando Prato Read The Tip

 

Hi Kevin, thanks for your feedback. 

I am having a little trouble understanding your criticism.  The candidate index in the example was nullified.... the optimizer chose to scan an entirely different index (on TerritoryID).   Unfortunately, it looks like the screen shot of the graphical plan was cut off when converted for the tip.  I also do note that a scan can be the result as opposed to a seek.   Also, I agree that it's sloppy and I unfortunately find some instances of this. However, downcasting can cause a scan.  I also note that data type consistency is key at the end of the tip. 

Am I misunderstanding what you're trying to convey?


Friday, September 07, 2012 - 8:39:09 AM - Greg Robidoux Read The Tip

It would be nice if DBAs and developers always did the right thing, but unfortunately people often take shortcuts or just do the same thing over and over again regardless if it is right or wrong.  I think this is a good example of why you should be aware of the datatypes that are being used and the potential issues that could be created.  I often see a mix of varchar and nvarchar datatypes in the same database and or table.  I think a lot of this comes form people just copying and pasting code they find without making any modifications or making it fit their database rules.  Hopefully this is a good lesson for people to be more aware of why they choose certain datatypes and how the database engine reacts.


Friday, September 07, 2012 - 9:46:46 AM - Kevin G. Boles Read The Tip

What database are you using?  Indexes do NOT get "nullified".  What happens is that the CONVERT_IMPLICIT alters the ESTIMATED ROWS such that using some other method than the expected index seek becomes the LOWEST COST query plan.  Please take a look at the estimated row counts for each plan and also the query costs and see if that makes sense.

Kevin


Friday, September 07, 2012 - 10:09:48 AM - Armando Prato Read The Tip

I am using the AdventureWorks for SQL Server 2008R2 (noted in the tip with a link).

I think we're semantically apart.  What I mean by nullified is that the expected index isn't used.  I apologize for the lack of clarity on that part. 


Friday, September 07, 2012 - 10:54:01 AM - Kevin G. Boles Read The Tip

So you think the AK_Customer_AccountNumber index should still be used, right?  So lets FORCE the optimizer to use it:

 

alter procedure dbo.PrecedenceTest

(

 @AccountNumber nvarchar(10)

)

as

begin

 set nocount on

 select * 

 from Sales.Customer WITH (INDEX=AK_Customer_AccountNumber)

 where AccountNumber = @AccountNumber

end

go

exec dbo.PrecedenceTest 'AW00030113'

GO

 
If you look at the reads, this takes 56, the other index and seek takes 39.  If you look at query cost, the forced index is 0.078 and the other index is just 0.067.  Both measures are LESS expensive when the optimizer picks the IX_Customer_TerritoryID index because it is NARROWER (thus less IO) due to TerritoryID being a smaller datatype.  The reason for this is that the AK_Customer_AccountNumber index cannot be SEEKED - it has to do a SCAN like I stated due to the CONVERT_IMPLICIT forced by using the incorrect datatype.
 
Looking at the table/column you used I will say you also picked a VERY unfortunate example because AccountNumber on Sales.Customer table is a COMPUTED column that uses a scalar UDF.  


Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.