THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Match those types!

This is a recommendation I believe is worth repeating from time to time: Make sure you match data types when you write TSQL code. Else you in most cases end up with an implicit data type conversion. And in worst case, this conversion is performed at the column side - not the literal side of your query. What does that mean? Consider below:

WHERE Col = Val

Now, say that the types for above don't match. Val might be some parameter (to a stored procedure, for instance), a variable or a written value (literal). In any case, when SQL Server need to do some operation )like comparison like here) involving several values which aren't of the same type, then one of the values need to be converted so it is of the same type as the other. Which one is determined by the rules for "Data Type Precedence".

What we don't want is a conversion at the column side. Why? I would argue that an implicit conversion in the first place in many cases mean I misunderstood something about the types in my system and am on my way of producing a bug in my code. But having a more strict language is not the topic for this particular post (check out this). My point here is that it is bad for performance reasons. Just yesterday and today I was involved in a thread on the MSDN forum. Here's the repro script from that thread (slightly adjusted by me):

USE tempdb
GO
IF OBJECT_ID('t'IS NOT NULL DROP TABLE t
GO
CREATE TABLE 
(
 
c1 int IDENTITY(10000001,1) NOT NULL 
,
c2 char(8) NULL
,
c3 datetime NULL
)
GO
INSERT INTO t(c3
SELECT TOP 3000000 '20080203' 
FROM master..spt_values t1
  
CROSS JOIN master..spt_values t2
  
CROSS JOIN master..spt_values t3
UPDATE SET c2 CAST(c1 AS char(8))
CREATE UNIQUE CLUSTERED INDEX ON t(c2)
GO

SELECT FROM WHERE c2 N'10000009'
SELECT FROM WHERE c2 N'10000008'

Don't bother too much about the first section. The relevant part here is that we have char(8) column with values which are unique (enforced by a unique index), on a table with three million rows.

Now, before we dig deeper: What is the problem with above queries? The type do not match! The type in the table is char(8), but literal is Unicode (the N before the string). Since nchar has higher precedence than char, then the column side needs to be converted to the value side. Not good. Try the code on SQL Server 2000 and you will see disaster. For me, STATISTICS IO gave 10755 logical reads for both queries, with Clustered Index Scan (which is the same as table scan). Hoover on the Clustered Index Scan icon and you will find "WHERE:(Convert([t].[c2])=[@1])". See how the conversion is done at the column side? This, my friend, mean that SQL Server cannot seek an index to find the rows. Ouch! That's just because we were a bit sloppy... Compare to below:

SELECT FROM WHERE c2 '10000009'
SELECT FROM WHERE c2 '10000008'

You will find for both two above we have Clustered Index Seek with only three logical reads. That is a good improvement.

OK, enough about SQL Server 2000, lets discuss 2008/2005. At first I couldn't see any difference between the two original queries (N'10000009' and N'10000008'). Both seemed to be instant, and the same execution plan for both. I even saved the execution plan as XML and used WinDiff to compare the xml files. No difference. But the poster said the "9" query was slower than the "8" query. I executed to text and had ctrl-E pressed for a while. And now I could see a difference. The flicker was quicker for the "8" version vs. the "9" version. I used Profiler and confirmed. The "9" version had consistently about 90000 microsecond duration where the "8" version had about 0 duration. This was also reflected in CPU usage, as shown by Profiler. I hoped to use Extended Events to find something deeper about this, but that didn't give me anything. Perhaps I didn't capture the relevant events, quite simply. X/Event experts are welcome to suggest event session configurations. :-)

My guess for the difference is in the execution plan. You will see an index seek for both. That is good, since SQL Server nowadays potentially can use an index even if you end up with an implicit conversion at the column side. But you will see a few more operators in addition to the index seek. See that Constant Scalar followed by a Compute Scalar? My guess is that the built-in internal function used inside the Compute Scalar operator is more expensive for N'10000009' compared to N'10000008'. You can see this function if you use SET STATISTICS PROFILE ON. See the GetRangeThroughConvert() function. My guess is that the answer is the cost for this function. (Search the Internet for things like "Constant Scan" and "GetRangeThroughConvert" for other blog post on this topic.)

The moral of the story? Here we have a cost which can be relevant in some cases, but we will only see this if we happen to execute our query with the right values and are very very observant. But we can eliminate all this mess this by using the right type for the column in the first place!

Published Tuesday, April 28, 2009 1:30 PM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Venkat said:

Very Informative post.   When developers are in a hurry, it is difficult to hammer in the point about matching the types, they usually think SQL is a no brainer.

April 28, 2009 10:03 AM
 

Alexander Kuznetsov said:

An interesting post, Tibor!

I recently described a similar case, but I was concerned about correctness, not performance:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/11/19/defensive-database-programming-fun-with-changing-column-widths.aspx

April 28, 2009 11:25 AM
 

TiborKaraszi said:

Thanks!

Alex, yes, those truncations drive me mad. I never seem to remember when I get truncation, rounding or error when I work in TSQL. Whenever I'm asked, I always have to fire up a query window and test.

April 28, 2009 11:46 AM
 

Jonathan Kehayias said:

Tibor,

The best you can get with XEvents is the plan_handle as an action on the sql_statement_completed event, which doesn't get you anywhere really.  You would be better served just querying against the DMV's to find the plans with implicit conversions occuring in them.  I have a script I am working on right now that seems to be doing a decent job, but could still use some tweaking.  I'll post it when I get it a bit more usable.

April 28, 2009 8:39 PM
 

TiborKaraszi said:

Hi Jonathan,

My point with X/Events was, though, that plan information itself isn't enough here. I know I get a conversion, but why do the value I search for cause such difference (N'10000009' vs N'10000008') - the conversion is the same and the plans are 100% the same. This is where I hopes that X/Events could give some "deep insight". I doubt that plan cache would help since the very same plan is re-used.

April 29, 2009 2:03 AM
 

ALZDBA said:

You pinpointed one of the top issues when exploring query performance.

One remark:

you mention:

... since SQL Server nowadays potentially can use an index even if you end up with an implicit conversion at the column side....

On my SQL2008 SP1 Dev Edtn I still get :

Clustered Index Scan(OBJECT:([DSSCTest].[dbo].[t_ImplConversions2].[x]), WHERE:(CONVERT_IMPLICIT(nchar(8),[dbo].[t_ImplConversions2].[c2],0)=[@1]))  1           4           2           Clustered Index Scan           Clustered Index Scan           OBJECT:([dbo].[t_ImplConversions2].[x]), WHERE:(CONVERT_IMPLICIT(nchar(8),[dbo].[t_ImplConversions2].[c2],0)=[@1])  [dbo].[t_ImplConversions2].[c1], [dbo].[t_ImplConversions2].[c2], [dbo].[t_ImplConversions2].[c3]

April 29, 2009 5:36 AM
 

TiborKaraszi said:

Interesting, ALZDBA... I notice you have different table and db name from my example. What behavior do you get if you run my code as it is? Also what compatibility level do you havefor yout database?

April 29, 2009 5:42 AM
 

ALZDBA said:

Never mind the remark: I misread "use an index seek " for "use an index even"

Off course the good thing is it can use an index scan (might be less overhead than a full table scan), but the index scan is not interrupted if the value(s) are "passed" in the operation.

So the cost is still bigger than an index seek.

April 29, 2009 5:45 AM
 

ALZDBA said:

I even overlooked the fact I should add another index to see if it would be picked.

I redid the test.

DBLevel = 100

Here's my script.

IF OBJECT_ID('t_ImplConversions') IS NOT NULL DROP TABLE t_ImplConversions

GO

CREATE TABLE t_ImplConversions

(

c1 int IDENTITY(10000001,1) NOT NULL

,c2 char(8) NULL

,c2_1  AS (convert(char(8),c1)) PERSISTED NOT NULL

,c3 datetime not null

);

CREATE UNIQUE CLUSTERED INDEX x ON t_ImplConversions (c2_1);

GO

set nocount on ;

INSERT INTO t_ImplConversions(c3)

SELECT TOP 3000000 getdate()

FROM master..spt_values t1

 CROSS JOIN master..spt_values t2

 CROSS JOIN master..spt_values t3

----UPDATE t_ImplConversions SET c2 = CAST(c1 AS char(8))

---- CREATE UNIQUE CLUSTERED INDEX x ON t_ImplConversions (c2)

set nocount off

/*

Select c1, CAST(c1 AS char(8)), c2_1, c3

into t_ImplConversions2

from t_ImplConversions

CREATE UNIQUE CLUSTERED INDEX x ON t_ImplConversions2 (c2)

create nonclustered index y on t_ImplConversions2 (c2)

*/

GO

dbcc freeproccache

go

SET STATISTICS PROFILE ON

set statistics io on

set statistics time on

go

SELECT * FROM t_ImplConversions WHERE c2_1 = N'10000009'

SELECT * FROM t_ImplConversions WHERE c2_1 = N'10000008'

go

SELECT * FROM t_ImplConversions WHERE c2_1 = '10000009'

SELECT * FROM t_ImplConversions WHERE c2_1 = '10000008'

go

/*

since SQL Server nowadays potentially can use an index even if you end up with an implicit conversion at the column side.

*/

SELECT * FROM t_ImplConversions2 WHERE c2 = N'10000009'

SELECT * FROM t_ImplConversions2 WHERE c2 = N'10000008'

go

SELECT * FROM t_ImplConversions2 WHERE c2 = '10000009'

SELECT * FROM t_ImplConversions2 WHERE c2 = '10000008'

go

And indeed .... now it uses the Y index :-)

Table 't_ImplConversions2'. Scan count 5, logical reads 5275, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Rows                 Executes             StmtText                                                                                                                                                                                                      StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                 DefinedValues                                                                                                                         EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                                                                                        Warnings Type                                                             Parallel EstimateExecutions

-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------

1                    1                    SELECT * FROM [t_ImplConversions2] WHERE [c2]=@1                                                                                                                                                              1           1           0           NULL                           NULL                           NULL                                                                                                                                                                     NULL                                                                                                                                  1             NULL          NULL          NULL        6,400179         NULL                                                                                                                                                                              NULL     SELECT                                                           0        NULL

1                    1                      |--Parallelism(Gather Streams)                                                                                                                                                                              1           3           1           Parallelism                    Gather Streams                 NULL                                                                                                                                                                     NULL                                                                                                                                  1             0             0,02850498    35          6,400179         [DSSCTest].[dbo].[t_ImplConversions2].[c1], [DSSCTest].[dbo].[t_ImplConversions2].[c2], [DSSCTest].[dbo].[t_ImplConversions2].[c2_1], [DSSCTest].[dbo].[t_ImplConversions2].[c3]  NULL     PLAN_ROW                                                         1        1

1                    4                           |--Nested Loops(Inner Join, OUTER REFERENCES:([DSSCTest].[dbo].[t_ImplConversions2].[c2]))                                                                                                             1           4           3           Nested Loops                   Inner Join                     OUTER REFERENCES:([DSSCTest].[dbo].[t_ImplConversions2].[c2])                                                                                                            NULL                                                                                                                                  1             0             2,09E-06      35          6,371674         [DSSCTest].[dbo].[t_ImplConversions2].[c1], [DSSCTest].[dbo].[t_ImplConversions2].[c2], [DSSCTest].[dbo].[t_ImplConversions2].[c2_1], [DSSCTest].[dbo].[t_ImplConversions2].[c3]  NULL     PLAN_ROW                                                         1        1

1                    4                                |--Parallelism(Repartition Streams, RoundRobin Partitioning)                                                                                                                                      1           5           4           Parallelism                    Repartition Streams            NULL                                                                                                                                                                     NULL                                                                                                                                  1             0             0,02850215    15          6,396891         [DSSCTest].[dbo].[t_ImplConversions2].[c2]                                                                                                                                        NULL     PLAN_ROW                                                         1        1

1                    4                                |    |--Index Scan(OBJECT:([DSSCTest].[dbo].[t_ImplConversions2].[y]),  WHERE:(CONVERT_IMPLICIT(nchar(8),[DSSCTest].[dbo].[t_ImplConversions2].[c2],0)=N'10000009'))                              1           6           5           Index Scan                     Index Scan                     OBJECT:([DSSCTest].[dbo].[t_ImplConversions2].[y]),  WHERE:(CONVERT_IMPLICIT(nchar(8),[DSSCTest].[dbo].[t_ImplConversions2].[c2],0)=N'10000009')                         [DSSCTest].[dbo].[t_ImplConversions2].[c2]                                                                                            1             3,84831       1,650079      15          5,498389         [DSSCTest].[dbo].[t_ImplConversions2].[c2]                                                                                                                                        NULL     PLAN_ROW                                                         1        1

1                    1                                |--Clustered Index Seek(OBJECT:([DSSCTest].[dbo].[t_ImplConversions2].[x]), SEEK:([DSSCTest].[dbo].[t_ImplConversions2].[c2]=[DSSCTest].[dbo].[t_ImplConversions2].[c2]) LOOKUP ORDERED FORWARD)  1           8           4           Clustered Index Seek           Clustered Index Seek           OBJECT:([DSSCTest].[dbo].[t_ImplConversions2].[x]), SEEK:([DSSCTest].[dbo].[t_ImplConversions2].[c2]=[DSSCTest].[dbo].[t_ImplConversions2].[c2]) LOOKUP ORDERED FORWARD  [DSSCTest].[dbo].[t_ImplConversions2].[c1], [DSSCTest].[dbo].[t_ImplConversions2].[c2_1], [DSSCTest].[dbo].[t_ImplConversions2].[c3]  1             0,003125      0,0001581     27          0,0032831        [DSSCTest].[dbo].[t_ImplConversions2].[c1], [DSSCTest].[dbo].[t_ImplConversions2].[c2_1], [DSSCTest].[dbo].[t_ImplConversions2].[c3]                                              NULL     PLAN_ROW                                                         1        1

But it still took the engine ( 1 quad core proc )   :

SQL Server Execution Times:

  CPU time = 3765 ms,  elapsed time = 962 ms.

April 29, 2009 5:57 AM
 

Scott R. said:

Tibor,

Great post!  I agree we should all be more aware of data types used in comparisons and functions, especially where they may introduce unintended behaviors with unfavorable consequences.

Wouldn't it be great if SSMS IntelliSense could flag T-SQL clauses with potential type conversion issues (from feedback in the execution plan), and possibly provide supplemental messages for further description?  Some compilers do this for traditional programs - why not SSMS for T-SQL via the query optimizer (a query plan is a type of program).  Maybe it could be an option in SSMS (like setting a compiler flagging option).  Leverage our experience with meaningful feedback from the query optimizer that people of all experience levels can use and learn from.

It's nice to dream.  Perhaps a new Connect item?

Scott R.

April 29, 2009 7:13 AM
 

Paul White said:

Spot on.  Especially the GetRangeThroughConvert stuff - I admit I do try to avoid that turning up in plans - it makes me nervous somehow ;c)

