Efficiency using "in" SQL

[kuhns_m]

What is more efficient using in or using or. For example
Select * from location where location in ('1','2','3','4','5')
or
select * from location where location = '1' or location = '2' or location = '3' or location = '4' or location = '5'
Which one is more efficient and how much more efficient. Any articles on this would be appreciated too.
Thanks in advance.

[panayotisk]

I do not know, but I guess they should be the same. Execute some queries using the profiler to see if there are differences. But I suppose that the 1st form is internally translated to the 2nd anyway.

[Madhi]

I think both will give the same result with same performance. If there are millions of records then you may see some difference

Madhivanan

[hspc]

But I suppose that the 1st form is internally translated to the 2nd anyway.
Yes
SQL servers's MOC tells the same :)

[NigelQ]

Also, if you are wanting efficiency, using "SELECT *" is a really big hit on the dataabse.

Wherever possible, use the column names (even if it's all of them), which will have a significant impact on the query performance.

The reason for this is the way the call is cached inside the database. When using select *, the query really can't be cached, so the next time it's called, the column names need to be collected again. This is not strictly true, but gives you an idea of what's going on behind the scenes.

In addition, if you have the option to use stored procedures, they can be significantly quicker than queries posted from clients - I've seen improvements of up to 4000 times quicker using stored procedures.

Hope this helps,

- Nigel