主页 / user-210615

dave111's questions

Martin Hope
dave111
Asked: 2024-02-29 05:32:39 +0800 CST
  • 6

我在 Azure SQL 上，向一些历史存档表添加每月分区（使用分区方案/函数创建第二个表，并将原始表中的行转储到其中）。我使用查询来查看所有分区号和日期范围，并且在将数据插入新表后能够看到所有内容，但是在添加 PK 约束后，我的查询停止返回行，或者如果我注释掉联接在 sys.index 表和下面我只看到一个分区。我不太确定需要对下面的查询进行哪些修改。

数据定义语言：

CREATE PARTITION FUNCTION [PF_YearMonthBiWeekly] (datetime)
AS RANGE RIGHT FOR VALUES 
(
    '2024-01-01', '2024-01-16', '2024-02-01', '2024-02-16', '2024-03-01', '2024-03-16', '2024-04-01', '2024-04-16', '2024-05-01', '2024-05-16', '2024-06-01', '2024-06-16', '2024-07-01', '2024-07-16', '2024-08-01', '2024-08-16', '2024-09-01', '2024-09-16', '2024-10-01', '2024-10-16', '2024-11-01', '2024-11-16', '2024-12-01','2024-12-16',
    '2025-01-01'
);
GO

CREATE PARTITION SCHEME PS_YearMonthBiWeekly
AS PARTITION [PF_YearMonthBiWeekly]
ALL TO ([PRIMARY])
GO

CREATE TABLE [dbo].[Table1]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Test_ID] [int] NULL,
    [Metric_ID] [int] NULL,
    [Metric_Desc] [nvarchar](300) NULL  
    [Result_On] [datetime] NULL,
) ON [PS_YearMonthBiWeekly] ([Result_On]);

插入数据，查询工作，然后添加PK：

ALTER TABLE [dbo].[Table1] 
ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [ID] ASC )
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

分区视图查询：

SELECT
    OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName
    ,OBJECT_NAME(pstats.object_id) AS TableName
    ,pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
    ,c.name AS PartitionKey
    ,CASE 
        WHEN pf.boundary_value_on_right = 0 
        THEN c.name + ' > ' + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) 
        ELSE c.name + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100))  + ' and ' + c.name + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100))
    END AS PartitionRange
    ,pf.name AS PartitionFunctionName
    ,ps.name AS PartitionSchemeName
    ,ds.name AS PartitionFilegroupName
    ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Range Left' ELSE 'Range Right' END AS PartitionFunctionRange
    ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Upper Boundary' ELSE 'Lower Boundary' END AS PartitionBoundary
    ,prv.value AS PartitionBoundaryValue
    ,p.data_compression_desc AS DataCompression
    ,case 
        when i.[type] = 0 then 'Nonclustered Heap Index'
        when i.[type] = 1 then 'Clustered Index'
        when i.[type] = 2 then 'Nonclustered Unique Index'
        when i.[type] = 3 then 'XML Index'
        when i.[type] = 4 then 'Spatial Index'
        when i.[type] = 5 then 'Clustered Columnstore Index'
        when i.[type] = 6 then 'Nonclustered Columnstore Index'
        when i.[type] = 7 then 'Nonclustered Hash Index'
        else NULL
    end as index_type
FROM sys.dm_db_partition_stats AS pstats
INNER JOIN sys.partitions AS p 
    ON pstats.partition_id = p.partition_id
INNER JOIN sys.destination_data_spaces AS dds 
    ON pstats.partition_number = dds.destination_id
INNER JOIN sys.data_spaces AS ds 
    ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps 
    ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf 
    ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.indexes AS i 
    ON pstats.object_id = i.object_id 
    AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id 
    --AND i.type <= 1 /* Heap or Clustered Index */
INNER JOIN sys.index_columns AS ic 
    ON i.index_id = ic.index_id 
    AND i.object_id = ic.object_id 
    AND ic.partition_ordinal > 0
INNER JOIN sys.columns AS c 
    ON pstats.object_id = c.object_id 
    AND ic.column_id = c.column_id
LEFT JOIN sys.partition_range_values AS prv 
    ON pf.function_id = prv.function_id 
    AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END)
--WHERE pstats.object_id = OBJECT_ID('Table1')
--ORDER BY TableName, PartitionNumber;
GO
index