SqlServer表分区

1.  创建文件组和文件

图形界面： 选择数据库--右键属性--文件组 和 文件

 

脚本

alter database CollectionDB add filegroup [fg2014]
alter database CollectionDB add filegroup [fg2015]
alter database CollectionDB add filegroup [fg2016]
alter database CollectionDB add filegroup [fg2017]
alter database CollectionDB add filegroup [fg2018]
alter database CollectionDB add filegroup [fg2019]
alter database CollectionDB add filegroup [fg2020]

alter database CollectionDB
add file
(name='fg2014',
filename='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\partitions\fg2014.ndf',
SIZE = 5MB,  
MAXSIZE=4096MB,  
FILEGROWTH=1)
to filegroup [fg2014]

alter database CollectionDB
add file
(name='fg2015',
filename='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\partitions\fg2015.ndf',
SIZE = 5MB,  
MAXSIZE=4096MB,  
FILEGROWTH=1)
to filegroup [fg2015]

alter database CollectionDB
add file
(name='fg2016',
filename='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\partitions\fg2016.ndf',
SIZE = 5MB,  
MAXSIZE=4096MB,  
FILEGROWTH=1)
to filegroup [fg2016]

alter database CollectionDB
add file
(name='fg2017',
filename='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\partitions\fg2017.ndf',
SIZE = 5MB,  
MAXSIZE=4096MB,  
FILEGROWTH=1)
to filegroup [fg2017]

alter database CollectionDB
add file
(name='fg2018',
filename='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\partitions\fg2018.ndf',
SIZE = 5MB,  
MAXSIZE=4096MB,  
FILEGROWTH=1)
to filegroup [fg2018]

alter database CollectionDB
add file
(name='fg2019',
filename='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\partitions\fg2019.ndf',
SIZE = 5MB,  
MAXSIZE=4096MB,  
FILEGROWTH=1)
to filegroup [fg2019]

alter database CollectionDB
add file
(name='fg2020',
filename='D:\Data\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\partitions\fg2020.ndf',
SIZE = 5MB,  
MAXSIZE=4096MB,  
FILEGROWTH=1)
to filegroup [fg2020]

2. 创建分区函数和分区方案

图形界面：选择数据库表--右键存储--创建分区

参考：http://www.cnblogs.com/chenxizhang/archive/2009/04/29/1445834.html

脚本：

USE [CollectionDB]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [date_partition_function](date) AS RANGE LEFT FOR VALUES (N'2014-12-31', N'2015-12-31', N'2016-12-31', N'2017-12-31', N'2018-12-31', N'2019-12-31')


CREATE PARTITION SCHEME [date_partition_schema] AS PARTITION [date_partition_function] TO ([fg2014], [fg2015], [fg2016], [fg2017], [fg2018], [fg2019], [fg2020])


CREATE CLUSTERED INDEX [ClusteredIndex_on_date_partition_schema_636361758109601773] ON [dbo].[ut_QiBao_TradeDetail] 
(
	[TradeDate]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [date_partition_schema]([TradeDate])


DROP INDEX [ClusteredIndex_on_date_partition_schema_636361758109601773] ON [dbo].[ut_QiBao_TradeDetail] WITH ( ONLINE = OFF )


COMMIT TRANSACTION

如果想对其它表进行Date分区，可继续使用已创建的分区函数和分区方案