{"id":17,"date":"2007-08-11T12:39:49","date_gmt":"2007-08-11T10:39:49","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=17"},"modified":"2007-08-11T12:39:49","modified_gmt":"2007-08-11T10:39:49","slug":"implicit-conversions","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2007\/08\/11\/implicit-conversions\/","title":{"rendered":"Implicit conversions"},"content":{"rendered":"<p>Or <em>&#8216;How to slow down a query without apparent reason&#8217; <\/em><\/p>\n<p>I&#8217;ve discussed <a href=\"http:\/\/sqlinthewild.wordpress.com\/2007\/07\/19\/functions-in-a-where-clause\/\">functions in where clauses<\/a> before and shown how they can prevent index usage and slow down queries. I&#8217;ve also discussed <a href=\"http:\/\/sqlinthewild.wordpress.com\/2007\/07\/10\/data-conversion-woes\/\">data type conversions<\/a> and shown that SQL Server sometimes does the conversions in places not expected. There&#8217;s a nice little gotcha that results from the combination of these two. Let&#8217;s take a look at a simple example.<\/p>\n<p>Which of the following queries will run slower? (sample code at the end)<\/p>\n<pre>Select ID from TestingConversion where Code = 'AAA'\nSelect ID from TestingConversion where Code = N'AAA'<\/pre>\n<p><!--more-->They look like they should run the same, however they don&#8217;t. If you look at the execution plan of the second, you&#8217;ll see an implicit conversion of the column Code from Char to nChar before the filter is done. ie, the second query is equivalent to writing<\/p>\n<pre>Select ID from TestingConversion where CAST(Code AS nchar(3)) = N'AAA'<\/pre>\n<p>Hence, just like for any other filter in the where clause, that conversion prevents index usage and results in queries that run slow when they look like they should run fast.<\/p>\n<pre>Sample code:\nCREATE TABLE TestingConversion (\n    ID int identity,\n    Code CHAR(3),\n    InsertDate DATETIME default getdate()\n)<\/pre>\n<pre>CREATE CLUSTERED INDEX idx_TestingConversion_ID ON TestingConversion (ID)<\/pre>\n<pre>CREATE NONCLUSTERED INDEX idx_TestingConversion _Code on TestingConversion (Code)<\/pre>\n<pre>;WITH DataInsert (RowNo) AS (\n    SELECT 1 AS RowNo\n    union all\n    select RowNo+1 FROM DataInsert WHERE RowNo&lt;5000\n)\ninsert into TestingConversion (Code, InsertDate)\nSELECT char(FLOOR(Rand(RowNo*78000)*26)+65) + char(FLOOR(Rand(RowNo*15000)*26)+65) + char(FLOOR(Rand(RowNo*51000)*26)+65),\n    DATEADD(mi,Rand(RowNo*78000)*575000,'2000\/01\/01')\n    FROM DataInsert\n    OPTION (MaxRecursion 5000)<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Or &#8216;How to slow down a query without apparent reason&#8217; I&#8217;ve discussed functions in where clauses before and shown how they can prevent index usage and slow down queries. I&#8217;ve also discussed data type conversions and shown that SQL Server&#8230; <a class=\"read-more-button\" href=\"https:\/\/www.sqlinthewild.co.za\/index.php\/2007\/08\/11\/implicit-conversions\/\">(Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[25,15],"tags":[],"class_list":["post-17","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-h","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/17","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=17"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/17\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=17"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=17"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=17"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}