Microsoft SQL Server 2014/2016/2017 index repro

BoomGoesTheDynamite.sql

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