数据库分区实践 - 军军小站|张军博客

--查询一张表的分区

      
        select
      
       $partition.Part_func_UserLog(createdDate) 
      
        as
      
       Patition 
      
        from
      
       dbo.UserLog GROUP BY $partition.Part_func_UserLog(createdDate)

--创建分区的时间段

      
        CREATE PARTITION FUNCTION Part_func_UserLog(datetime)

 AS RANGE LEFT FOR VALUES 

(


      
      
        '
      
      
        20120709 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120710 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120711 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120712 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120713 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120714 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120715 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120716 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120717 23:59:59.997
      
      
        '
      
      
        ,


      
      
        '
      
      
        20120718 23:59:59.997
      
      
        '
      
      
        

);

--创建分区分组

      ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks1];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks2];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks3];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks4];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks5];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks6];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks7];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks8];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks9];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks10];

--创建分区文件

      
        ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks1_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks1_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks1];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks2_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks2_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks2];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks3_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks3_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks3];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks4_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks4_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks4];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks5_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks5_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks5];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks6_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks6_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks6];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks7_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks7_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks7];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks8_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks8_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks8];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks9_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks9_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks9];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks10_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks10_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks10];

--绑定分组

      CREATE PARTITION SCHEME Part_func_UserLog_Scheme

AS PARTITION Part_func_UserLog

TO ([FGGoodBooks1],[FGGoodBooks2],[FGGoodBooks3],[FGGoodBooks4],[FGGoodBooks5],[FGGoodBooks6],[FGGoodBooks7],[FGGoodBooks8],[FGGoodBooks9],[FGGoodBooks10],[Primary]);

GO

--给表添加主键

      ALTER TABLE dbo.UserLog ADD CONSTRAINT PK_UserLog PRIMARY KEY NONCLUSTERED  (UserLogID)

   WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX IX_UserLog_partitioncol ON dbo.UserLog (UserLogID)

  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

  ON Part_func_UserLog_Scheme(CreatedDate)

GO

--删除表的主键、聚集索引

      alter table UserLog drop CONSTRAINT PK_UserLog

--查询指定分区的数据

      select *  from dbo.UserLog WHERE $PARTITION.Part_func_UserLog(CreatedDate)=3

--自动添加分区

      declare  @databaseName varchar(50),  @fileGroupName varchar(50),  @ndfName varchar(50),  @newNameStr varchar(50),  @fullPath 

varchar(500),  @newDay varchar(50),  @oldDay datetime,  @partFunName varchar(50),  @schemeName varchar(50)

set @databaseName='GoodBooks'

set @newDay=CONVERT(varchar(100), getdate(), 23)--23:按天 114:按时间

set @oldDay=cast(CONVERT(varchar(10),dateadd(day,-5,getdate()), 120 ) as datetime)

set @newNameStr=Replace(Replace(@newDay,':','_'),'-','_')

set @fileGroupName=N'G'+@newNameStr

set @ndfName=N'F'+@newNameStr+''

set @fullPath=N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+@ndfName+'.ndf'

set @partFunName=N'Part_func_UserLog'

set @schemeName=N'Part_func_UserLog_Scheme'

--创建文件组

if exists(select * from sys.filegroups where name=@fileGroupName)

begin

	print '文件组存在,不需添加'

end

else

begin

	exec('ALTER DATABASE '+@databaseName+' ADD FILEGROUP ['+@fileGroupName+']')

	print '新增文件组'

	if exists(select * from sys.partition_schemes where name =@schemeName)

	begin

		exec('alter partition scheme '+@schemeName+'  next used ['+@fileGroupName+']')

		print '修改分区方案'

	end

	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 '修改分区函数'

	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 '+@databaseName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')

	PRINT 'ALTER DATABASE '+@databaseName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']'

	print '新创建ndf文件'

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 '新创建分区函数'

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 '新创建分区方案'

end

print '---------------以下是变量定义值显示---------------------'

print '当前数据库:'+@databaseName

print '当前日期:'+@newDay+'(用作随机生成的各种名称和分区界限)'

print '合法命名方式:'+@newNameStr

print '文件组名称:'+@fileGroupName

print 'ndf物理文件名称:'+@ndfName

print '物理文件完整路径:'+@fullPath

print '分区函数:'+@partFunName

print '分区方案:'+@schemeName

/*

--查看创建的分区函数

select * from sys.partition_functions

--查看分区函数的临界值

select * from sys.partition_range_values

--查询分区方案

select * from sys.partition_schemes

--查询表数据在哪个分区中存储,where条件查询第一个分区中存在的数据

select *,$partition.pf_SaveTime(分区字段) as Patition from 表名 where $partition.pf_SaveTime(分区字段)=1

*/

GO

数据库分区实践

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义