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
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?