Jeff Moden (5/10/2012)
As usual, awesome article, Wayne.It is a bit amazing to me that MS isn't, for some reason, able to do performance wise at the base code level what we're able to do at the "hack" level. I was really shocked when you test the previous row stuff against the Quirky Update. They should spend more time on stuff like making Pivot as useful as it is in Access. On second thought, it's already slow enough. Maybe they should leave it alone. 😀
Thanks Jeff.
One thing to keep in mind is that these functions are not designed to truncate parts of a date. (Well, maybe Format is.) Everything else going on here is using existing functions to truncate a date down to a specific part. I'm sure that if MS ever builds a "TruncateDate" function, it will be fast (though internally it might just do the dateadd/datediff thing?).
So, what is being tested is utilizing one or more functions to accomplish a specific task which these functions were not explicitly designed to perform, but they might be used to accomplish the task. DateAdd/DateDiff - neither is designed for truncating a datetime value. DateTimeFromParts is designed to build a datetime from values, not to truncate. Format could be argued that it is designed to do so, but I see it more as a report formatting tool since it doesn't leave the result in the original data type.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes