3

I'm in the process of tuning a query in SQL Server 2012 that is spilling to tempdb due to an incorrect cardinality estimation when using the DATENAME() function on a column to check if the data is weekend or weekday. Due to the the use of the function, the query is non-sargable and mis-estimates the number of rows (est 1700, actual 38000).

The where clause was simply:

WHERE DATENAME(WEEKDAY, vqc.DateRecorded) NOT IN ('Saturday', 'Sunday')

I was hoping to use a Persisted Computed Column and then index it:

ALTER TABLE <table_name> ADD IsWeekend AS DATENAME(WeekDay, DateRecorded) PERSISTED;

However get the error:

Computed column 'IsWeekend' in table cannot be persisted because the column is non-deterministic.

According to BOL, a large number of Date functions cannot be persisted because they are non-deterministic.

This is because the results depend on the LANGUAGE and DATEFORMAT settings of the server session. For example, the results of the expression CONVERT (datetime, '30 listopad 1996', 113) depend on the LANGUAGE setting because the string '30 listopad 1996' means different months in different languages. Similarly, in the expression DATEADD(mm,3,'2000-12-01'), the Database Engine interprets the string '2000-12-01' based on the DATEFORMAT setting.

There was an interesting way around this issue for the MONTH() function using a CASE statement that was posted on Stack Overflow, but this doesn't work for DATENAME due to SET DATEFIRST and the LANGUAGE setting.

Aside from joining to a calendar table in my query and then filtering the weekends from there, is there a way of determining whether a date is the weekend so it can be persisted and then indexed?

Or am I trying to overcomplicate the situation?

5

You can do a datediff between 1900-01-01, that just happens to be a monday, and check if modulo 7 is a 5 or a 6.

create table D
(
  DateRecorded date,
  IsWeekend as cast(case when datediff(day, 0, DateRecorded) % 7 in (5, 6) 
                      then 1 
                      else 0 
                    end as bit) persisted
)

BTW, you don't need to persist the computed column in order to use it in an index.

  • Interesting solution! – Mark Sinkinson Dec 18 '14 at 13:00
  • Clever. Would picking a more recent Monday be faster? Something like DATEDIFF(DAY, '2010-01-04', DateRecorded) (obviously the first Monday to check for has to be older than the earliest date in that column). Also, since there is no value above 6, would it be faster to instead test for > 4 or >= 5? Either way, +1. – Solomon Rutzky Dec 18 '14 at 17:36
  • 1
    @srutzky If you want to use a string literal as the fixed date part you need to use convert with a style that is deterministic like convert(datetime, '2010-01-04', 126). in (5, 6) is expanded in the query plan to an or so it is evaluating the expression twice. Using > 4 avoids the double evaluation so probably somewhat faster. – Mikael Eriksson Dec 19 '14 at 5:58
  • 1
    Regarding replacing the IN, yes, that is what I was going for, simplifying from the implicit OR. Regarding the literal date, ok, so just use 40180 instead, which is equivalent to '2010-01-04': DATEDIFF(DAY, 40180, DateRecorded). That would have to be fine since your code is using the same style INT date (although I do hate that style ;-). – Solomon Rutzky Dec 19 '14 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.