Column-statistics-bug-repro-Clustered-Columnstore-Index.sql

/************************************************************
SET UP THE REPRO
************************************************************/

WHILE @@trancount > 0
	ROLLBACK
GO

USE master;
GO

IF DB_ID('StatsTest') IS NOT NULL
BEGIN
	ALTER DATABASE StatsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE StatsTest;
END
GO

CREATE DATABASE StatsTest;
GO

USE StatsTest;
GO
SET NOCOUNT ON;
GO


/* create */
DROP TABLE IF EXISTS dbo.ModificationCounterClusterCol;
GO

CREATE TABLE dbo.ModificationCounterClusterCol (
	i int identity not null,
	varcharcol varchar(256) default ('foo'),
	tinyintcol tinyint default (2),
	intcol int default (20000),
	GUIDcol uniqueidentifier default (newid()),
	datetime2col datetime2(0) default ('2016-01-01')
);
GO

/* populate */
DECLARE @i INT = 1;
BEGIN TRAN
	WHILE @i < 1000000
	BEGIN
		INSERT dbo.ModificationCounterClusterCol DEFAULT VALUES;
		SET @i=@i+1;
	END
COMMIT
GO 

/* create col stats */
select *
from dbo.ModificationCounterClusterCol
where tinyintcol=2;
GO

select *
from dbo.ModificationCounterClusterCol
where intcol=2;
GO

select *
from dbo.ModificationCounterClusterCol
where datetime2col is null;
GO

select *
from dbo.ModificationCounterClusterCol
where GUIDcol is null;
GO


select *
from dbo.ModificationCounterClusterCol
where varcharcol is null;
GO

CREATE CLUSTERED COLUMNSTORE INDEX ccxtest ON dbo.ModificationCounterClusterCol;
GO



/************************************************************
VERIFICATION: WE SHOULD HAVE FIVE FRESH COLUMN STATS
************************************************************/
SELECT
	modification_counter,
    sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows   
FROM sys.stats AS stat   
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = object_id('dbo.ModificationCounterClusterCol');  
GO


/************************************************************
OK, NOW RUN THE TEST
************************************************************/

/* increment one stat */
UPDATE dbo.ModificationCounterClusterCol
SET varcharcol = 'test'
WHERE i=109;
GO

/* this looks weird, alright */
SELECT
	modification_counter,
    sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows   
FROM sys.stats AS stat   
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = object_id('dbo.ModificationCounterClusterCol');  
GO


/************************************************************
EXTRA CREDIT: TEST ANOTHER COLUMN
************************************************************/
UPDATE dbo.ModificationCounterClusterCol
SET intcol = intcol+1
WHERE i=109000;
GO 100

/* Yep, still weird. */
SELECT
	modification_counter,
    sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows   
FROM sys.stats AS stat   
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = object_id('dbo.ModificationCounterClusterCol');  
GO