Chris Morris (7/25/2008)
The recommended method which Jack posted (where are ya, Jack?) will always run fast because the column in the WHERE clause on which it operates isn't subjected to any computation before it can be compared. This is significant whether or not the column is indexed, and more so if you're expecting the index to be used.It baffles me to see code using a function on a column in a WHERE clause on a million-row table, when the computation could be applied - once - to the value which it's being compared to.
But you're correct Orion, Jack's method is specific for Datetime.
Cheers
ChrisM
I'm still around. I just had not felt the need to reply until now.
Orion,
I posted a SQL Server specific solution because this is a SQL Server specific site and forum and SQL Server is what I work with. Now As Chris has said, using a computation on column in a WHERE clause is, in SQL Server, a bad idea. You will get much better performance in SQL Server by doing any needed modification to the parameter/variable than the column. This is also why, in columns that do not need time, I would set the time to 00:00:00.
As always a good discussion.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question