· 7 years ago · Nov 29, 2018, 02:26 AM
1/*
2CREATE PARTITION FUNCTION [PartitionRange](bigint) AS RANGE RIGHT FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
3CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionRange] ALL TO ([PRIMARY]);
4*/
5
6GO
7SET NOCOUNT ON;
8
9IF EXISTS
10(
11 SELECT * FROM sys.schemas AS S INNER JOIN sys.tables AS T ON T.schema_id = S.schema_id WHERE S.name = 'dbo' AND T.name = 'gibberish'
12)
13BEGIN
14 DROP TABLE dbo.gibberish;
15END
16
17CREATE TABLE dbo.[gibberish]
18(
19 [RECORD_KEY] [numeric](30, 0) NOT NULL,
20 [OCCNO] [bigint] NOT NULL,
21 -- Just needs to be some computed column
22 [PartitionKey] AS OCCNO PERSISTED NOT NULL,
23 CONSTRAINT [dbo__gibberish__PK] PRIMARY KEY CLUSTERED
24(
25 [RECORD_KEY] ASC,
26 [OCCNO] ASC,
27 [PartitionKey] ASC
28)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartitionScheme]([PartitionKey])
29) ON [PartitionScheme]([PartitionKey])
30GO
31
32INSERT INTO dbo.[gibberish]
33SELECT D.RECORD_KEY, D.OCCNO
34FROM
35(
36VALUES
37( 123456789012345678901234204073, 1 ),
38( 123456789012345678901234204073, 2 ),
39( 123456789012345678901234204073, 3 ),
40( 123456789012345678901234204073, 4 ),
41( 123456789012345678901234204073, 5 ),
42( 123456789012345678901234204073, 6 ),
43( 123456789012345678901234204073, 7 ),
44( 123456789012345678901234204073, 8 ),
45( 123456789012345678901234204038, 9 ),
46( 123456789012345678901234204038, 10 ),
47( 123456789012345678901234204038, 11 ),
48( 123456789012345678901234204038, 12 ),
49( 123456789012345678901234204038, 13 ),
50( 123456789012345678901234204038, 14 ),
51( 123456789012345678901234204038, 15 ),
52( 123456789012345678901234204038, 16 ),
53( 123456789012345678901234204038, 17 ),
54( 123456789012345678901234204038, 18 ),
55( 123456789012345678901234204077, 19 ),
56( 123456789012345678901234204077, 20 ),
57( 123456789012345678901234204077, 21 ),
58( 123456789012345678901234204077, 22 ),
59( 123456789012345678901234204077, 23 ),
60( 123456789012345678901234204077, 24 ),
61( 123456789012345678901234204077, 25 ),
62( 123456789012345678901234204077, 26 ),
63( 123456789012345678901234204078, 27 ),
64( 123456789012345678901234204078, 28 ),
65( 123456789012345678901234204078, 29 ),
66( 123456789012345678901234204078, 30 ),
67( 123456789012345678901234204078, 31 ),
68( 123456789012345678901234204078, 32 ),
69( 123456789012345678901234204078, 33 ),
70( 123456789012345678901234204078, 34 ),
71( 123456789012345678901234204078, 35 ),
72( 123456789012345678901234204078, 36 ),
73( 123456789012345678901234204078, 37 ),
74( 123456789012345678901234204078, 38 ),
75( 123456789012345678901234204078, 39 ),
76( 123456789012345678901234204078, 40 ),
77( 123456789012345678901234204078, 41 ),
78( 123456789012345678901234204078, 42 ),
79( 123456789012345678901234204078, 43 ),
80( 123456789012345678901234204078, 44 ),
81( 123456789012345678901234204078, 45 ),
82( 123456789012345678901234204078, 46 ),
83( 123456789012345678901234204078, 47 ),
84( 123456789012345678901234204078, 48 ),
85( 123456789012345678901234204078, 49 ),
86( 123456789012345678901234204094, 50 ),
87( 123456789012345678901234204094, 51 ),
88( 123456789012345678901234204094, 52 ),
89( 123456789012345678901234204094, 53 ),
90( 123456789012345678901234204094, 54 ),
91( 123456789012345678901234204094, 55 ),
92( 123456789012345678901234204094, 56 ),
93( 123456789012345678901234204075, 57 ),
94( 123456789012345678901234204075, 58 ),
95( 123456789012345678901234204075, 59 ),
96( 123456789012345678901234204075, 60 ),
97( 123456789012345678901234204075, 61 ),
98( 123456789012345678901234204075, 62 ),
99( 123456789012345678901234204075, 63 ),
100( 123456789012345678901234204075, 64 ),
101( 123456789012345678901234204075, 65 ),
102( 123456789012345678901234204075, 66 ),
103( 123456789012345678901234204075, 67 ),
104( 123456789012345678901234204075, 68 ),
105( 123456789012345678901234204075, 69 ),
106( 123456789012345678901234204075, 70 ),
107( 123456789012345678901234204075, 71 ),
108( 123456789012345678901234204075, 72 ),
109( 123456789012345678901234204075, 73 ),
110( 123456789012345678901234204075, 74 ),
111( 123456789012345678901234204075, 75 ),
112( 123456789012345678901234204075, 76 ),
113( 123456789012345678901234204075, 77 ),
114( 123456789012345678901234204075, 78 ),
115( 123456789012345678901234204075, 79 ),
116( 123456789012345678901234204075, 80 ),
117( 123456789012345678901234204075, 81 ),
118( 123456789012345678901234204075, 82 ),
119( 123456789012345678901234204075, 83 ),
120( 123456789012345678901234204075, 84 ),
121( 123456789012345678901234204075, 85 ),
122( 123456789012345678901234204075, 86 ),
123( 123456789012345678901234204061, 87 ),
124( 123456789012345678901234204061, 88 ),
125( 123456789012345678901234204061, 89 ),
126( 123456789012345678901234204061, 90 ),
127( 123456789012345678901234204061, 91 ),
128( 123456789012345678901234204061, 92 )
129)D (RECORD_KEY, OCCNO);
130
131
132INSERT INTO dbo.[gibberish]
133SELECT D.RECORD_KEY, D.OCCNO
134FROM
135(
136VALUES
137
138( 201810020514071000210261504054, 53 ),
139( 201803092054066280226937604065, 1 ),
140( 201803092054066280226937604065, 2 ),
141( 201803092054066280226937604065, 3 ),
142( 201803092054066280226937604065, 4 ),
143( 201803092054066280226937604065, 5 ),
144-- Uncomment this line to have exactly 100 rows in the table
145-- and uncover the index error
146( 201803092054066280226937604065, 6 ),
147( 201803092054066280226937604065, 7 )
148)D (RECORD_KEY, OCCNO);
149
150-- Boom goes the dynamite
151-- Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violates a uniqueness constraint imposed by the target table. Sort order incorrect for the following two rows: primary key of first row: (201803092054066280226937604065, 1, 1), primary key of second row: (123456789012345678901234204073, 2, 2).
152ALTER INDEX dbo__gibberish__PK
153 ON dbo.gibberish
154 REBUILD PARTITION = 2
155 WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 4);