In my previous post, I mentioned the use of functions, specifically ISNULL and the effects on SARGability of the query. There is another one that often catches out developers and DBAs.

To start off I have created the following two tables and populated them with the same 3 million rows:

Both have clustered index on Id and a non-clustered on DisplayName

CREATE NONCLUSTERED INDEX [IX_MyUnicodeUsers_DisplayName]
ON [dbo].[MyUnicodeUsers] ([DisplayName])
GO

CREATE NONCLUSTERED INDEX [IX_MyUsers_DisplayName]
ON [dbo].[MyUsers] ([DisplayName])
GO

The tables are very similar with the only difference being that AboutMe and DisplayName columns in MyUnicodeUsers are of data type nvarchar, unlike varchar data type in MyUsers table. Before running any queries, please select “Include Actual Execution Plan”. This can be easily done using the keyboard shortcut, “Ctrl + M”.

Now let’s run the following two queries against MyUnicodeUsers table:

SELECT Id, DisplayName
FROM MyUnicodeUsers 
WHERE DisplayName = 'Paul'
SELECT Id, DisplayName
FROM MyUnicodeUsers 
WHERE DisplayName = N'Paul'

The execution plans look as follows:

Nothing to worry there. Now let’s run the same two queries against MyUsers table:

SELECT Id, DisplayName
FROM MyUsers 
WHERE DisplayName = 'Paul'
SELECT Id, DisplayName
FROM MyUsers 
WHERE DisplayName = N'Paul'

And here’s the execution plan:

Huh? What happened there?

When the last query ran, SQL Server decided to scan the non-clustered index instead of doing a seek, like it did in the three previous queries. With one of the queries run against MyUnicodeUsers table, I did compare the varchar parameter to nvarchar data type column. And that seemed fine. So why the difference?

The answer to that lies in this MSDN article.

The nvarchar data type has higher precedence than varchar data type. So when the operator compares the DisplayName varchar column with the nvarchar parameter, it will implicitly convert the DisplayName column, making the predicate non-sargable and, thereby, hindering the seek on the non-clustered index. Well, well… that wasn’t quite what I expected.

If you looks closely, and hover your mouse over the execution plan that did a seek on MyUnicodeUsers table. even though the two data types – column and parameter – were different, you will notice:

There was an implicit conversion, although it did not affect the sargability of our query as it wasn’t the column that got converted, but the parameter passed.

Hmmmm, I wonder how many queries running on my database are suffering from scans due to implicit conversions on the column.

Fortunately, Jonathan Kehayias has provided a script in this article, which is as follows:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
DECLARE @dbname SYSNAME 
SET @dbname = QUOTENAME(DB_NAME()); 
WITH XMLNAMESPACES 
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT 
   stmt.value('(@StatementText)[1]', 'varchar(max)'), 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'), 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'), 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'), 
   ic.DATA_TYPE AS ConvertFrom, 
   ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, 
   t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo, 
   t.value('(@Length)[1]', 'int') AS ConvertToLength, 
   query_plan 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) 
JOIN INFORMATION_SCHEMA.COLUMNS AS ic 
   ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') 
   AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') 
   AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') 
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1
and ic.DATA_TYPE = 'varchar'

This will display details of any queries suffering from scans due to implicit conversions to a column. Fantastic!

The post VARCHAR = @NVARCHAR and SARGability appeared first on SQLYSE.