Sqlserver大数据量分区表创建

/* 逆向删除对象
DROP PARTITION SCHEME [PS_BasicPolicy2014];
DROP PARTITION FUNCTION [PF_BasicPolicy2014];

ALTER DATABASE [JKGateway] REMOVE FILE FG_BasicPolicy2014_ELSE;
ALTER DATABASE [JKGateway] REMOVE FILE FG_BasicPolicy2014_Piaomeng;
ALTER DATABASE [JKGateway] REMOVE FILE FG_BasicPolicy2014_Jinri;
ALTER DATABASE [JKGateway] REMOVE FILE FG_BasicPolicy2014_51Book;
ALTER DATABASE [JKGateway] REMOVE FILE FG_BasicPolicy2014_19E;

ALTER DATABASE [JKGateway] REMOVE FILEGROUP [FG_BasicPolicy2014_ELSE];
ALTER DATABASE [JKGateway] REMOVE FILEGROUP [FG_BasicPolicy2014_Piaomeng];
ALTER DATABASE [JKGateway] REMOVE FILEGROUP [FG_BasicPolicy2014_Jinri];
ALTER DATABASE [JKGateway] REMOVE FILEGROUP [FG_BasicPolicy2014_51Book];
ALTER DATABASE [JKGateway] REMOVE FILEGROUP [FG_BasicPolicy2014_19E];
*/


-- 创建文件组
ALTER DATABASE [JKGateway] ADD FILEGROUP [FG_BasicPolicy2014_ELSE];
ALTER DATABASE [JKGateway] ADD FILEGROUP [FG_BasicPolicy2014_Piaomeng];
ALTER DATABASE [JKGateway] ADD FILEGROUP [FG_BasicPolicy2014_Jinri];
ALTER DATABASE [JKGateway] ADD FILEGROUP [FG_BasicPolicy2014_51Book];
ALTER DATABASE [JKGateway] ADD FILEGROUP [FG_BasicPolicy2014_19E];

-- 创建文件
ALTER DATABASE [JKGateway] ADD FILE ( NAME = N'FG_BasicPolicy2014_ELSE', FILENAME = N'E:\Sqlserver_FILEGROUP\FG_BasicPolicy2014_ELSE.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG_BasicPolicy2014_ELSE];
ALTER DATABASE [JKGateway] ADD FILE ( NAME = N'FG_BasicPolicy2014_Piaomeng', FILENAME = N'F:\Sqlserver_FILEGROUP\FG_BasicPolicy2014_Piaomeng.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG_BasicPolicy2014_Piaomeng];
ALTER DATABASE [JKGateway] ADD FILE ( NAME = N'FG_BasicPolicy2014_Jinri', FILENAME = N'E:\Sqlserver_FILEGROUP\FG_BasicPolicy2014_Jinri.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG_BasicPolicy2014_Jinri];
ALTER DATABASE [JKGateway] ADD FILE ( NAME = N'FG_BasicPolicy2014_51Book', FILENAME = N'D:\Sqlserver_FILEGROUP\FG_BasicPolicy2014_51Book.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG_BasicPolicy2014_51Book];
ALTER DATABASE [JKGateway] ADD FILE ( NAME = N'FG_BasicPolicy2014_19E', FILENAME = N'D:\Sqlserver_FILEGROUP\FG_BasicPolicy2014_19E.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG_BasicPolicy2014_19E];

-- 创建分区函数
CREATE PARTITION FUNCTION [PF_BasicPolicy2014](INT) AS RANGE RIGHT FOR VALUES ('1', '2', '3', '6');

-- 创建分区方案(注意顺序：第一个为Other，共它的文件组对应上面的1,2,3,6，如：FG_BasicPolicy2014_Piaomeng对应1，FG_BasicPolicy2014_Jinri对应2，以此类推)
CREATE PARTITION SCHEME [PS_BasicPolicy2014] AS PARTITION [PF_BasicPolicy2014] TO ([FG_BasicPolicy2014_ELSE], [FG_BasicPolicy2014_Piaomeng], [FG_BasicPolicy2014_Jinri], [FG_BasicPolicy2014_51Book], [FG_BasicPolicy2014_19E]);

CREATE TABLE [Policy].[BasicPolicy2014](
    [policyid] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [ID] [varchar](300) NOT NULL,
    [companyID] [int] NOT NULL,
    ...
 CONSTRAINT [PK_BasicPolicy2014] PRIMARY KEY CLUSTERED 
(
    [policyid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PS_BasicPolicy2014](companyID) 

 

参考资料：http://www.cnblogs.com/robinli/archive/2011/03/02/1969314.html