Heh... I see now... first, of course the rowcounts will be/are identical. And, of course your testing shows that WHERE IN will be much faster... you changed the problem...

Do your tests all come back with the same rowcounts? Remember... it's random data and SomeInt has different counts for different values.

And, no... I didn't get any Index Seeks......

Heh... I believe I have it... ROUND just shouldn't be used for display purposes... in Books Online, they say...

This example shows two expressions illustrating that with the ROUND function the...

I get the correct answers by copying and pasting the code directly from the original post in SQL Server 2005...

In SQL Server 2000, no dice... @a and @c both...

Heh... sure... you've done something wrong in the code. 😉

There is no way possible to figure out what's wrong from here with what you posted. Discounting all of the...

Absolutely not true. Take a look...

Thanks for the feedback, Jorge. Let us know if there's anything else we can do to help.

Heh... good luck.

aevans1981 (11/5/2008)

---

Thanks for that Jeff, that worked great.

I have commented out the swap section as i am using this to calculate the time taken to turn around orders from when...

that's good news, Sam. Thank you very much for the feedback. Very happy I could help.

Thanks for the feedback, Bryant.

I'd be tempted to use the one that uses the fewest resources... but lemme ask... are you returning any output from the procs to the screen?

SQLBill (11/4/2008)

---

It looks like the OP is looking for exercises for ORACLE not SQL Server.

Quote:

where I can get good SQL exercise to practise sql queries and pl sql

Bolding is mine...

Heh... or maybe "Stormtrooper Clones'R'Us" 😛

My second swag would be that, as you suggest, both will cause some sort of index scan.... but because the WHERE NOT IN has only 42.857% as many items to...