About Me

My photo
Kozyatağı, İstanbul, Türkiye

Saturday, July 30, 2016

How to detect if SQL Server IN operator is identical to multiple OR operators


Create a simple table:

CREATE TABLE MYTABLE
(
     ID INT
)

Write an invalid query intentionally, give invalid column name in where clause:

SELECT * FROM MYTABLE WHERE ID_X IN (1, 2, 3)

Sql Server generates the error message as:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'ID_X'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ID_X'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ID_X'.

As you see, for one invalid column, the engine generates 3 same error messages.
Now, lets rewrite the invalid query as below, which has the same meaning:


SELECT * FROM MYTABLE WHERE
ID_X = 1 OR
ID_X = 2 OR
ID_X = 3


Sql Server generates the error message as:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'ID_X'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ID_X'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ID_X'.

As two error messages generated for two versions of query are identical, we can conclude that IN clause is converted to multiple OR clauses in engine side before execution.



No comments:

Post a Comment