1

I am using the following where predicate to get only Monday to Fridays tickets from one of our DW tables.

SET DATEFIRST 1

SELECT TicketID 
FROM Tickets AS T
WHERE DATEPART(DW,T.StartDate) BETWEEN 1 AND 5 ....`

Would it be possible to let me know if the DATEPART predicate is sargable please?

I took a look at the actual execution plan and it uses an index seek even with the DATEPART function.

We have already optimised many of our old queries by modifying the where predicates, analysing the plan, set statistics io on, etc. For this one I am not sure if there is a better way to get tickets only from mon - fri (shifts..)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mathias Florin
  • 48
  • 3
  • 19

1 Answers1

2

The issue with your query is likely index selectivity because of your BETWEEN clause. If you're selecting 5 out of 7 week days, the optimizer is going to perform a table scan.

Brad Schoening
  • 1,281
  • 6
  • 22
  • We already used an index seek before instead of a table scan. We changed the design anyway now to a date dimension join instead of the where predicate. – Mathias Florin Nov 11 '16 at 15:40