SQL SERVER2005分区表创建SQL

本文主要是介绍SQL SERVER2005分区表创建SQL，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

--创建分区表之前，请在新建数据前添加数据库文件和文件组(文件组数>=分区数)
--创建分区函数(有三个范围会产生四个分区)
CREATE PARTITION FUNCTION FiveYearDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES (
'20061031 23:59:59.997',
'20061130 23:59:59.997',
'20061231 23:59:59.997')

--删除PARTITION FUNCTION
--DROP PARTITION FUNCTION FiveYearDateRangePFN

 

 

--分区映射到文件组的方案('200610'代表文件组，文件组的个数不得少于分区的个数,文件组包括数据文件)
CREATE PARTITION SCHEME [FiveYearDateRangePScheme]
AS
PARTITION FiveYearDateRangePFN TO
('200610','200611','200612','200701')

--删除SCHEME
--DROP PARTITION SCHEME [FiveYearDateRangePScheme]

--创建分区表
CREATE TABLE PARTITIONTABLE (P_NAME VARCHAR(10),BIRTHDAY DATETIME)
ON FiveYearDateRangePScheme(BIRTHDAY)

--插入测试数据
INSERt INTO PARTITIONTABLE values ('a','2006-5-1')

INSERt INTO PARTITIONTABLE values ('b','2006-8-1')

INSERt INTO PARTITIONTABLE values ('c','2006-10-1')

INSERt INTO PARTITIONTABLE values ('d','2006-11-1')

INSERt INTO PARTITIONTABLE values ('e','2006-12-1')

INSERt INTO PARTITIONTABLE values ('f','2007-5-1')

--查看数据是否写到相应的分区
select $partition.FiveYearDateRangePFN(BIRTHDAY) as PARTITIONT_ID,BIRTHDAY,* from PARTITIONTABLE

--创建分区索引
create index PARTITION_INDEX ON PARTITIONTABLE(BIRTHDAY) ON FiveYearDateRangePScheme(BIRTHDAY)

这篇关于SQL SERVER2005分区表创建SQL的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---