分区索引一实例

-- 创建文件组ALTER DATABASE [DeanDB] ADD FILEGROUP [FG1]ALTER DATABASE [DeanDB] ADD FILE ( NAME = N'FG1', FILENAME = N'C:\DeanData\FG1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG1] --2 ALTER DATABASE [DeanDB] ADD FILEGROUP [FG2]ALTER DATABASE [DeanDB] ADD FILE ( NAME = N'FG2', FILENAME = N'D:\DeanData\FG2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG2] --3 ALTER DATABASE [DeanDB] ADD FILEGROUP [FG3]ALTER DATABASE [DeanDB] ADD FILE ( NAME = N'FG3', FILENAME = N'E:\DeanData\FG3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG3] --4 ALTER DATABASE [DeanDB] ADD FILEGROUP [FG4]ALTER DATABASE [DeanDB] ADD FILE ( NAME = N'FG4', FILENAME = N'F:\DeanData\FG4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG4] --创建分区函数[SendSMSPF]CREATE PARTITION FUNCTION [SendSMSPF](datetime) AS RANGE RIGHT FOR VALUES ('20120401','20120701','20121001')--创建分区方案[SendSMSPS]CREATE PARTITION SCHEME [SendSMSPS] AS PARTITION [SendSMSPF] TO ([FG1], [FG2], [FG3], [FG4])--创建分区表SendSMSLogCREATE TABLE SendSMSLog([ID] [int] IDENTITY(1,1) NOT NULL,[IDNum] [nvarchar](50) NULL,[SendContent] [text] NULL,[SendDate] [datetime] NOT NULL) ON SendSMSPS(SendDate)-- 创建聚集分区索引create clustered index IXC_SendSMS_OrderDate on dbo.SendSMSLog(SendDate)go-- 为分区表设置主键alter table dbo.SendSMSLog add constraint PK_SendSMS primary key (ID, SendDate)go-- 查看分区表信息SELECT $partition.SendSMSPF(o.SendDate)                      AS [Partition Number]       , min(o.SendDate) AS [Min SendDate]       , max(o.SendDate) AS [Max SendDate]       , count(*) AS [Rows In Partition]FROM dbo.SendSMSLog AS oGROUP BY $partition.SendSMSPF(o.SendDate)ORDER BY [Partition Number]