--查詢一張表的分區
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元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
