Ms Sql Server Compare Numeric column with string value

Question

In MS SQL Server, what is the difference between:

select * from Person where Id='7'

and

select * from Person where Id=7

The two queries returns the same results.

The Id type is int.

should we not compare int to string?

and when should use one of them?

You should avoid implicit conversions. And compare directly to id=7 — Lukasz Szozda
– Lukasz Szozda, Commented Jun 5, 2018 at 9:34
If you want to know what kind of trouble conversions can bring, try the reverse: where name=3, with name a string column, will give errors when the contents of name are converted to int, due to the bizarre precedence rules SQL Server has for implicit conversions. — Jeroen Mostert
– Jeroen Mostert, Commented Jun 5, 2018 at 9:35
To add to @JeroenMostert 's comment: Data type precedence (Transact-SQL). Although, not sure I agree with the "bizarre" statement. :) — Thom A
– Thom A ♦, Commented Jun 5, 2018 at 9:38

EzLo · Accepted Answer · 2018-06-05 09:53:19Z

5

Always compare with the same data type and avoid implicit conversions.

SELECT 'Not OK!' WHERE 'text' = 1
-- Result: Conversion failed when converting the varchar value 'text' to data type int.

As stated by the data type precedence, when there is a mismatch of the data types, the SQL engine will (in most cases) try to convert the most complex type to the simplest, so comparisons are faster. In this case (VARCHAR vs. INT), it will always convert the string type to int.

Also, when coding SQL that has implicit conversions with joins and other operations, it's most likely to generate a different execution plan that the one it would with an explicit conversion.

If you have to compare different types, remember to explicitly cast them, not just for the reason mentioned before but it also says to the next developer that the columns or expressions differ in data type.

SELECT 'OK!' WHERE 'text' = CONVERT(VARCHAR(10), 1)

With some data type you might find unwanted behaviour if you leave implicit conversions.

DECLARE @BitValue1 BIT = NULL
DECLARE @BitValue2 BIT = 1

SELECT
    'Oops!'
WHERE
    ISNULL(@BitValue1, -999) = ISNULL(@BitValue2, -999)

-- Result: Oops! (-999 is being converted to bit, which is 1)

answered Jun 5, 2018 at 9:53

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

You should also mention that improper use of types can affect optimization, such as the use of indexes, table statistics, and partitions.

Collectives™ on Stack Overflow

Ms Sql Server Compare Numeric column with string value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related