psQZMR3T

1/************************************************************
2SET UP THE REPRO
3************************************************************/
4
5WHILE @@trancount > 0
6	ROLLBACK
7GO
8
9USE master;
10GO
11
12IF DB_ID('StatsTest') IS NOT NULL
13BEGIN
14	ALTER DATABASE StatsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
15	DROP DATABASE StatsTest;
16END
17GO
18
19CREATE DATABASE StatsTest;
20GO
21
22USE StatsTest;
23GO
24SET NOCOUNT ON;
25GO
26
27
28/* create */
29DROP TABLE IF EXISTS dbo.ModificationCounterClusterCol;
30GO
31
32CREATE TABLE dbo.ModificationCounterClusterCol (
33	i int identity not null,
34	varcharcol varchar(256) default ('foo'),
35	tinyintcol tinyint default (2),
36	intcol int default (20000),
37	GUIDcol uniqueidentifier default (newid()),
38	datetime2col datetime2(0) default ('2016-01-01')
39);
40GO
41
42/* populate */
43DECLARE @i INT = 1;
44BEGIN TRAN
45	WHILE @i < 1000000
46	BEGIN
47		INSERT dbo.ModificationCounterClusterCol DEFAULT VALUES;
48		SET @i=@i+1;
49	END
50COMMIT
51GO 
52
53/* create col stats */
54select *
55from dbo.ModificationCounterClusterCol
56where tinyintcol=2;
57GO
58
59select *
60from dbo.ModificationCounterClusterCol
61where intcol=2;
62GO
63
64select *
65from dbo.ModificationCounterClusterCol
66where datetime2col is null;
67GO
68
69select *
70from dbo.ModificationCounterClusterCol
71where GUIDcol is null;
72GO
73
74
75select *
76from dbo.ModificationCounterClusterCol
77where varcharcol is null;
78GO
79
80CREATE CLUSTERED COLUMNSTORE INDEX ccxtest ON dbo.ModificationCounterClusterCol;
81GO
82
83
84
85/************************************************************
86VERIFICATION: WE SHOULD HAVE FIVE FRESH COLUMN STATS
87************************************************************/
88SELECT
89	modification_counter,
90    sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows   
91FROM sys.stats AS stat   
92CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
93WHERE stat.object_id = object_id('dbo.ModificationCounterClusterCol');  
94GO
95
96
97/************************************************************
98OK, NOW RUN THE TEST
99************************************************************/
100
101/* increment one stat */
102UPDATE dbo.ModificationCounterClusterCol
103SET varcharcol = 'test'
104WHERE i=109;
105GO
106
107/* this looks weird, alright */
108SELECT
109	modification_counter,
110    sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows   
111FROM sys.stats AS stat   
112CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
113WHERE stat.object_id = object_id('dbo.ModificationCounterClusterCol');  
114GO
115
116
117/************************************************************
118EXTRA CREDIT: TEST ANOTHER COLUMN
119************************************************************/
120UPDATE dbo.ModificationCounterClusterCol
121SET intcol = intcol+1
122WHERE i=109000;
123GO 100
124
125/* Yep, still weird. */
126SELECT
127	modification_counter,
128    sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows   
129FROM sys.stats AS stat   
130CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
131WHERE stat.object_id = object_id('dbo.ModificationCounterClusterCol');  
132GO