在 SQL 中创建分区函数 [英] Create a Partition Function in SQL
问题描述
我创建了一个分区函数,但我无法将它应用到表中.我不确定我哪里出错了.
I have created a partition function but I am not able to apply it to a table. I am not sure where I am going wrong.
这是我的分区函数:
CREATE PARTITION FUNCTION StaticDateMonthPartition (int)
AS RANGE left
FOR VALUES (
20120301,
20120401,
20120501,
20120601,
20120701,
20120801,
20120901,
20121001,
20121101,
20121201,
20130101,
20130201
)
正在尝试申请此表:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[partition_OLAP_Fact_vvv]') AND type in (N'U'))
DROP TABLE [dbo].[partition_OLAP_Fact_vvv]
GO
CREATE TABLE [dbo].[partition_OLAP_Fact_vvv]
(
FFFFactvvvId bigint,
CORStaticDateId int,
CORVersionvvvId bigint,
vvvCount tinyint,
UPB decimal(18, 2)
) ON CORStaticDateMonthPartition ([CORStaticDateId])
但是当我尝试执行表脚本时出现此错误:
But when I try to execute the table script I get this error:
Invalid partition scheme 'CORStaticDateMonthPartition' specified
请帮忙.
用步骤重新发布我的代码
Reposting my code with steps
Pinal 的 教程很棒!这是一个快速总结
Pinal's tutoral is great! Here's a quick summary
为每个分区添加文件组
Add file groups for each of your partitions
Alter Database [database] Add FileGroup partition_201207
创建分区函数
Create Partition Function
CREATE PARTITION FUNCTION Partition_Range_CORStaticMonth(int)
AS RANGE left
FOR VALUES (20120301)
创建分区方案
Create Partition Scheme
CREATE PARTITION SCHEME Partition_Scheme_CORStaticMonth
AS PARTITION Partition_Range_CORStaticMonth
TO (FFF_Fact_vvv_201203)
将文件添加到文件组
Add Files to the File Groups
ALTER DATABASE [database]
ADD FILE(
NAME = N'FFF_Fact_vvv_201203',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FFF_Fact_vvv_201203.ndf' ,
SIZE = 2048KB ,
FILEGROWTH = 1024KB
)
TO FILEGROUP [FFF_Fact_vvv_201203]
使用分区方案构建表
Build Table with Partition Scheme applied
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[partition_Table]') AND type in (N'U'))
DROP TABLE [dbo].[partition_Table]
GO
CREATE TABLE [dbo].[partition_Table]
(
CORStaticDateId int
) ON Partition_Scheme_CORStaticMonth ([CORStaticDateId])
推荐答案
你需要一个分区方案来应用到一个表.
you need a partition scheme to apply to a table.
顺序是:
1) 创建你的文件组
2) 创建你的分区函数
2) Create your partition Function
3) 将分区方案附加到文件组(使用分区功能)
3) Attach Partition Scheme to FileGroups (using the partition Function)
4) 在分区方案上创建表
4) Create table on partition Scheme
检查这个链接 教程
这篇关于在 SQL 中创建分区函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!