T8Bugma1

1DROP TABLE IF EXISTS SMALL_CI;
2
3CREATE TABLE SMALL_CI (
4	FILTER_ID BIGINT NOT NULL,
5	DIM_NAME VARCHAR(100) NOT NULL,
6	PRIMARY KEY (FILTER_ID)
7);
8
9INSERT INTO SMALL_CI VALUES
10(0, 'NAME 1'),
11(1, 'NAME 2'),
12(2, 'NAME 3'),
13(3, 'NAME 4'),
14(4, 'NAME 5'),
15(5, 'NAME 6'),
16(6, 'NAME 7'),
17(7, 'NAME 8'),
18(-1, 'NAME 10'),
19(-2, 'NAME 111')
20
21CREATE STATISTICS S1 on SMALL_CI (FILTER_ID);
22
23
24
25DROP TABLE IF EXISTS MEDIUM_CCI;
26
27CREATE TABLE MEDIUM_CCI (
28	FACT_ID BIGINT NOT NULL,	
29	FILTER_ID BIGINT NOT NULL
30	--,	INDEX CCI CLUSTERED COLUMNSTORE
31);
32
33
34INSERT INTO MEDIUM_CCI WITH (TABLOCK)
35SELECT t.RN
36, CASE WHEN t.RN % 1000 > 5 THEN 0 ELSE t.RN % 1000 END
37FROM
38(
39	SELECT TOP (4000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN -- 64363
40	FROM master..spt_values t1
41	CROSS JOIN master..spt_values t2
42) t;
43
44CREATE STATISTICS S1 ON MEDIUM_CCI (FACT_ID) WITH FULLSCAN;
45CREATE STATISTICS S2 ON MEDIUM_CCI (FILTER_ID) WITH FULLSCAN;
46
47
48
49
50
51DROP TABLE IF EXISTS LARGE_HEAP;
52
53CREATE TABLE LARGE_HEAP (
54	FACT_ID BIGINT NOT NULL,	
55	FILLER VARCHAR(1000) NOT NULL
56);
57
58INSERT INTO LARGE_HEAP WITH (TABLOCK)
59SELECT 40 * t.RN
60, REPLICATE('Z', 100)
61FROM
62(
63	SELECT TOP (16000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 160 RN
64	FROM master..spt_values t1
65	CROSS JOIN master..spt_values t2
66	CROSS JOIN master..spt_values t3
67) t
68OPTION (MAXDOP 1);
69
70CREATE STATISTICS S1 ON LARGE_HEAP (FACT_ID) WITH FULLSCAN;