create partition sheme, partition function

1. create SP to create or maintain the partition sheme, partition function, file, file group, 

USE [DYDB]
GO


/****** Object:  StoredProcedure [dbo].[sp_maintain_partion]    Script Date: 2017/1/18 10:41:21 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO





CREATE procedure [dbo].[sp_maintain_partion] (
@tableName varchar(50),
@inputdate datetime  
)
as begin
declare
@fileGroupName varchar(50),
@ndfName varchar(50),  
@newNameStr varchar(50),  
@fullPath varchar(50),  
@newDay varchar(50),  
@oldDay datetime,  
@partFunName varchar(50),  
@schemeName varchar(50),
@sqlstr varchar(1000)


--set @tableName='DYDB'
set @newDay=CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@inputdate), 0), 23 )--CONVERT(varchar(100), @inputdate, 23)--23:按天 114:按时间
set @oldDay=cast(CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@inputdate)-1, 0), 112 ) as datetime)
set @newNameStr=left(Replace(Replace(@newDay,':','_'),'-','_'),7)
set @fileGroupName=N'G'+@newNameStr
set @ndfName=N'F'+@newNameStr+''
set @fullPath=N'F:\SQLData\PartitionData\'+@tableName+'\'+@tableName+'_'+@ndfName+'.ndf'
set @partFunName=N'pf_Time'
set @schemeName=N'ps_Time'






--创建文件组
if exists(select * from sys.filegroups where name=@fileGroupName)
begin
print '文件组存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')
--print 'exec '+('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')
print '新增文件组'


if exists(select * from sys.partition_schemes where name =@schemeName)
begin
exec('alter partition scheme '+@schemeName+'  next used ['+@fileGroupName+']')
print 'exec '+('alter partition scheme '+@schemeName+'  next used ['+@fileGroupName+']')
print '修改分区方案'
end


--select * from sys.partition_range_values where function_id=(select function_id from 
--sys.partition_functions where name =@partFunName) and value=@oldDay
--print @partFunName
--print @oldDay
if exists(select * from sys.partition_range_values where function_id=(select function_id from 
sys.partition_functions where name =@partFunName) and value=@oldDay)
begin
exec('alter partition function  '+@partFunName+'() split range('''+@newDay+''')')
print 'exec '+('alter partition function  '+@partFunName+'() split range('''+@newDay+''')')
print '修改分区函数'
end
end




--创建NDF文件
if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))
begin
print 'ndf文件存在,不需添加'
end


else
begin
exec('ALTER DATABASE '+@tableName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')
--print 'ALTER DATABASE '+@tableName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']'


print '新创建ndf文件'
end
--/*--------------------以上创建数据库的文件组和物理文件------------------------*/
end


--分区函数
if exists(select * from sys.partition_functions where name =@partFunName)
begin
print '此处修改需要在修改分区函数之前执行'
end
else
begin
exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHT FOR VALUES ('''+@newDay+''')')
--print 'CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHT FOR VALUES ('''+@newDay+''')'
print '新创建分区函数'
end
--分区方案
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
print '此处修改需要在修改分区方案之前执行'
end
else
begin
exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO (''PRIMARY'','''+@fileGroupName+''')')
--print ('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO (''PRIMARY'','''+@fileGroupName+''')')
print '新创建分区方案'
end
GO


2. initial the partition sheme, partition function, file, fg
declare @dd datetime,
@dbname varchar(100) --需要分区的库名


set @dbname='DYDB'
set @dd='2013-01-01'
set @dd=DATEADD(mm, DATEDIFF(mm,0,@dd), 0) --取当月第一天
while @dd<=DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
begin
--print @dd
exec sp_maintain_partion @dbname,@dd
--print ('[DYDB].[dbo].[sp_maintain_partion]'+''''+'DYDB'+''''+','+ ''''+CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@dd), 0), 23 )+'''')
set @dd=DATEADD(mm, DATEDIFF(mm,0,@dd)+1, 0)
end

3. create a job ,which on 25th everymonth, this job will create the data file, file group, and modify the partition function, partition sheme
job name montly maintian partition

declare @dd datetime,
@dbname varchar(100) --需要分区的库名
begin
set @dbname='DYDB'
set @dd=DATEADD(mm, DATEDIFF(mm,0,getdate())+1, 0) --取当月第一天
--print @dd
exec sp_maintain_partion @dbname,@dd
end


4. apply the partition function on existiong table 

USE [DYDB]
GO
BEGIN TRANSACTION
ALTER TABLE [dbo].[tablename] DROP CONSTRAINT [PK__ tablename__3214EC2773C5E8D2]




ALTER TABLE [dbo].[ tablename] ADD  CONSTRAINT [PK__ tablename__3214EC2773C5E8D2] PRIMARY KEY NONCLUSTERED 
(
[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 [DATA]




CREATE CLUSTERED INDEX [ClusteredIndex_on_ps_Time_636203337955852229] ON [dbo].[ tablename]
(
[CREATE_TIME]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_Time]([CREATE_TIME])




DROP INDEX [ClusteredIndex_on_ps_Time_636203337955852229] ON [dbo].[ tablename]








COMMIT TRANSACTION

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16131092/viewspace-2132615/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16131092/viewspace-2132615/