ALTER TABLE SWITCH语句失败。表X中分区1定义的范围不是表Y中分区2定义的范围的子集

Question

我正在尝试在Azure SQL DataWarehouse中创建一个简单的分区交换例程，它基于https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-best-practices-transactions中的“使用分区切换进行优化”一节

我认为我有我试图交换对齐的分区，但我得到一个错误，似乎告诉我，他们没有(ALTER TABLE SWITCH语句失败。表'Distribution_55.dbo.Table_62915da3af53441980fedba6da729c62_55')中分区1定义的范围不是分区2定义的范围的子集

这是我的完整复制品：

--Create a view for us to use to look up the partition numbers later
CREATE VIEW dbo.TablePartitions
AS
    SELECT
        s.name SchemaName
        ,t.name TableName
        ,CAST(r.value as nvarchar(128)) BoundaryValue
        ,p.partition_number PartitionNumber
    FROM        
        sys.schemas s
        JOIN sys.tables t
            ON s.[schema_id] = t.[schema_id]
        JOIN sys.indexes i
            ON t.[object_id] = i.[object_id]
        JOIN sys.partitions p 
            ON i.[object_id] = p.[object_id] 
            AND i.[index_id] = p.[index_id] 
        JOIN sys.partition_schemes h 
            ON i.[data_space_id] = h.[data_space_id]
        JOIN sys.partition_functions f
            ON h.[function_id] = f.[function_id]
        LEFT JOIN sys.partition_range_values r
            ON f.[function_id] = r.[function_id] 
            AND r.[boundary_id] = p.[partition_number]
    WHERE 
        i.[index_id] <= 1;

--Create our main partitioned table
CREATE TABLE [dbo].[PartitionedTable](
    [DistributionField] [nvarchar](30) NOT NULL,
    [PartitionField] [int] NOT NULL,
    [Value] [int] NOT NULL
)
WITH (
    DISTRIBUTION = HASH( [DistributionField] ),
    PARTITION ( [PartitionField] RANGE RIGHT FOR VALUES() ),
    CLUSTERED COLUMNSTORE INDEX
)

--Create the main table's partition boundaries
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (1)
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (2)
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (3)

--Create a staging table for partition swapping
CREATE TABLE [dbo].[PartitionedTableStaging]
WITH
(    
    DISTRIBUTION = HASH( [DistributionField] ),
    PARTITION ( [PartitionField] RANGE RIGHT FOR VALUES() ),
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM    [dbo].[PartitionedTable]
WHERE 1=2

--Create boundaries that will align the partition that PartitionValue = 2 will fall into
ALTER TABLE dbo.[PartitionedTableStaging] SPLIT RANGE (2)
ALTER TABLE dbo.[PartitionedTableStaging] SPLIT RANGE (3)

--Load the staging table with values where PartitionValue = 2
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('X', 2, 1)
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('Y', 2, 2)
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('Z', 2, 3)

--Find the partition numbers that we will swap
select * from TablePartitions where SchemaName = 'dbo' and TableName = 'PartitionedTable' and BoundaryValue = 2
select * from TablePartitions where SchemaName = 'dbo' and TableName = 'PartitionedTableStaging' and BoundaryValue = 2

--Swap the staged partition over to the main table
ALTER TABLE PartitionedTableStaging SWITCH PARTITION 1 TO PartitionedTable PARTITION 2;

保持PartitionField =2的分区的边界是否对齐？

Answer 1

原来我误解了RANGE RIGHT和RANGE LEFT的工作原理。例如，RANGE RIGHT将值(2是repro关注的值)放入分区3，而不是分区2。如果将repro更改为使用RANGE LEFT，并在临时表上创建分区2的下限(通过创建值1的边界)，则临时表和活动表上的分区2将对齐，并且交换工作。以下是更正后的示例：

--Create a view for us to use to look up the partition numbers later
CREATE VIEW dbo.TablePartitions
AS
    SELECT
        s.name SchemaName
        ,t.name TableName
        ,CAST(r.value as nvarchar(128)) BoundaryValue
        ,p.partition_number PartitionNumber
    FROM        
        sys.schemas s
        JOIN sys.tables t
            ON s.[schema_id] = t.[schema_id]
        JOIN sys.indexes i
            ON t.[object_id] = i.[object_id]
        JOIN sys.partitions p 
            ON i.[object_id] = p.[object_id] 
            AND i.[index_id] = p.[index_id] 
        JOIN sys.partition_schemes h 
            ON i.[data_space_id] = h.[data_space_id]
        JOIN sys.partition_functions f
            ON h.[function_id] = f.[function_id]
        LEFT JOIN sys.partition_range_values r
            ON f.[function_id] = r.[function_id] 
            AND r.[boundary_id] = p.[partition_number]
    WHERE 
        i.[index_id] <= 1;

--Create our main partitioned table
CREATE TABLE [dbo].[PartitionedTable](
    [DistributionField] [nvarchar](30) NOT NULL,
    [PartitionField] [int] NOT NULL,
    [Value] [int] NOT NULL
)
WITH (
    DISTRIBUTION = HASH( [DistributionField] ),
    PARTITION ( [PartitionField] RANGE LEFT FOR VALUES() ),
    CLUSTERED COLUMNSTORE INDEX
)

--Create the main table's partition boundaries
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (1)
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (2)
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (3)

--Create a staging table for partition swapping
CREATE TABLE [dbo].[PartitionedTableStaging]
WITH
(    
    DISTRIBUTION = HASH( [DistributionField] ),
    PARTITION ( [PartitionField] RANGE LEFT FOR VALUES() ),
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM    [dbo].[PartitionedTable]
WHERE 1=2

--Create boundaries that will align the partition that PartitionValue = 2 will fall into
ALTER TABLE dbo.[PartitionedTableStaging] SPLIT RANGE (1)
ALTER TABLE dbo.[PartitionedTableStaging] SPLIT RANGE (2)

--Load the staging table with values where PartitionValue = 2
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('X', 2, 1)
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('Y', 2, 2)
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('Z', 2, 3)

--Find the partition numbers that we will swap
select * from TablePartitions where SchemaName = 'dbo' and TableName = 'PartitionedTable' and BoundaryValue = 2
select * from TablePartitions where SchemaName = 'dbo' and TableName = 'PartitionedTableStaging' and BoundaryValue = 2

--Swap the staged partition over to the main table
ALTER TABLE PartitionedTableStaging SWITCH PARTITION 2 TO PartitionedTable PARTITION 2;