BUG: Statistics cannot be set to incremental when the Partition Function contains a "." (dot)
We are moving SQL 2012 databases to SQL 2016, and some databases contain partitioned tables.
Being SQL2016, I want to make use of incremental statistics, but when I enable incremental statistics on the existing statistics/indexes, I always received the following error: "Incorrect syntax near '.'."
This error shows up for each partition that contains data.
After a lot of testing, I found out that it is caused by the name of the Partition Function.
This contains a "." ("PF_dbo.ID") .
Has anybody run into this problem and found a way to solve it without needing to rebuild each partitioned table?
This would take a long downtime of the database, and is something I would like to avoid.
I consider this being a bug and it was perfectly possible to create the partition function with a "." in the name in the first place.
It's even more convincing that this is a bug when you consider the following situation:
Create a table that uses the Partition Function with a "." in its name, and with incremental statistics from the start.
This works perfectly, no errors thrown.
Insert some records into that table.
Then check the statistics ... the statistic is empty.
Side note: it is perfectly fine that the Partition Scheme contains a ".". Stats can then be set to incremental without a problem.
Thank you, we are reviewing.
Some more details on the root-cause of this bug are found by Erland Sommarskog.
Please refer to "https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3e5a07d4-0f4f-4598-af65-d46df538acfb/bug-statistics-cannot-be-set-to-incremental-when-the-partition-function-contains-a-quotquot?forum=sqldatabaseengine"for details.
In short, this script is called in the background:
SELECT StatMan([SC0], [SC1])
SELECT TOP 100 PERCENT [PARTITIONING_ID] AS [SC0], [ID] AS [SC1]
FROM [dbo].[TestTable] WITH (READUNCOMMITTED)
WHERE $PARTITION.PF_dbo_Test([PARTITIONING_ID]) = 2
ORDER BY [SC0], [SC1]
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 4)
"PF_dbo_Test" is not enclosed in square brackets, causing the error.
Is there any chance to get this fixed?
Here's a script that demonstrates this problem...
Charles Fierens commented
Some extra SQL-version info:
Next to SQL2016, this problem is also present on SQL2017
(SQL2016 is on SP2 CU5, SQL2017 on CU13)