主页 / dba / 问题 / 330905
Accepted
Akhilesh
Asked: 2023-09-04 18:47:29 +0800 CST

在 Azure SQL 数据库中使用相同分区号创建分区表上的分区

  • 772

我正在创建一个具有分区逻辑的表。下面是配分函数和方案：

DECLARE @StartDate datetime2 = '20230401';
DECLARE @YearlyQuartersPartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION YearlyQuartersPartitionFunction (datetime2) AS RANGE RIGHT FOR VALUES (';  

DECLARE @i datetime2 = @StartDate;  
WHILE @i < '20300101'  
BEGIN  
    SET @YearlyQuartersPartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';  
    SET @i = DATEADD(MM, 3, @i);  
END  
SET @YearlyQuartersPartitionFunction += '''' + CAST(@i as nvarchar(10))+ '''' + N');';  
--PRINT @YearlyQuartersPartitionFunction;
EXEC sp_executesql @YearlyQuartersPartitionFunction; 
GO

如果您运行 bloc 来创建分区函数并取消注释 PRINT 语句，您将看到以下内容：

CREATE PARTITION FUNCTION YearlyQuartersPartitionFunction (datetime2) AS RANGE RIGHT FOR VALUES ('2023-04-01', '2023-07-01', '2023-10-01', '2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01', '2025-01-01', '2025-04-01', '2025-07-01', '2025-10-01', '2026-01-01', '2026-04-01', '2026-07-01', '2026-10-01', '2027-01-01', '2027-04-01', '2027-07-01', '2027-10-01', '2028-01-01', '2028-04-01', '2028-07-01', '2028-10-01', '2029-01-01', '2029-04-01', '2029-07-01', '2029-10-01', '2030-01-01');

-- Partition scheme that maps to the PRIMARY filegroup
CREATE PARTITION SCHEME YearlyQuartersPartitionScheme
AS PARTITION YearlyQuartersPartitionFunction
ALL TO ([PRIMARY]);

我正在使用此方案使用以下 DDL 创建一个表：

CREATE TABLE [dbo].[MyTable](
    [Id] [uniqueidentifier] NOT NULL,
    [CompanyId] [uniqueidentifier] NOT NULL,
    [AddedUtc] [datetime2](7) NOT NULL,
    [IsTest] [bit] NOT NULL
) ON YearlyQuartersPartitionScheme(AddedUtc) ON PRIMARY;
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MyTable_CompanyId] ON [dbo].[MyTable]
(
    [CompanyId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD  DEFAULT (CONVERT([bit],(0))) FOR [IsTest]
GO
ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_Companies_CompanyId] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Companies] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_Companies_CompanyId]
GO

如果我检查为此表创建的分区，我会发现所有分区都具有相同的partition_number 值。这是错误的。

如果我做错了什么，有人可以指出吗？

我注意到分区方案的 data_space_id 与表上索引的 data_space_id 不匹配。

sql-server

1 个回答

  1. Best Answer

    索引都不是ON PRIMARY，ON YearlyQuartersPartitionScheme所以它们根本没有分区。您获取多行的原因sys.partitions是因为您有多个索引，但所有索引都只有一个分区。

    相反，您必须将 PK 重新创建为非聚集索引，并创建一个新的分区聚集索引。

    ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [PK_MyTable];

CREATE UNIQUE CLUSTERED INDEX [IX_MyTable_Partitioning] ON [dbo].[MyTable] (
  AddedUtc,
  Id
) ON YearlyQuartersPartitionScheme;

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
) ON [PRIMARY];
    • 1

相关问题