Home Forums SQL Server 2005 T-SQL (SS2K5) Datediff versus Convert for datetime comparison (ignoring time part) RE: Datediff versus Convert for datetime comparison (ignoring time part)

  • Overall, for SQL to use any index, it has to highly selective or a covering index: I suspect that's unlikey here.

    In a smaller environment you'll see the optimizer frequently do without a given index unless it's really perfect for the job. On the smaller tables the cost of the scan is simply not high enough to justify the use of the index. 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.

    The big thing is not to guess ... do testing and see what the issues are with the execution plan in your environment. It very well may even differ greatly from dev to test to prod.

    Of course you can avoid any manipulation on one of the columns by doing two manipulations on the other one :-). Then at least one of the columns is SARGable.

    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. In a really small environment you shouldn't really notice the inefficiency of date manipulations. So again, it's just up to you to test to figure out what works for your specific environment.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]