Sep 26, 2013

CREATE and UPDATE STATISTICS for memory optimized tables requires the WITH FULLSCAN or RESAMPLE and the NORECOMPUTE options; the WHERE clause is not supported

If we will create a statistics on memory optimized table in sql server 2014 by following script:

CREATE TABLEtblEmp(
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID) WITH (BUCKET_COUNT=1024),
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)

Go

CREATE STATISTICSSta_Name
ON tblEmp (vcName,moSalary)

We may get following error message:

Msg 41346, Level 16, State 1, Line 11
CREATE and UPDATE STATISTICS for memory optimized tables requires the WITH FULLSCAN or RESAMPLE and the NORECOMPUTE options; the WHERE clause is not supported.

Cause: In the memory optimized table in sql server 2014 we must have to create statistics with fullscan and norecompute option. Also there is not any concept of filtered statistics.  For examples:

CREATE STATISTICSSta_Name
ON tblEmp (vcName,moSalary)

WITH FULLSCAN, NORECOMPUTE

No comments:

Post a Comment