Data Mismatch on WHERE Clause might Cause Serious Performance Problems

  • Article

Like Applying Function on WHERE clause problem, any datatype mismatch on WHERE clause might cause serious performance problems.

Predicates on both sides of comparisons (for example on WHERE clause) always must match datatypes. It means that if the left side of predicate is integer than the right side needs to be integer. If the datatypes are different then SQL Server tries to make and implicit conversion to match the datatypes of both sides like below example.

image

If an implicit conversion is not possible SQL Server returns an error like below.

image

When an implicit conversion is possible, SQL Server automatically converts the data from one data type to another. Selection of the conversion direction depends on data loss possibility. SQL server choose the side which is to avoid data lost. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds. (You can check this link to see all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. )

The potential problem arises when the index column is selected to convert. It makes impossible to use any index on this column and causing serious performance problems

Let’s make some demos to see this problem

 

/*

Turgay Sahtiyan - Microsoft SQL Server Premier Field Engineer

Blog : www.turgaysahtiyan.com

Twitter : @turgaysahtiyan

*/

--Data Mismatch on Where Clause

--Create a work database

create database DBDataMismatch

GO

use DBDataMismatch

GO

--Create a work table

create table tblDataMismatch(ID int identity(1,1),Col1 varchar(10))

GO

--Populate it with 10.000 records

declare @i int=0

while @i<10000 begin

  insert tblDataMismatch

    select cast(@i as varchar(10))

  set @i=@i+1

end

--Create a clustered index on ID column

Create Clustered Index CI_1 on tblDataMismatch(ID)

--Create a nonclustered index on col1 column

Create NonClustered Index IX_1 on tblDataMismatch(Col1)

--Work table is ready

select * from tblDataMismatch

--This query does index seek

select * from tblDataMismatch

Where Col1 = '111'

--But this one does index scan because there is a data mismatch and col1 is selected to convert

select * from tblDataMismatch

Where Col1 = 111

--This also does index scan

declare @value NVarchar(10)='111'

select * from tblDataMismatch

Where Col1 = @Value

--Drop work database

use master

go

drop database DBDataMismatch