N3hn8Sg9

1DROP TABLE IF EXISTS dbo.Test;
2
3IF EXISTS (SELECT * FROM sys.partition_schemes AS PS WHERE PS.[name] = N'PS')
4    DROP PARTITION SCHEME PS;
5
6IF EXISTS (SELECT * FROM sys.partition_functions AS PF WHERE PF.[name] = N'PF')
7DROP PARTITION FUNCTION PF;
8GO
9CREATE PARTITION FUNCTION PF (float)
10AS RANGE RIGHT 
11FOR VALUES 
12(
13    0,
14    1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000,10000000,
15    11000000,12000000,13000000,14000000,15000000,16000000,17000000,18000000,19000000,
16    20000000,21000000,22000000,23000000,24000000,25000000,26000000,27000000,28000000,
17    29000000,30000000,31000000,32000000,33000000,34000000,35000000,36000000,37000000,
18    38000000,39000000,40000000,41000000,42000000,43000000,44000000,45000000,46000000,
19    47000000,48000000,49000000,50000000,51000000,52000000,53000000,54000000,55000000,
20    56000000,57000000,58000000,59000000,60000000,61000000,62000000,63000000,64000000,
21    65000000,66000000,67000000,68000000,69000000,70000000,71000000,72000000,73000000,
22    74000000,75000000,76000000,77000000,78000000,79000000,80000000,81000000,82000000,
23    83000000,84000000,85000000,86000000,87000000,88000000,89000000,90000000,91000000,
24    92000000,93000000,94000000,95000000,96000000,97000000,98000000,99000000,100000000,
25    101000000,102000000,103000000,104000000,105000000,106000000,107000000,108000000,109000000,
26    110000000,111000000,112000000,113000000,114000000,115000000,116000000,117000000,118000000,119000000,
27    120000000,121000000,122000000,123000000,124000000,125000000,126000000,127000000,128000000,129000000,
28    130000000,131000000,132000000,133000000,134000000,135000000,136000000,137000000,138000000,139000000,
29    140000000,141000000,142000000,143000000,144000000,145000000,146000000,147000000,148000000,149000000,
30    150000000,151000000,152000000,153000000,154000000,155000000,156000000,157000000,158000000,159000000,
31    160000000,161000000,162000000,163000000,164000000,165000000,166000000,167000000,168000000,169000000,
32    170000000,171000000,172000000,173000000,174000000,175000000,176000000,177000000,178000000,179000000,
33    180000000,181000000,182000000,183000000,184000000,185000000,186000000,187000000,188000000,189000000,
34    190000000,191000000,192000000,193000000,194000000,195000000,196000000,197000000,198000000,199000000,
35    200000000,201000000,202000000,203000000,204000000,205000000,206000000,207000000,208000000,209000000,
36    210000000,211000000,212000000,213000000,214000000,215000000,216000000,217000000,218000000,219000000,
37    220000000,221000000,222000000,223000000,224000000,225000000,226000000,227000000,228000000,229000000,
38    230000000,231000000,232000000,233000000,234000000,235000000,236000000,237000000,238000000,239000000,
39    240000000,241000000,242000000,243000000,244000000,245000000,246000000,247000000,248000000,249000000,
40    250000000,251000000,252000000,253000000,254000000,255000000,256000000,257000000,258000000,259000000,
41    260000000,261000000,262000000,263000000,264000000,265000000,266000000,267000000,268000000,269000000,
42    270000000,271000000,272000000,273000000,274000000,275000000,276000000,277000000,278000000,279000000,
43    280000000,281000000,282000000,283000000,284000000,285000000,286000000,287000000,288000000,289000000,
44    290000000,291000000,292000000,293000000,294000000,295000000,296000000,297000000,298000000,299000000,
45    300000000
46);
47CREATE PARTITION SCHEME PS
48AS PARTITION PF
49ALL TO ([PRIMARY]);
50GO
51CREATE TABLE dbo.Test
52(
53    c1 float NOT NULL,
54    c2 float NOT NULL,
55
56    CONSTRAINT [PK dbo.Test (c1)]
57    PRIMARY KEY CLUSTERED (c1)
58    ON PS (c1),
59
60    INDEX [IX dbo.Test (c2)]
61    NONCLUSTERED (c2)
62    ON PS (c1)
63)
64ON PS (c1);
65GO
66ALTER INDEX [IX dbo.Test (c2)]
67ON dbo.Test 
68DISABLE;
69GO
70INSERT dbo.Test WITH (TABLOCKX)
71(
72    c1,
73    c2
74)
75SELECT
76    CONVERT(float, SV1.number * 1000 + SV2.number),
77    CONVERT(float, (SV1.number * 1000 + SV2.number) * 2)
78FROM master.dbo.spt_values AS SV1
79CROSS JOIN master.dbo.spt_values AS SV2
80WHERE
81    SV1.[type] = N'P'
82    AND SV2.[type] = N'P'
83    AND SV1.number >= 0
84    AND SV1.number < 1000
85    AND SV2.number >= 0
86    AND SV2.number < 1000;
87GO
88ALTER INDEX [IX dbo.Test (c2)]
89ON dbo.Test
90REBUILD;
91	
92DROP TABLE IF EXISTS dbo.TestSwitchP2;
93GO
94-- Table with constraints limiting values to the target partition
95CREATE TABLE dbo.TestSwitchP2
96(
97    c1 float NOT NULL PRIMARY KEY,
98    c2 float NOT NULL,
99
100    CHECK (c1 >= 0 AND c1 < 1000000)
101)
102ON [PRIMARY];
103GO
104-- Switch existing rows into the working table
105ALTER TABLE dbo.Test
106SWITCH PARTITION 2
107TO dbo.TestSwitchP2;
108GO
109-- Add new rows
110INSERT dbo.TestSwitchP2 WITH (TABLOCKX)
111(
112    c1,
113    c2
114)
115SELECT
116    CONVERT(float, SV1.number * 1000 + SV2.number),
117    CONVERT(float, (SV1.number * 1000 + SV2.number) * 2)
118FROM master.dbo.spt_values AS SV1
119CROSS JOIN master.dbo.spt_values AS SV2
120WHERE
121    SV1.[type] = N'P'
122    AND SV2.[type] = N'P'
123    AND SV1.number >= 0
124    AND SV1.number < 1000
125    AND SV2.number >= 0
126    AND SV2.number < 1000;
127GO
128-- Create a compatible nonclustered index
129CREATE INDEX i ON dbo.TestSwitchP2 (c2);
130GO
131-- Switch the partition back in to the main table
132ALTER TABLE dbo.TestSwitchP2
133SWITCH TO dbo.Test 
134PARTITION 2;