The only implicit conversions I grudgingly accept are those done for correctness when dividing decimals in an intermediate step of a computation.  To illustrate:

declare @a dec(5,2), @b dec(5,2)

select top (1) @a = 123.45, @b = 123.34

select top (1) @a / @b as c into dbo.t

...column c has datatype decimal (13,8) - who knew?!

Paul

April 29, 2009 7:40 AM
 

TiborKaraszi said:

Interesting idea with the Intellisense thing, Scott. The information is available to the SQL parser so why not help us with this as well! Enter a connect item and I will vote. :-)

April 29, 2009 12:29 PM
 

Alexander Kuznetsov said:

To avoid such problems it would be nice to declare a variable of the same type as a column, like we do in Oracle. Something like this:

DECLARE @c TYPEOF(t.c2)

-- conversion or error here

SET @c=N'10000009'

-- types always match

SELECT * FROM t WHERE c2 = @c

April 29, 2009 1:32 PM
 

Martin Schmidt said:

@Paul White

Here is a link from Microsoft that explains the outcome of your little test - msdn.microsoft.com/en-us/library/ms190476.aspx

So the mystery is not that big anymore

May 4, 2009 9:31 AM
 

Paul White said:

Martin,

Thanks for the link - I had seen something like that before, maybe even in BOL - I forget.  

If you can keep all those rules for scale and precision in your head, my hat comes off to you :c)

I'm as big a fan as anyone of getting the types right - but those rules are just way too much effort!

But thanks again for the link - appreciated.

Paul

May 4, 2009 5:48 PM
 

Ian Stirk said:

By coincidence, the following article tackles the problem at its source:

http://www.sqlservercentral.com/articles/Admin/65138/

The utility described in the article will allow you to quickly identify which columns have mismatched datatypes across tables in a database, correcting these will improve performance, integrity and maintainability

Thanks

Ian

May 5, 2009 3:28 AM
 

The Rambling DBA: Jonathan Kehayias said:

Last year Tibor Karaszi posted a blog post titled Match Those Types that talked about implicit data type

January 8, 2010 12:54 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement