ALTER TABLE SWITCH demo for errors 4907 and 4908

ALTER TABLE SWITCH demo errors 4907 and 4908.sql

/*
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16#switch--partition-source_partition_number_expression--to--schema_name--target_table--partition-target_partition_number_expression-
*/
SET NOCOUNT ON;
GO
CREATE PARTITION FUNCTION PF1 (int) AS RANGE RIGHT FOR VALUES (0, 100)
CREATE PARTITION SCHEME PS1 AS PARTITION PF1 ALL TO ([PRIMARY]);
GO
CREATE PARTITION FUNCTION PF2 (int) AS RANGE RIGHT FOR VALUES (0, 100, 200)
CREATE PARTITION SCHEME PS2 AS PARTITION PF2 ALL TO ([PRIMARY]);
GO
CREATE PARTITION FUNCTION PF3 (int) AS RANGE RIGHT FOR VALUES (0, 200)
CREATE PARTITION SCHEME PS3 AS PARTITION PF3 ALL TO ([PRIMARY]);
GO

-- PrtTable1: The source table
CREATE TABLE dbo.PrtTable1
(
	prtCol int NOT NULL CONSTRAINT PK_PrtTable1 PRIMARY KEY CLUSTERED ON PS1(prtCol),
	otherCol int NOT NULL
) ON PS1(prtCol)

CREATE NONCLUSTERED INDEX IX1 ON dbo.PrtTable1 (otherCol) ON PS2(prtCol)
CREATE NONCLUSTERED INDEX IX1_3 ON dbo.PrtTable1 (otherCol) ON PS3(prtCol)

GO

-- PrtTable2: Target table partitioned and indexed identically to source table PrtTable1
CREATE TABLE dbo.PrtTable2
(
	prtCol int NOT NULL CONSTRAINT PK_PrtTable2 PRIMARY KEY CLUSTERED ON PS1(prtCol),
	otherCol int NOT NULL
) ON PS1(prtCol)

CREATE NONCLUSTERED INDEX IX2 ON dbo.PrtTable2 (otherCol) ON PS2(prtCol)
CREATE NONCLUSTERED INDEX IX2_3 ON dbo.PrtTable2 (otherCol) ON PS3(prtCol)

GO

-- PrtTable3: Target table without any nonclustered indexes, partition range not identical but contains the range from source table
CREATE TABLE dbo.PrtTable3
(
	prtCol int NOT NULL CONSTRAINT PK_PrtTable3 PRIMARY KEY CLUSTERED ON PS3(prtCol),
	otherCol int NOT NULL
) ON PS3(prtCol)

GO
-- Insert just one row into source table, goes into partition number 2
INSERT INTO dbo.PrtTable1 (prtCol, otherCol) VALUES(50, 30)
GO
SELECT OBJECT_NAME(object_id), index_id, partition_number, rows
FROM sys.partitions
WHERE object_id IN (OBJECT_ID('PrtTable1'),OBJECT_ID('PrtTable2'),OBJECT_ID('PrtTable3'))
GO
RAISERROR(N'ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2',0,1) WITH NOWAIT;
ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2 -- raises error 4907
GO
RAISERROR(N'ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2',0,1) WITH NOWAIT;
ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2 -- also raises error 4907
GO
PRINT N'Moving IX1 to same PS as the table PrtTable1'
CREATE NONCLUSTERED INDEX IX1 ON dbo.PrtTable1 (otherCol) WITH(DROP_EXISTING = ON) ON PS1(prtCol)
GO
RAISERROR(N'ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2',0,1) WITH NOWAIT;
ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2 -- raises error 4908 due to different range boundaries
GO
PRINT N'Moving IX1_3 to same PS as the table PrtTable1'
CREATE NONCLUSTERED INDEX IX1_3 ON dbo.PrtTable1 (otherCol) WITH(DROP_EXISTING = ON) ON PS1(prtCol)
GO
RAISERROR(N'ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2',0,1) WITH NOWAIT;
ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2 -- raises error 4907, but this time on the target table
GO
RAISERROR(N'ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2',0,1) WITH NOWAIT;
ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2 -- without nonclustered index on target: works (even though range boundaries are different, they're still matching)
PRINT N'Success'
GO
SELECT OBJECT_NAME(object_id), index_id, partition_number, rows
FROM sys.partitions
WHERE object_id IN (OBJECT_ID('PrtTable1'),OBJECT_ID('PrtTable2'),OBJECT_ID('PrtTable3'))
GO
-- Cleanup:
DROP TABLE IF EXISTS dbo.PrtTable1
DROP TABLE IF EXISTS dbo.PrtTable2
DROP TABLE IF EXISTS dbo.PrtTable3
DROP PARTITION SCHEME PS1
DROP PARTITION FUNCTION PF1
DROP PARTITION SCHEME PS2
DROP PARTITION FUNCTION PF2
DROP PARTITION SCHEME PS3
DROP PARTITION FUNCTION PF3
GO