add this one to "10 things i wish my developers knew about sql"
Make sure you use the datatype of your columns ! because you avoid your dbms to translate it for you (each and every time again and again)
Jul 13
btw precedence has been changed with sql2000 sp4
Let's hope they don't do that again
I have NEVER posted a reply to any article before. But this one deserves some praise.
I'm a developer and constantly bugging my dba for ways to make my apps runs faster and jump higher.
He has probably already read this article, but I'm going to go sit and show him that developers can be taught if properly beaten...
Nice info to know. Fell foul of the conversion rules with SP4 when trying to use a variable devalred as Decimal(16,0) against a Primary key column defined as Decimal(12,0).
It managed to persuade SQL to do a table scan instead of an Index seek!
Excellent.
I will even forward a link to this to the folks at Learning tree who developed their high performance DB course, and while they covered SARGs, they did not cover anything like this.
Thanks again.
Good article, but I disagree with the DATEDIFF/DATEADD statement "The first query can't possibly use an index and performs the DATEDIFF on every row in the table." used on the DateOfBirth example.
I believe this is because of the "select *", and not because of the DateDiff/DateAdd differences. (Also as a side note, both return a different amount of rows in my testing so I'd say they are not interchangeable). If you reduce the number of fields needed (maybe down to just CustomerID ) and they are also in an index ( DateOfBirth, CustomerID ), then that index can be used in both types of queries.
