1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
use tempdb
GO
SET NOCOUNT ON
GO
CREATE SEQUENCE seq1 START WITH 0 INCREMENT BY 1 ;
create table t (
a0 int not null PRIMARY KEY CLUSTERED,
a1 int not null,
a2 int not null,
a3 int not null,
a4 int not null,
a5 int not null,
a6 int not null,
a7 int not null,
a8 int not null,
a9 int not null
);
GO
INSERT INTO t SELECT
NEXT VALUE FOR seq1,
NEXT VALUE FOR seq1 % 13,
NEXT VALUE FOR seq1 % 17,
NEXT VALUE FOR seq1 % 5,
NEXT VALUE FOR seq1 % 101,
NEXT VALUE FOR seq1 % 107,
NEXT VALUE FOR seq1 % 1291,
NEXT VALUE FOR seq1 % 1000000,
NEXT VALUE FOR seq1 % 17471,
NEXT VALUE FOR seq1 % 86453
GO 100
INSERT INTO t
SELECT
NEXT VALUE FOR seq1,
NEXT VALUE FOR seq1 % 13,
NEXT VALUE FOR seq1 % 17,
NEXT VALUE FOR seq1 % 5,
NEXT VALUE FOR seq1 % 101,
NEXT VALUE FOR seq1 % 107,
NEXT VALUE FOR seq1 % 1291,
NEXT VALUE FOR seq1 % 1000000,
NEXT VALUE FOR seq1 % 17471,
NEXT VALUE FOR seq1 % 86453
FROM (SELECT TOP 99 * FROM t) t
GO 100
SET NOCOUNT OFF
GO
INSERT INTO t
SELECT
NEXT VALUE FOR seq1,
NEXT VALUE FOR seq1 % 13,
NEXT VALUE FOR seq1 % 17,
NEXT VALUE FOR seq1 % 5,
NEXT VALUE FOR seq1 % 101,
NEXT VALUE FOR seq1 % 107,
NEXT VALUE FOR seq1 % 1291,
NEXT VALUE FOR seq1 % 1000000,
NEXT VALUE FOR seq1 % 17471,
NEXT VALUE FOR seq1 % 86453
FROM (SELECT TOP 9900 * FROM t) t
GO 100
INSERT INTO t
SELECT
NEXT VALUE FOR seq1,
NEXT VALUE FOR seq1 % 13,
NEXT VALUE FOR seq1 % 17,
NEXT VALUE FOR seq1 % 29,
NEXT VALUE FOR seq1 % 101,
NEXT VALUE FOR seq1 % 107,
NEXT VALUE FOR seq1 % 1291,
NEXT VALUE FOR seq1 % 1000000,
NEXT VALUE FOR seq1 % 17471,
NEXT VALUE FOR seq1 % 86453
FROM (SELECT TOP 900000 * FROM t) t
GO 10
--SELECT CONCAT(10000 * ROW_NUMBER() OVER (ORDER BY 1/0), ',') FROM t;
--GO
--SELECT '[PRIMARY],' FROM t;
--GO
CREATE PARTITION FUNCTION range100 (int)
AS RANGE LEFT FOR VALUES (
10000,
20000,
30000,
40000,
50000,
60000,
70000,
80000,
90000,
100000,
110000,
120000,
130000,
140000,
150000,
160000,
170000,
180000,
190000,
200000,
210000,
220000,
230000,
240000,
250000,
260000,
270000,
280000,
290000,
300000,
310000,
320000,
330000,
340000,
350000,
360000,
370000,
380000,
390000,
400000,
410000,
420000,
430000,
440000,
450000,
460000,
470000,
480000,
490000,
500000,
510000,
520000,
530000,
540000,
550000,
560000,
570000,
580000,
590000,
600000,
610000,
620000,
630000,
640000,
650000,
660000,
670000,
680000,
690000,
700000,
710000,
720000,
730000,
740000,
750000,
760000,
770000,
780000,
790000,
800000,
810000,
820000,
830000,
840000,
850000,
860000,
870000,
880000,
890000,
900000,
910000,
920000,
930000,
940000,
950000,
960000,
970000,
980000,
990000,
1000000);
GO
CREATE PARTITION SCHEME RANGE100ps
AS PARTITION RANGE100
TO (
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY],
[PRIMARY]);
create table t_CS (
a0 int not null,
a1 int not null,
a2 int not null,
a3 int not null,
a4 int not null,
a5 int not null,
a6 int not null,
a7 int not null,
a8 int not null,
a9 int not null
) ON RANGE100ps(a7);
go
INSERT INTO t_CS
SELECT * FROM t
GO
CREATE CLUSTERED COLUMNSTORE INDEX IX_T_CS ON t_CS ON RANGE100ps(a7);
GO
CREATE INDEX IX_T_a7a3 ON t(a7, a3) INCLUDE (a4)
GO
CREATE INDEX IX_T_a3456 ON t(a3,a4,a5,a6)
GO
CREATE INDEX IX_T_a469 ON t(a4,a6,a9)
GO
CREATE INDEX IX_T_a829 ON t(a8,a2,a9) INCLUDE (a3, a4)
GO
CREATE INDEX IX_T_a137 ON t(a1,a3,a7) INCLUDE (a2, a4)
GO
SELECT @@VERSION
GO
-- распределение по партициям
SELECT COUNT(*), a7 / 10000 FROM t_CS
GROUP BY a7 / 10000
ORDER BY a7 / 10000
GO
DBCC FREEPROCCACHE
GO
SET STATISTICS TIME ON;
SELECT a7, a3, a4 FROM t WHERE a7 = 33 --AND a3 = 2
SET STATISTICS TIME OFF;
GO
DBCC FREEPROCCACHE
GO
SET STATISTICS TIME ON;
SELECT a7, a3, a4 FROM t_CS WHERE a7 = 33 --AND a3 = 2
SET STATISTICS TIME OFF;
GO
DBCC FREEPROCCACHE
GO
SET STATISTICS TIME ON;
UPDATE t SET a3 = a7 WHERE a7 = 33
SET STATISTICS TIME OFF;
GO
DBCC FREEPROCCACHE
GO
SET STATISTICS TIME ON;
UPDATE t_CS SET a3 = a7 WHERE a7 = 33
SET STATISTICS TIME OFF;
GO
EXEC sp_spaceused 't';
GO
EXEC sp_spaceused 't_CS';
GO
DROP SEQUENCE seq1;
GO
DROP TABLE t;
GO
DROP TABLE t_CS;
GO
DROP PARTITION SCHEME RANGE100ps;
GO
DROP PARTITION FUNCTION RANGE100;
GO
