'Creating Incremental Statistics

I want to enable Incremental Statistics on my table. Here is some code

create table tmp.TestTable
(
  ID INT NOT NULL IDENTITY(1,1)
  ,StDate int
  ,Price int 
  ,Quantity decimal(24,9)
)

CREATE PARTITION FUNCTION udf_PartitionByIdDate(int) 
AS RANGE RIGHT 
FOR VALUES(   
     20190101 
    ,20190201 
    ,20190301 
    ,20190401 
    ,20190501 
    ,20190601 
    ,20190701 
    ,20190801 
    ,20190901 
    ,20191001 
    ,20191101 
    ,20191201
    );
GO

CREATE PARTITION SCHEME ups_partionByIdDate_scheme
    AS PARTITION  udf_PartitionByIDDate
    ALL TO ([PRIMARY])
GO

-- create index
CREATE UNIQUE CLUSTERED INDEX CIX_Par56 on tmp.TestTable ([Id] ,[StDate])  
with (STATISTICS_INCREMENTAL=ON) ON ups_partionByIdDate_scheme([StDate])

I have got the error

This type of statistics is not supported to be incremental.

when I want to create index.

Can someone help me?

sqlsql-server


Solution 1:[1]

when you are creating partitioning through index, engine has also to create statistics for index. statistics_incremental = ON means that engine will only add statistics on top of already created stats. However, there is no statistics if you are creating a new clustered index. So turn off the statistics incremental. create the index and after that turn on incremental statisitics.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Achin

Related Questions