{"id":123,"date":"2015-08-04T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/implicit-conversion-mine-field-blowing-sargability-away"},"modified":"2026-03-18T21:52:00","modified_gmt":"2026-03-18T21:52:00","slug":"implicit-conversion-mine-field-blowing-sargability-away","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2015\/08\/04\/implicit-conversion-mine-field-blowing-sargability-away\/","title":{"rendered":"Implicit Conversion Mine Field: Blowing SARGability away! &#8211; SQL Hammer"},"content":{"rendered":"<p><strong>Implicit conversions are convenient. Implicit conversions are EVIL!<\/strong><\/p>\n<p>When talking to professionals about implicit conversions, I\u2019ve heard a few different comments, such as:<\/p>\n<ul>\n<li>(a developer) Don\u2019t waste time converting if the engine does it for you.<\/li>\n<\/ul>\n<ul>\n<li>(a DBA) Sure it probably adds a little bit of overhead but I\u2019ve never found it to be severe enough to worry about.<\/li>\n<\/ul>\n<p>Both of those people were completely missing the point. The developer was focused on development efficiency and\u00a0assuming that the engine would perform the conversion in the most efficient manner possible. The DBA seemed most concerned with the CPU overhead of performing the conversion. Unfortunately, that is not the worst issue with using implicit conversions.<\/p>\n<h2>SARGability<\/h2>\n<p>SARGability is the ability of the query to properly search the arguments that you pass it. Essentially it is whether an index can be effectively utilized with a given predicate. By effectively, I mean using index\u00a0<em>seeks<\/em> instead of\u00a0<em>scans<\/em>. We won\u2019t get too deep into SARGability in general here but I\u00a0will demonstrate how implicit conversions affect it. For other areas, please see these references.<\/p>\n<ul>\n<li><a href=\"http:\/\/sqlblog.com\/blogs\/rob_farley\/archive\/2013\/09\/23\/string-length-and-sargability.aspx\">String Length and SARGability<\/a><\/li>\n<\/ul>\n<ul>\n<li><a href=\"https:\/\/thelonedba.wordpress.com\/2012\/12\/11\/t-sql-tuesday-037-sargability-not-just-for-where-clauses\/\">SARGability \u2013 Not Just for the WHERE Clause<\/a><\/li>\n<\/ul>\n<ul>\n<li><a href=\"http:\/\/www.brentozar.com\/archive\/2010\/06\/sargable-why-string-is-slow\/\">SARGability: Why %String% is slow<\/a><\/li>\n<\/ul>\n<h2>Implicit conversions<\/h2>\n<p>An implicit conversion occurs when one side of your predicate is not the identical data type as the other side of the predicate. Each data type has a <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms190309.aspx\">precedence rank<\/a> and lower data types will be converted to the one with the higher precedence. These can occur with any type of predicate such as column-to-column, column-to-variable, column-to-scalar-function, scalar-function-to-scalar-function, etc.<\/p>\n<p>There is some overhead with performing a conversion but that is not going to be our focus. What is really important is where the implicit conversion occurs. Let\u2019s demonstrate while we explain.<\/p>\n<h3>Demonstration<\/h3>\n<p><em><a href=\"\/wp-content\/uploads\/Implicit-Conversions.sql_.txt\">Click here<\/a> to download the complete demonstration setup script. I omitted the table creation, population, and indexing for the sake of brevity.<\/em><\/p>\n<p>Even though there is a precedence of data types, this only applies to data types which are compatible. Here we get a red error message from an improper conversion.<\/p>\n<pre><code class=\"language-\">DECLARE @bigintParam BIGINT = 152\n\n<p>SELECT bigintNum, tinyintNum\nFROM #ImplicitConverts\nWHERE varcharString = @bigintParam<\/code><\/pre>\n<\/p>\n<p><a href=\"\/wp-content\/uploads\/bigint-error1.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/bigint-error1.png\" alt=\"\" \/><\/a><\/p>\n<p>Now we will play with compatible data types and examine their performance.<\/p>\n<pre><code class=\"language-\">DECLARE @tinyintParam TINYINT = 152\nDECLARE @varcharParam VARCHAR(100) = '152'\n\n<p>SELECT tinyintNum\nFROM #ImplicitConverts\nWHERE tinyintNum = @tinyintParam<\/p>\n\n<p>SELECT bigintNum\nFROM #ImplicitConverts\nWHERE bigintNum = @tinyintParam<\/p>\n\n<p>SELECT bigintNum\nFROM #ImplicitConverts\nWHERE bigintNum = @varcharParam<\/code><\/pre>\n<\/p>\n<p>There are three\u00a0interesting notes about the execution of these queries.<\/p>\n<p>1. All three queries utilized index seeks.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-plan1.png\" alt=\"\" \/><\/p>\n<p><a href=\"\/wp-content\/uploads\/implicit-conversion-plan2.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-plan2.png\" alt=\"\" \/><\/a>\u00a0\u00a0<a href=\"\/wp-content\/uploads\/implicit-conversion-plan3.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-plan3.png\" alt=\"\" \/><\/a><\/p>\n<p>2. The first query didn\u2019t need to convert because the data types were a match but the second two queries did require an implicit conversion.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-stats1.png\" alt=\"\" \/><\/p>\n<p><a href=\"\/wp-content\/uploads\/implicit-conversion-stats3.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-stats3.png\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"\/wp-content\/uploads\/implicit-conversion-stats2.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-stats2.png\" alt=\"\" \/><\/a><\/p>\n<p>3. All three were very fast (less than 1\/3rd of a second).<\/p>\n<p><a href=\"\/wp-content\/uploads\/implicit-conversion-stats-full3.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-stats-full3-300x113.png\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"\/wp-content\/uploads\/implicit-conversion-stats-full1.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-stats-full1-300x114.png\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"\/wp-content\/uploads\/implicit-conversion-stats-full2.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-stats-full2-300x115.png\" alt=\"\" \/><\/a><\/p>\n<p><strong>What is wrong with the implicit conversion then?<\/strong><\/p>\n<p>In the cases above, the implicit conversion occurred on the parameter side, not on the column side of the predicate. This meant that the queries were all SARGable, they could use the index to perform seeks. If you refer back to the <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms190309.aspx\">data type precedence list<\/a>, you will notice that BIGINT is #15, TINYINT is #18 and VARCHAR is #27. This means that, when comparing to a BIGINT, the TINYINT and VARCHAR will incur the implicit conversion and become a BIGINT that the index can effectively use for a seek.<\/p>\n<p>When the implicit conversion occurs on the column side of the predicate, our land mines begin to explode.<\/p>\n<p>This next demo uses an NVARCHAR (precedence #25) variable with a VARCHAR (precedence #27) column. Since the column\u00a0is of a lower precedence than the variable, our implicit conversion will occur on the column and force an index scan.<\/p>\n<pre><code class=\"language-\">DECLARE @nvarcharParam NVARCHAR(100) = N'7371varcharString'\n\n<p>SELECT varcharString, nvarcharString\nFROM #ImplicitConverts\nWHERE varcharString = @nvarcharParam<\/code><\/pre>\n<\/p>\n<p><a href=\"\/wp-content\/uploads\/implicit-conversion-plan4.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-plan4.png\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"\/wp-content\/uploads\/implicit-conversion-stats-full4.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-stats-full4-300x121.png\" alt=\"\" \/><\/a><\/p>\n<p>28 seconds compared with less than 1\/3rd of a second. That\u00a0says it all.<\/p>\n<h2>Solution<\/h2>\n<p>The solution to this problem is simple. All you need to do is match your data types during development \/ design or utilize explicit conversions so that your conversion occurs on variables and not columns. The design aspect is particularly important when dealing with JOINs.<\/p>\n<p>This CAST solves our issue from the previous query.<\/p>\n<pre><code class=\"language-\">SELECT varcharString, nvarcharString\nFROM #ImplicitConverts\nWHERE varcharString = CAST(@nvarcharParam AS VARCHAR(100))<\/code><\/pre>\n<p><a href=\"\/wp-content\/uploads\/implicit-conversion-plan5.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-plan5.png\" alt=\"\" \/><\/a> <a href=\"\/wp-content\/uploads\/implicit-conversion-stats-full5.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/implicit-conversion-stats-full5-300x114.png\" alt=\"\" \/><\/a><\/p>\n<hr \/>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/hammer-derik-172x140.png\" alt=\"\" \/><strong>Written by Derik Hammer of <a href=\"http:\/\/www.sqlhammer.com\/\">SQL Hammer<\/a><\/strong><\/p>\n<p>Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/high-availability\/\">high-availability<\/a>, <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/general\/disaster-recovery\/\">disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n<p>Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started <a href=\"http:\/\/www.sqlhammer.com\/\">SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n<p>Derik is the owner and lead author of SQL Hammer, a <a href=\"http:\/\/www.sqlhammer.com\/\">Microsoft SQL Server resource<\/a>.<\/p>\n<p>For more information, visit <a href=\"http:\/\/www.sqlhammer.com\/\">http:\/\/www.sqlhammer.com<\/a>. Follow Derik on <a href=\"http:\/\/twitter.com\/SQLHammer\">Twitter<\/a> for <strong>SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Implicit conversions are convenient. Implicit conversions are EVIL! When talking to professionals about implicit conversions, I\u2019ve heard a few different comments, such as: (a developer) Don\u2019t waste time converting if the engine does it for you. (a DBA) Sure it probably adds a little bit of overhead but I\u2019ve never found it to be severe [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":536,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-123","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/123","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=123"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/123\/revisions"}],"predecessor-version":[{"id":385,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/123\/revisions\/385"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/536"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=123"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=123"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=123"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}