0 comments
Various 
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.
SELECT
COUNT(*)
FROM #dates
WHERE
CONVERT(DATE, the_date) = CONVERT(DATE, GETDATE());
GO
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