{"id":1756,"date":"2013-04-11T13:50:40","date_gmt":"2013-04-11T17:50:40","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=1756"},"modified":"2017-04-13T12:55:39","modified_gmt":"2017-04-13T16:55:39","slug":"implicit-conversions-that-cause-index-scans","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/","title":{"rendered":"Implicit Conversions that cause Index Scans"},"content":{"rendered":"<p>Implicit conversions seem to be a growing trend in the SQL Server performance tuning work that I&#8217;ve been engaged in recently, and I&#8217;ve blogged in the past about ways to<a title=\"Finding Implicit Column Conversions in the Plan Cache\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/finding-implicit-column-conversions-in-the-plan-cache\/\"> identify when implicit conversions are occurring using the plan cache<\/a>.<\/p>\n<p>For those of you who don&#8217;t know, implicit conversions occur whenever data with two different data types are being compared, based on the data type precedence.\u00a0 The precedence establishes the hierarchy of of the types, and lower precedence data types will always be implicitly converted up to the higher precedence type.\u00a0 These conversions increase CPU usage for the operation, and when the conversion occurs on a table column can also result in an index scan where an index seek would have been possible without the implicit conversion.<\/p>\n<p>For a long time I&#8217;ve wanted to map out the most common data types and the effect of a column-side implicit conversion for creating an index seek versus an index scan and recently I finally got around to mapping it all out.<\/p>\n<h2>Setting up the tests<\/h2>\n<p>To map out the implicit conversion affects I created two databases using different collations, one using SQL_Latin_General_CP1_CI_AS and the other using Latin_General_CI_AS, and then created the following table in each of the databases.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TABLE dbo.TestImplicitConverts\r\n(\r\n\tRowID int NOT NULL IDENTITY (1, 1),\r\n\tBigIntCol bigint NOT NULL,\r\n\tBitCol bit NOT NULL,\r\n\tCharCol char(10) NOT NULL,\r\n\tDateCol date NOT NULL,\r\n\tDateTimeCol datetime NOT NULL,\r\n\tDateTime2Col datetime2(7) NOT NULL,\r\n\tDateTimeOffsetCol datetimeoffset(7) NOT NULL,\r\n\tDecimalCol decimal(10, 2) NOT NULL,\r\n\tFloatCol float(53) NOT NULL,\r\n\tIntCol int NOT NULL,\r\n\tMoneyCol money NOT NULL,\r\n\tNCharCol nchar(10) NOT NULL,\r\n\tNumericCol numeric(10, 2) NOT NULL,\r\n\tNVarchrCol nvarchar(50) NOT NULL,\r\n\tRealCol real NOT NULL,\r\n\tSmallDateTimeCol smalldatetime NOT NULL,\r\n\tSmallIntCol smallint NOT NULL,\r\n\tSmallMoneyCol smallmoney NOT NULL,\r\n\tTimeCol time(7) NOT NULL,\r\n\tTinyIntCol tinyint NOT NULL,\r\n\tGUIDCol uniqueidentifier NOT NULL,\r\n\tVarcharCol varchar(50) NOT NULL,\r\n\tCONSTRAINT PK_TestImplicitConverts PRIMARY KEY CLUSTERED (RowID)\r\n);\r\nGO\r\n<\/pre>\n<p>I then created a nonclustered index on each of the columns in the test table so that a single column query filtering on each column could then generate an execution plan with a single index seek when using the matching data type for the filtering.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n-- Create nonclustered indexes on all columns to test implicit conversion affects\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_BigIntCol ON dbo.TestImplicitConverts (BigIntCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_BitCol ON dbo.TestImplicitConverts (BitCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_CharCol ON dbo.TestImplicitConverts (CharCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_DateCol ON dbo.TestImplicitConverts (DateCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_DateTime2Col ON dbo.TestImplicitConverts (DateTime2Col);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_DateTimeCol ON dbo.TestImplicitConverts (DateTimeCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_DateTimeOffsetCol ON dbo.TestImplicitConverts (DateTimeOffsetCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_DecimalCol ON dbo.TestImplicitConverts (DecimalCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_FloatCol ON dbo.TestImplicitConverts (FloatCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_GUIDCol ON dbo.TestImplicitConverts (GUIDCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_NVarcharCol ON dbo.TestImplicitConverts (NVarchrCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_RealCol ON dbo.TestImplicitConverts (RealCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_SmallDateTimeCol ON dbo.TestImplicitConverts (SmallDateTimeCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_SmallIntCol ON dbo.TestImplicitConverts (SmallIntCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_IntCol ON dbo.TestImplicitConverts (IntCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_MoneyCol ON dbo.TestImplicitConverts (MoneyCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_NCharCol ON dbo.TestImplicitConverts (NCharCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_NumericCol ON dbo.TestImplicitConverts (NumericCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_SmallMoneyCol ON dbo.TestImplicitConverts (SmallMoneyCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_TimeCol ON dbo.TestImplicitConverts (TimeCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_TinyIntCol ON dbo.TestImplicitConverts (TinyIntCol);\r\nGO\r\nCREATE NONCLUSTERED INDEX IX_TestImplicitConverts_VarcharCol ON dbo.TestImplicitConverts (VarcharCol);\r\nGO\r\n<\/pre>\n<p>Finally each of the tables was loaded with 467000 rows of random data and the indexes were rebuilt to remove any fragmentation before testing was started.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nINSERT INTO dbo.TestImplicitConverts\r\n(\tBigIntCol, BitCol, CharCol, DateCol, DateTimeCol, DateTime2Col, DateTimeOffsetCol,\r\n\tDecimalCol, FloatCol, IntCol, MoneyCol, NCharCol, NumericCol, NVarchrCol, RealCol,\r\n\tSmallDateTimeCol, SmallIntCol, SmallMoneyCol, TimeCol, TinyIntCol, GUIDCol, VarcharCol)\r\nSELECT a.number, a.number%1, CAST(b.name AS CHAR(10)), DATEADD(ms, -1*a.number, GETDATE()), DATEADD(ms, -1*a.number, GETDATE()), DATEADD(ms, -1*a.number, GETDATE()), DATEADD(ms, -1*a.number, GETDATE()),\r\n\ta.number, a.number, a.number, a.number, CAST(b.name AS NCHAR(10)), a.number, b.name, a.number,\r\n\tDATEADD(ms, -1*a.number, GETDATE()), a.number, a.number, DATEADD(ms, -1*a.number, GETDATE()), a.number%255, NEWID(), b.name\r\nFROM master.dbo.spt_values AS a\r\nCROSS JOIN master.dbo.spt_values AS b\r\nWHERE a.type = N'P'\r\n  AND a.number &lt; 1000\r\n  AND b.name IS NOT NULL;\r\nGO\r\nALTER INDEX ALL ON TestImplicitConverts REBUILD;\r\nGO\r\n<\/pre>\n<h2>The Test Harness<\/h2>\n<p>The test harness first connects to the instance of SQL Server and retrieves the column list from the database for the TestImplicitConverts table and stores them into an ArrayList that is then used to iterate over the tests, generating dynamic statements and at the same time using a different type for each test.\u00a0 The types are selected from the SqlDbType enumeration in .NET and filter out the types that are not being tested as a part of this investigation.\u00a0 Each test is executed with SET STATISTICS XML ON to collect the actual execution plan for each of the tests for processing to determine if the implicit conversion caused a scan to occur or not.<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n#Load the Showplan as a XML document for parsing\r\n$xml = New-Object System.Xml.XmlDocument;\r\n$xml.LoadXml($String);\r\n\r\n#Setup the XmlNamespaceManager and add the ShowPlan Namespace to it.\r\n$nsMgr = New-Object 'System.Xml.XmlNamespaceManager' $xml.NameTable;\r\n$nsMgr.AddNamespace(&quot;sm&quot;, &quot;&lt;a href=&quot;http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan&amp;quot;);&quot;&gt;http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan&quot;);&lt;\/a&gt;\r\n\r\n#Search for a Index Scan or Clustered Index Scan operation that is an Implicit Conversion\r\n$HasImplictConvert = $xml.SelectNodes(&quot;\/\/sm:RelOp&#x5B;@LogicalOp='Index Scan' or @LogicalOp='Clustered Index Scan']\/sm:IndexScan\/sm:Predicate\/\/sm:Convert&#x5B;@Implicit='1']\/sm:ScalarOperator\/sm:Identifier\/sm:ColumnReference&quot;, $nsMgr).Count -gt 0;\r\n<\/pre>\n<p>Then the results of each test is output to the host as a comma separated list that can then be placed in Excel and used to generate a chart of the implicit conversion effects.<\/p>\n<p><strong>Note:<\/strong>\u00a0 The full test harness and scripts are attached <a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/testimplicitconversions_query_generator.zip\">to this post.<\/a><\/p>\n<h2>Results<\/h2>\n<p>The results of the tests are mapped out in the two charts below.\u00a0 The green blocks show where an implicit conversion occurs but still results in an index seek operation.\u00a0 The yellow blocks show where the implicit conversion causes an index scan to occur.\u00a0 The red blocks show where an operand clash occurs and implicit conversion is not actually supported by SQL Server for the data types, and the grey blocks are the same data type, so no conversion was required.<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/image1.png\"><img fetchpriority=\"high\" decoding=\"async\" style=\"background-image: none; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border-width: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/image_thumb1.png\" width=\"644\" height=\"439\" border=\"0\" \/><\/a><\/p>\n<p><strong>Figure 1 &#8211; Implicit Conversions using SQL_Latin_General_CP1_CI_AS<\/strong><\/p>\n<p>There is a difference between using the SQL_Latin_General_CP1_CI_AS collation and the Latin_General_CI_AS for the char and varchar data types, when converting to nchar or nvarchar.\u00a0 In the SQL collation the conversion results in an index scan as shown above, but in the Windows collation, the scan does not occur and an index seek is still used to execute the query.<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/image2.png\"><img decoding=\"async\" style=\"background-image: none; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border-width: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/image_thumb2.png\" width=\"644\" height=\"441\" border=\"0\" \/><\/a><\/p>\n<p><strong>Figure 2 &#8211; Implicit Conversions using Latin_General_CI_AS<\/strong><\/p>\n<h2>Conclusion<\/h2>\n<p>While implicit conversions occur in SQL Server to allow data evaluations against different data types, they can introduce performance problems for specific data type conversions that result in an index scan occurring during the execution.\u00a0 Good design practices and code reviews can easily prevent implicit conversion issues from ever occurring in your design or workload.<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/testimplicitconversions_query_generator.zip\">Download scripts here!<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Implicit conversions seem to be a growing trend in the SQL Server performance tuning work that I&#8217;ve been engaged in recently, and I&#8217;ve blogged in the past about ways to identify when implicit conversions are occurring using the plan cache. For those of you who don&#8217;t know, implicit conversions occur whenever data with two different [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49,31,32,38,39],"tags":[],"class_list":["post-1756","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","category-plan-cache","category-powershell","category-sql-server-2008","category-sql-server-2012"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Implicit Conversions that cause Index Scans - Jonathan Kehayias<\/title>\n<meta name=\"description\" content=\"Implicit conversions are a growing problem in the SQL Server performance tuning work. This post maps out the impact of implicit conversions in SQL Server.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Implicit Conversions that cause Index Scans - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"Implicit conversions are a growing problem in the SQL Server performance tuning work. This post maps out the impact of implicit conversions in SQL Server.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2013-04-11T17:50:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:55:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/image_thumb1.png\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Implicit Conversions that cause Index Scans\",\"datePublished\":\"2013-04-11T17:50:40+00:00\",\"dateModified\":\"2017-04-13T16:55:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/\"},\"wordCount\":1313,\"commentCount\":13,\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/04\\\/image_thumb1.png\",\"articleSection\":[\"Performance Tuning\",\"Plan Cache\",\"Powershell\",\"SQL Server 2008\",\"SQL Server 2012\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/\",\"name\":\"Implicit Conversions that cause Index Scans - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/04\\\/image_thumb1.png\",\"datePublished\":\"2013-04-11T17:50:40+00:00\",\"dateModified\":\"2017-04-13T16:55:39+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"description\":\"Implicit conversions are a growing problem in the SQL Server performance tuning work. This post maps out the impact of implicit conversions in SQL Server.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/04\\\/image_thumb1.png\",\"contentUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/04\\\/image_thumb1.png\",\"width\":644,\"height\":439},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/implicit-conversions-that-cause-index-scans\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance Tuning\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/performance-tuning\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Implicit Conversions that cause Index Scans\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Implicit Conversions that cause Index Scans - Jonathan Kehayias","description":"Implicit conversions are a growing problem in the SQL Server performance tuning work. This post maps out the impact of implicit conversions in SQL Server.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/","og_locale":"en_US","og_type":"article","og_title":"Implicit Conversions that cause Index Scans - Jonathan Kehayias","og_description":"Implicit conversions are a growing problem in the SQL Server performance tuning work. This post maps out the impact of implicit conversions in SQL Server.","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/","og_site_name":"Jonathan Kehayias","article_published_time":"2013-04-11T17:50:40+00:00","article_modified_time":"2017-04-13T16:55:39+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/image_thumb1.png","type":"","width":"","height":""}],"author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Implicit Conversions that cause Index Scans","datePublished":"2013-04-11T17:50:40+00:00","dateModified":"2017-04-13T16:55:39+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/"},"wordCount":1313,"commentCount":13,"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/image_thumb1.png","articleSection":["Performance Tuning","Plan Cache","Powershell","SQL Server 2008","SQL Server 2012"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/","name":"Implicit Conversions that cause Index Scans - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/image_thumb1.png","datePublished":"2013-04-11T17:50:40+00:00","dateModified":"2017-04-13T16:55:39+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"description":"Implicit conversions are a growing problem in the SQL Server performance tuning work. This post maps out the impact of implicit conversions in SQL Server.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/image_thumb1.png","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/04\/image_thumb1.png","width":644,"height":439},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/implicit-conversions-that-cause-index-scans\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Performance Tuning","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/performance-tuning\/"},{"@type":"ListItem","position":3,"name":"Implicit Conversions that cause Index Scans"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1756","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=1756"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1756\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=1756"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=1756"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=1756"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}