I am currently busy reviewing and updating the internal programming guidelines and best practices for SQL Server programming. While looking on the net for other opinions on subjects, I came across a nice set of articles on best practices from Aaron Bertrand: http://sqlblog.com/blogs/aaron_bertrand/archive/tags/best+practices/default.aspx, a recommended read for all T-SQL programmers!
A interesting follow-up on one of the articles was made though by Adam Machanic that undermined one of our own best practices for a bit (which was to not use conversions or functions on the left side of a WHERE clause, because it prevents indexes from being used effectively). What Adam showed was that in SQL 2008, in conjunction with the addition of the DATE type, the query optimizer received a minor upgrade, which makes the following query use an Index Seek instead of a full Index Scan.
CONVERT(DATE, the_date) = CONVERT(DATE, GETDATE());
I am interested in how many more of these query optimizations have been implemented and will be implemented in the future.
Full details on his blog: http://sqlblog.com/blogs/adam_machanic/archive/2009/10/20/what-happened-today-date-and-date-ranges-over-datetime.aspx