In many RDBMS systems, if the value(s) passed to a query for filtering/matching is of different data type than the column data type it is comparing against, an implicit data conversion occurs. This conversion can render the index defined on said column(s) less useful or entirely useless. I encountered one such problem on SQL Server today.

Here is the relevant “WHERE CLAUSE”:
[code language=”sql”]
WHERE DomainNTLogin = SUSER_NAME()
[/code]

Column DomainNTLogin is defined as varchar(100), but SQL Server’s SUSER_NAME() returns nvarchar(128). Note the letter “n” in nvarchar: the difference is subtle but significant. nvarchar is used for languages such as Arabic, Hebrew, Chinese, Japanese, and others. Because of the data type difference, the index defined on DomainNTLogin is not used, one would see a CONVERSION_IMPLICIT in the query’s execution plan:
[code language=”text”]
CONVERT_IMPLICIT(nvarchar(100),[DbName].[SchemaName].[TableName].[ColumnName],0)=SUSER_NAME()
[/code]

What that tells us is that the query engine needs to get all values in ColumnName, which is a scan operation, convert that to nvarchar, before the comparison can be made. Ideally, we’d like to see a seek, instead of a scan!

Fortunately, this problem is easy to fix. Instead of letting the query engine doing the implicit conversion, we do the conversion for it:
[code language=”sql”]
WHERE DomainNTLogin = CAST(SUSER_NAME() AS VARCHAR(100))
[/code]

This change turns a table scan into an index seek, decreasing this query’s execution time from around 16 minutes to less than 1 second!

Years ago, I seem to remember that a JDBC driver for SQL Server from a certain vendor turns all varchar values to nvarchar by default, and I believe/hope there is a setting to turn that off. Inform me, dear reader, if this is the case and you know how to adjust that setting, as needed.