SQLSERVER对数据表进行分区

数据表非常大的时候，有时就需要考虑进行分区了，提高查询效率

创建文件组

ALTER DATABASE EWYBH ADD FILEGROUP TEST1
ALTER DATABASE EWYBH ADD FILEGROUP TEST2
ALTER DATABASE EWYBH ADD FILEGROUP TEST3
ALTER DATABASE EWYBH ADD FILEGROUP TEST4

右键数据库“属性”——“文件组”

创建数据文件到文件组

ALTER DATABASE EWYBH ADD FILE(name=N'ByDate1',filename=N'D:\SiSS\DATA\ByDate1.ndf',size=5Mb,filegrowth=5mb) TO FILEGROUP TEST1
ALTER DATABASE EWYBH ADD FILE(name=N'ByDate2',filename=N'D:\SiSS\DATA\ByDate2.ndf',size=5Mb,filegrowth=5mb) TO FILEGROUP TEST2
ALTER DATABASE EWYBH ADD FILE(name=N'ByDate3',filename=N'D:\SiSS\DATA\ByDate3.ndf',size=5Mb,filegrowth=5mb) TO FILEGROUP TEST3
ALTER DATABASE EWYBH ADD FILE(name=N'ByDate4',filename=N'D:\SiSS\DATA\ByDate4.ndf',size=5Mb,filegrowth=5mb) TO FILEGROUP TEST4

右键数据库“属性”——“文件”

创建分区表

右键需要创建分区的表，点击“存储”——“创建分区”

选择分区列（vip_start_date），新建分区函数：ByPartitionFun，新建分区方案：ByPartitionSchema

一路下来之后，脚本生成如下：

USE [EWYBH]

GO

BEGIN TRANSACTION

CREATE PARTITION FUNCTION [ByPartitionFun](datetime) AS RANGE LEFT FOR VALUES (N'2017-01-01T00:00:00', N'2018-01-01T00:00:00', N'2019-01-01T00:00:00')

CREATE PARTITION SCHEME [ByPartitionSchema] AS PARTITION [ByPartitionFun] TO ([PRIMARY], [test1], [test2], [TEST4])

ALTER TABLE [dbo].[t_rm_vip_info] DROP CONSTRAINT [PK_T_RM_VIP_INFO]

SET ANSI_PADDING ON

ALTER TABLE [dbo].[t_rm_vip_info] ADD  CONSTRAINT [PK_T_RM_VIP_INFO] PRIMARY KEY NONCLUSTERED ([card_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_ByPartitionSchema_637313264147968282] ON [dbo].[t_rm_vip_info]( [vip_start_date])WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ByPartitionSchema]([vip_start_date])

DROP INDEX [ClusteredIndex_on_ByPartitionSchema_637313264147968282] ON [dbo].[t_rm_vip_info]

COMMIT TRANSACTION

测试：SELECT * FROM EWYBH.DBO.T_RM_VIP_INFO WHERE VIP_START_DATE>'2018-01-01' AND VIP_START_DATE<'2018-12-31'