Paul White (4/28/2009)
It sounds as if you are quoting percentage batch costs from the execution plan.
The results were from batch queries, 100 each with random dates for each iteration, I just rounded the results up because I was more interested in the severe differences between what was posted in the article and other suggestions.
Paul White (4/28/2009)
I am confused as to how a computed column slows down your data loads 'drastically'. Also, simple scalar calculations in the SELECT part of an INSERT are typically so much cheaper than the INSERT operation that it makes no odds.
You're right, I made a gross generalization, and actually didn't elaborate well what I was thinking. Computed columns have no impact on loads and they're only computed for indexing purposes or during queries; the same impacts you'd see by bulk updating. In this instance, since truncating a date using floor would be deterministic, it would actually be OK to use computed columns with an index as opposed to computing the value and sticking in it another column.
What I was really aiming for, however, is that if the principal query against your table is going to be on date ranges, if you're looking to avoid the time portion of it, eliminate it from the equation all together. Calling on UDFs or math functions inside of a query, although optimized by SQL Server itself, will still be slower than computing the date portion once and querying multiple times against it. If you have to do the calculation more than once, you're just wasting the servers time by making it repeat itself; compute it and save it, the disk cost is negligible compared to the performance gains you'll achieve.