通过外键列在sql server上进行表分区

All examples of table partitioning that I have found are quite simple but I need to partition many tables by one criteria.

我找到的表分区的所有示例都非常简单,但我需要按一个条件对许多表进行分区。

For example I have tables: Contractors and Products where ContractorId in Products table is a foreign key.

例如,我有表:Contractors和Products,其中ContractorId in Products表是外键。

I created function and schema for ContractorId. It works perfectly for Contractors table but when it comes to the Products table...

我为ContractorId创建了函数和模式。它适用于Contractors表,但是当它涉及Products表时......

I have no idea how should I use it because when I try I always got the information: "The filegroup 'PRIMARY' specified for the clustered index 'PK_dbo.Products' was used for table 'dbo.Products' even though partition scheme 'scheme_Contractors' is specified for it". My Products table looks like:

我不知道应该如何使用它,因为当我尝试时总是得到信息:“为聚集索引'PK_dbo.Products'指定的文件组'PRIMARY'用于表'dbo.Products',即使分区方案'scheme_Contractors '是为它指定的“。我的产品表看起来像:

CREATE TABLE [dbo].[Products](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[Amount] [int] NULL,
[Color] [nvarchar](max) NULL,
[Price] [decimal](18, 2) NULL,
[Guarantee] [nvarchar](max) NULL,
[GuaranteeType] [int] NULL,
[AdditionalFeatures] [nvarchar](max) NULL,
[Valid] [bit] NULL,
[ContractorId] [int] NOT NULL,
[ProducerId] [int] NOT NULL,
[ProductCategoryId] [int] NOT NULL,
 CONSTRAINT [PK_dbo.Products] PRIMARY KEY ( [ProductId] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )
GO
ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Products_dbo.Contractors_ContractorId] FOREIGN KEY([ContractorId]) 
REFERENCES [dbo].[Contractors] ([ContractorId])
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_dbo.Products_dbo.Contractors_ContractorId]
GO

Could anymone tell me please - is it possible to use my schema on ContractorId column and how? Thank you in advance!

请问任何人告诉我 - 是否可以在ContractorId列上使用我的架构以及如何?先感谢您!

1 个解决方案

#1

In agreement with Dan Guzman, I'd like to point out there should be no [PRIMARY] specification in the table definition.

与Dan Guzman达成一致,我想指出表定义中不应该有[PRIMARY]规范。

We use partitioning on large scale. It is very comfortable to partition all tables on the same partitioning scheme, because the SQL engine will use its multi-processor paralellisation capabilities to the full.

我们大规模使用分区。在同一分区方案上对所有表进行分区非常方便,因为SQL引擎将充分利用其多处理器并行化功能。

When a certain group of partitions is in one database file and another paration in another file you can even become flexible with disc-usage and backups.

当某个分区组在一个数据库文件中而另一个分区在另一个文件中时,您甚至可以通过光盘使用和备份变得灵活。

So you first need a partition function to define the values of the partitioning scheme:

所以你首先需要一个分区函数来定义分区方案的值:

CREATE PARTITION FUNCTION [ContractorPartitionFunction](int) AS RANGE LEFT
FOR VALUES (contractor1,contractor2,...)

Then you need to create the partition scheme

然后,您需要创建分区方案

CREATE PARTITION SCHEME [ContractorPartitionScheme] 
AS PARTITION [ContractorPartitionFunction] 
TO ([File_001],[File_002],...,[PRIMARY])

Then for all tables and indexes you now create you should remove ON [PRIMARY] from the definitions as the target filegroup, but instead you should use ON [ContractorPartitionScheme](ContractorId)

然后对于您现在创建的所有表和索引,您应该从定义中删除ON [PRIMARY]作为目标文件组,而是应该使用ON [ContractorPartitionScheme](ContractorId)

So you table definition should now read:

所以你现在应该读取表定义:

CREATE TABLE [dbo].[Products](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[Amount] [int] NULL,
[Color] [nvarchar](max) NULL,
[Price] [decimal](18, 2) NULL,
[Guarantee] [nvarchar](max) NULL,
[GuaranteeType] [int] NULL,
[AdditionalFeatures] [nvarchar](max) NULL,
[Valid] [bit] NULL,
[ContractorId] [int] NOT NULL,
[ProducerId] [int] NOT NULL,
[ProductCategoryId] [int] NOT NULL)
ON ContractorPartitionScheme(ContractorId)

CREATE UNIQUE NONCLUSTERED INDEX PK_dbo.Products ON Products
    (
    productId,
    ConstructorId
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
 ON ContractorPartitionScheme(ContractorId)

#1