Statistics on non persisted computed columns
I cannot create the statistics on the column manually I get the error
"Column 'ID_ISNULL' in table 'dbo.TEST_DATES' cannot be used in an index or statistics or as a partition key because it is non-deterministic.".
But when I query the column I see statilink textstics are created on the column, unfortunately when I try to use the column in the where clause it does not seem to use the statistics on the column to get a row estimate.
I attached the script that will create the test table in tempdb and populate it with data.
How can I make the computed column deterministic (it actually is deterministic) ?
Why the row estimates does not use the existing auto created statistics?
The issue is documented in the CAST and CONVERT Books Online topic.
Conversion from a string to a date is deterministic only when a deterministic style is specified. To specify a style, use TRY_CONVERT instead of TRY_CAST. You can use the COLUMNPROPERTY function to see if a column is deterministic or not.
Simple code that is careful with types and explicit about conversions is shown below 🙂
CREATE TABLE dbo.Test ( ID integer NULL, Calc AS CASE WHEN ID NOT BETWEEN 19000000 AND 22000000 THEN NULL WHEN TRY_CONVERT(date, CONVERT(varchar(11), ID), 112) IS NOT NULL THEN TRY_CONVERT(date, CONVERT(varchar(11), ID), 112) END ); -- Returns 1 SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.Test', N'U'), 'Calc', 'IsDeterministic'); -- A test row INSERT dbo.Test (ID) VALUES (19730101) -- Show the table contents SELECT ID, Calc FROM dbo.Test; -- Succeeds CREATE STATISTICS stats_TD_Calc ON dbo.Test (Calc);