Last active
November 29, 2018 18:15
-
-
Save billinkc/49753a891722884b5b274afb449a0966 to your computer and use it in GitHub Desktop.
Microsoft SQL Server 2014/2016/2017 index repro
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
IF NOT EXISTS | |
( | |
SELECT * FROM sys.partition_functions AS PF WHERE PF.name = 'PartitionRange' | |
) | |
BEGIN | |
CREATE PARTITION FUNCTION PartitionRange(bigint) AS RANGE RIGHT FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9); | |
END | |
GO | |
IF NOT EXISTS | |
( | |
SELECT * FROM sys.partition_schemes AS PS WHERE PS.name = 'PartitionScheme' | |
) | |
BEGIN | |
CREATE PARTITION SCHEME PartitionScheme AS PARTITION PartitionRange ALL TO ([PRIMARY]); | |
END | |
GO | |
SET NOCOUNT ON; | |
IF EXISTS | |
( | |
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' | |
) | |
BEGIN | |
DROP TABLE dbo.gibberish; | |
END | |
CREATE TABLE dbo.[gibberish] | |
( | |
[RECORD_KEY] [numeric](30, 0) NOT NULL | |
, [OCCNO] [bigint] NOT NULL | |
-- Just needs to be some computed column | |
, [PartitionKey] AS OCCNO PERSISTED NOT NULL, | |
CONSTRAINT [dbo__gibberish__PK] PRIMARY KEY CLUSTERED | |
( | |
[RECORD_KEY] ASC | |
, [OCCNO] ASC | |
, [PartitionKey] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartitionScheme]([PartitionKey]) | |
) ON [PartitionScheme]([PartitionKey]); | |
GO | |
INSERT INTO dbo.[gibberish] | |
SELECT D.RECORD_KEY, 2 | |
FROM | |
( | |
SELECT TOP 98 | |
CAST(123456789012345678901234204069 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS numeric(30,0)) AS RECORD_KEY | |
FROM sys.all_columns AS AC | |
)D (RECORD_KEY); | |
-- Need at least one row that is not in the partition we are going to rebuild | |
INSERT INTO dbo.[gibberish] | |
VALUES (201803092054066280226937604065, 1); | |
-- Almost there.... | |
ALTER INDEX dbo__gibberish__PK | |
ON dbo.gibberish | |
REBUILD PARTITION = 2 | |
WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON); | |
-- Add one more row to make 100 rows | |
-- Matters not what partition it lands in | |
INSERT INTO dbo.[gibberish] | |
VALUES (201810020514071000210261504054, 2); | |
-- Boom goes the dynamite | |
-- 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: (123456789012345678901234204070, 2, 2). | |
ALTER INDEX dbo__gibberish__PK | |
ON dbo.gibberish | |
REBUILD PARTITION = 2 | |
WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON); | |
/* | |
-- Cleanup | |
DROP TABLE dbo.gibberish; | |
DROP PARTITION SCHEME [PartitionScheme]; | |
DROP PARTITION FUNCTION [PartitionRange]; | |
*/ |
Confirmed on
- SQL 2014 SP2 CU6
- SQL 2016 SP2 CU4
- SQL 2017 RTM CU9 GDR
- SQL 2019 CTP 2
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
What's interesting is that the rebuild fails once there are 100 rows in the table and at least two partitions have data in them. 2014-2019 CTP appear to be susceptible