一段创建SQLSERVER表分区的脚本示例
作者：[51ak ]
一段创建SQLSERVER表分区的脚本示例，注意是表分区（SQL2008)不是分区表。。
USE [Db_sql_error]
GO
/****** Object: PartitionScheme [my_psch_1month] Script Date: 02/16/2011 17:02:09 ******/
CREATE PARTITION SCHEME [my_psch_1month] AS PARTITION [my_Pfun_1month] TO ([fg3], [fg2], [fg1], [fg3])
GO
ALTER PARTITION FUNCTION myRangePF1()
SPLIT RANGE (500);
USE [Db_sql_error]
GO
/****** Object: PartitionFunction [my_Pfun_1month] Script Date: 02/16/2011 17:04:25 ******/
CREATE PARTITION FUNCTION [my_Pfun_1month](datetime) AS RANGE RIGHT FOR VALUES (N'2010-09-01T00:00:00.000', N'2010-10-01T00:00:00.000', N'2011-01-01T00:00:00.000')
GO
--增加一个分区
declare @f_date datetime=getdate()
declare @f_str varchar(20)
set @f_str=left(CONVERT(varchar(20),getdate(),23),7)+'-01'
set @f_date=CONVERT(datetime,@f_str)
print @f_date
ALTER PARTITION FUNCTION [my_Pfun_1month]()
SPLIT RANGE (@f_date);
--减掉一个分区
ALTER PARTITION FUNCTION [my_Pfun_1month]()
MERGE RANGE ('2010-10-01T00:00:00.000');
Ｓelect partition = $partition.[my_Pfun_1month](addtime)
,rows = count(*)
,minval = min(addtime)
,maxval = max(addtime)
from dbo.mrtg_inout
group by $partition.[my_Pfun_1month](addtime)
order by partition
go
/*
USE [master]
GO
ALTER DATABASE [Db_sql_error] ADD FILEGROUP [Month1]
GO
ALTER DATABASE [Db_sql_error] ADD FILEGROUP [Month2]
GO
ALTER DATABASE [Db_sql_error] ADD FILEGROUP [Month3]
GO
ALTER DATABASE [Db_sql_error] ADD FILE ( NAME = N'month1', FILENAME = N'F:\SouFun_Data\Db_sql_error\Db_sql_error_Moth1.ndf' , SIZE = 3072KB , FILEGROWTH = 102400KB ) TO FILEGROUP [Month1]
GO
ALTER DATABASE [Db_sql_error] ADD FILE ( NAME = N'month2', FILENAME = N'F:\SouFun_Data\Db_sql_error\Db_sql_error_Moth2.ndf' , SIZE = 3072KB , FILEGROWTH = 102400KB ) TO FILEGROUP [Month2]
GO
ALTER DATABASE [Db_sql_error] ADD FILE ( NAME = N'month3', FILENAME = N'F:\SouFun_Data\Db_sql_error\Db_sql_error_Moth3.ndf' , SIZE = 3072KB , FILEGROWTH = 102400KB ) TO FILEGROUP [Month3]
*/
alter partition scheme [my_psch_1month] next used [month1]
alter partition scheme [my_psch_1month] next used [month2]
alter partition scheme [my_psch_1month] next used [month3]
declare @f_date datetime=getdate()
declare @f_str varchar(20)
set @f_str=left(CONVERT(varchar(20),getdate(),23),7)+'-01'
set @f_date=CONVERT(datetime,@f_str)
print @f_date
ALTER PARTITION FUNCTION [my_Pfun_1month]()
SPLIT RANGE (@f_date);