Ravindra Devrani

SQL Tip: Avoid using implicit conversions.

When we compare two values with different data types, the sql server converts the lower-precedence data type to higher-precedence data type. Let's look at the example below:

-- Note: Note: `CardNumber` is `nvarchar`, there is an index on the `CardNumber` and we are comparing it with an integer 11119775847802


SELECT
CreditCardID,
CardNumber
FROM Sales.CreditCard
WHERE CardNumber = 11119775847802;

Here `CardNumber` is a type of `nvarchar` and we are comparing it with an `integer`. `CardNumber` has an index. There is only one record with CardNumber=11119775847802, so it should read only 1 row to fetch the record. But that won't happen. Sql server database engine reads all the rows to find this record. You wonder why? Ok let me explain.

`CardNumber` is a `nvarchar` type, we are searching for a record : 11119775847802 which is an integer type. Instead of converting 11119775847802 to the `nvarchar`, the SQL Server converts all `CardNumber` rows to an integer, leading to index scan. Are you getting the problem here. We just need a single record, whose CardNumber is 11119775847802, for that the whole table is being scanned.

So, instead of 11119775847802, use '11119775847802', which avoids implicit conversion in a table side, as show below:

SELECT



CreditCardID,
CardNumber
FROM Sales.CreditCard
WHERE CardNumber = '11119775847802';

This query will read 1 row only and use the index effectively.

7 months ago (edited) | [YT] | 4