June 16, 2010 at 12:58 pm
Eugene Elutin (6/16/2010)
Elliott Whitlow (6/16/2010)
...I don't really have any objection if that column is used for comparisons only, if they ever want to display it I might consider smalldatetime...You can easely convert INT back to datetime or smalldatetime (without time portion) for display purposes.
I was thinking we were talking about storage space. But as I said, it depends, I would want to know how it will be used and how often. I like to keep my options open..
CEWII
June 16, 2010 at 1:27 pm
http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/
If you can, use the datediff on only one column or on the variable/parameter so that the predicate is sargable (though if you're comparing two columns it may not make that much of a difference). If you're comparing a column with a variable/parameter, make sure that the functions are used on the variable/parameter, not the column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2010 at 1:35 pm
scott.pletcher (6/16/2010)
However, as you are working in a larger environment, the optimizer may even choose to use a non-covering index with less than fantastic selectivity just because the cost of the scan is so enormously high. In this case you could see a key lookup to pick up the uncovered columns.
...
Potentially instead of doing a DATEDIFF which includes both columns, you could hypothetically leave the one date column alone and do a comparison with a DATEADD on the other column, but since the other column isn't SARGable you'll still have it looking at each record. In a huge environment this will result in a crushing performance hit unless the result set is otherwise greatly reduced by some other criteria.
Isn't that somewhat self-contradictory? Isn't it better to have one of them SARGable than neither? Assuming you already have an index on that one column (if neither column is in any index, don't see how you avoid a scan no matter what you do). I would think the other column would have to be in the index as well, even if just INCLUDEd .... but the optimizer gets better all the time, so I won't try to limit it 🙂 .
I believe he wanted specific code of how to do the compare leaving one column alone, not just a generic statement.
First of all, I agree with the statement made elsewhere that the better solution is to maintain the persisted calculated column that maintains the datediff and will be index-able. For the specific question asked by the OP, this is likely to be the way to go.
However, it terms of the specific theoretical discussion above regarding a comparison of two date columns within a table ...
While the one column has the appearance of being SARG-able, it is not truly so. The optimizer will never be able to use an index on it because the other column to which it is being compared will need to be evaluated for each record, thus there is nothing objective for the index to function. Now, if instead of comparing column1 to column2 you were comparing column1 to @dt_yourDateVariable, then the actual SARG-ability would be affected by changing the datediff function to leaving the column1 alone and comparing to a dateadd on the variable.
While you generally ruin SARG-ability by performing functions on the column in the where clause, not performing functions on the column there does not necessarily guarantee that it will be SARG-able. (And obviously, even if the optimizer *can* use a particular index doesn't mean that it will.)
Genuinely interesting and potentially practical discussion ... I've seen many times that date comparisons have been the cause of major performance snags.
*edit: And while I was typing Gail said what I was trying to get at, only much more concisely.
June 16, 2010 at 1:41 pm
Yes, SARGable was the wrong term for me to use.
But if there is a covering index which includes both columns, and the date1_col is in the first / next position with all other key values specificed, hopefully the optimizer would still be able to use the index as a covering index. That is the point I was trying to make.
If, however, both columns are manipulated, SQL will never consider an index at all (at least as I understand how the current version of the optimizer works).
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply