sql server - sql statements with equals vs in

Question

Say that someone came up to you and said we're going to cut down the amount of SQL that we write by replacing equals with IN. The use would be both for single scalar values and lists of numbers.

SELECT * 
  FROM table 
 WHERE id = 1

OR

SELECT * 
  FROM table 
 WHERE id IN (1)

Are these statement equivalent to what the optimizer produces?

This looks really simple on the surface, but it leads to simplification for two reasons: 1. large blocks of SQL don't need to be duplicated, and 2. we don't overuse dynamic SQL.

This is a contrived example, but consider the following.

select a.* from tablea a 
join tableb b on a.id = b.id
join tablec c on b.id2 = c.id2
left join tabled d on c.id3 = c.id3
where d.type = 1

... and the same again for the more than one case

select a.* from tablea a 
join tableb b on a.id = b.id
join tablec c on b.id2 = c.id2
left join tabled d on c.id3 = c.id3
where d.type in (1,2,3,4)

(this isn't even a large statement)

conceivably you could do string concatenation, but this isn't desirable in light of ORM usage, and dynamic SQL string concatenation always starts off with good intentions (at least in these parts).

Answer 1

The two will produce the same execution plan - either a table scan, index scan, or index seek, depending on if/how you have your table indexed.

You can see for yourself - Displaying Graphical Execution Plans (SQL Server Management Studio) - See the section called "Using the Execution Plan Options".