0

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?

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
NewMe
  • 35
  • 1
  • 8
  • This appears to be a little bug/shortcoming in `CREATE INDEX`. The `CREATE INDEX` statement considers the clustered index to be "not aligned" with the base table, even though that very statement is supposed to be the one aligning the table in the first place. If you create the partition function and scheme in advance, and specify the create table with `ON ups_partionByIdDate_scheme([StDate])`, it works. – Jeroen Mostert Apr 17 '19 at 13:20
  • Is this scenario exclusively about a new table, or about moving an existing table to a partition scheme complete with incremental statistics? The former admits some easy syntactical fixes, the latter is more complicated. You can do it without a redundant rebuild of the index, but not (I think) without regenerating the statistics. – Jeroen Mostert Apr 17 '19 at 13:34
  • Does this answer your question? [Jenkins fails when running "service start jenkins"](https://stackoverflow.com/questions/39621263/jenkins-fails-when-running-service-start-jenkins) – JoeF Mar 15 '22 at 22:34

1 Answers1

0

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.

Achin
  • 1
  • CREATE UNIQUE CLUSTERED INDEX CIX_Par56 on tmp.TestTable ([Id] ,[StDate]) with (STATISTICS_INCREMENTAL=OFF) ON ups_partionByIdDate_scheme([StDate]) Then Alter INDEX CIX_Par56 REBUILD with (STATISTICS_INCREMENTAL=ON) – Achin Dec 23 '20 at 06:00