Using IN or not ?

[bab.]

I've read in an Oracle fine tuning and performance book that it was better
to user IN clause rather than multiple OR.

Is it the same thing with SQL Server 7.0?

Thanx

[Michael Levy]

It seems that SQL Server explodes INs to ORs (or at least solves them both
the same way).

I created an index on Authors(state)

CREATE NONCLUSTERED INDEX nc_author_state ON authors(state)

And then ran the following queries:

SELECT * FROM authors WHERE state IN ('CA', 'OR')
SELECT * FROM authors WHERE state = 'CA' OR state = 'OR'

The showplan for the first query:

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[authors]))
|--Index Seek(OBJECT:([pubs].[dbo].[authors].[nc_author_state]),
SEEK:([authors].[state]='CA' OR [authors].[state]='OR') ORDERED FORWARD)

And for the second:

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[authors]))
|--Index Seek(OBJECT:([pubs].[dbo].[authors].[nc_author_state]),
SEEK:([authors].[state]='CA' OR [authors].[state]='OR') ORDERED FORWARD)

-Mike
--
Michael Levy MCDBA, MCSD, MCT
michaell@gasullivan.com