在 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

1. 为每个分区添加文件组

1. 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屋！