Bug: Implementing Incremental Statistics
Published: 25th May 2015
I’ve covered Incremental Statistics over a couple of posts now and although they are a fantastic feature, there is a bug which you need to be aware of. Luckily it’s by no means a showstopper, more just an annoyance.

The bug? For some reason SQL Server won’t actually let you create an index with incremental statistics enabled unless you’re replacing an existing partitioned index. Yup - weird.

There’s nothing really needing to be said about this, we just need to run a simple piece of code…

set nocount on

use
AdventureWorks2012
go

create partition function pf (int)
as range right for values(10, 20, 30, 40, 50, 60)
go

create partition scheme ps
  
as partition pf all to ([primary])
go

create table partitionTable
(
  
id int identity,
  
value varchar(5) default('aaaaa')
)
go

insert into partitionTable
default values
go 55

-- This will fail
create clustered index ix_partitionTable on partitionTable(id) with (drop_existing = on, statistics_incremental = on) on ps(id)
go

select *
from sys.stats
where object_id = object_id('partitionTable')
go

if object_id('partitionTable') is not null drop table partitionTable
go

if exists(select * from sys.partition_schemes where name = 'ps')
  
drop partition scheme ps

if exists(select * from sys.partition_functions where name = 'pf')
  
drop partition function pf
go


There was nothing wrong with that code, so what happened?

Well I’ve no actual idea why this is happening, but I do know how to work around it… If anyone has an easier way then do let me know as this is a pain, but we need to create a partitioned index WITHOUT incremental statistics and then do a rebuild WITH incremental statistics.

This can be a complete pain because invariably a table is partitioned because it’s large and therefore the act of creating an index is heavy and time consuming therefore the last thing we want to do is to perform that twice!!! But this seems to be the only way I can get this to work.
Comments:
NB: Comments will only appear once they have been moderated.