SQLSERVER2005中分区表的设计
一、 概要
1.1 实现目标
SQL Server 2005 中基于表的分区功能简化了分区表的创建和维护过程,给数据库提供了灵活性和更好的性能;如果能良好应用将改善数据库大型表性能,设计人员管理性的的设计和实现;并方便于DBA相关维护工作1.2 参考资料
1 微软msdnhttp://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true 2 WebCastsSQL Server2005中的表分区功能和索引 http://www.microsoft.com/china/msdn/events/webcasts/shared/webcast/episode.aspx?newsID=1242313 3 sqlserver2005 中文帮助ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/f1745145-182d-4301-a334-18f799d361d1.htm
二、 需求描述
2.1主要参与者
2.1.1数据库管理人员DBA2.1.2 负责相关模块的SA2.2系统相关人员及其兴趣
2.2.1 User ,希望系统能快速准确的新增,修改,删除,查询,到业务数据;同時因為業務需要和審計需要更多的數據2.2.2 PM 使管理设计维护功能进一步分开,让数据库维护部分交给DBA进行性能调优;同时系统能更好的相应用户操作;2.2.3 SA 简化对数据库表的设计,并把可以把数据库设计,可把优化的工作交给DBA处理2.2.3 Develper 简化开发工作2.2.4 DBA减少备份,聚合数据带来的用户响应等待;充分利用多硬盘,多CPU能并发处理数据相关工作;利用分區,减能少磁盘I/O读取;方便实现负载平衡,均衡I/O;建立起良好的数据库架构工作,方便数据库服务器性扩展,減少約束和表的結構的維護; 2.3前置条件
2.3.1了解基础环境,包括数据库,硬件的准备和基本配置情况数据库是SQLServer2005 SQL Server Enterprise的版本硬件 系统是多硬盘,多CPU为佳 2.3.2现在业务中存在需要性能优化和管理方便的地方可以优化的有A. 如何處理 transaction 數據多的 Table1. 按定時抄去 _Arc 的 database,只留有用(e.g. report要用) 的 data 在 現行的DB
2. 按年份月份 開 Table上面的方法存在的问题有每年需要对该表进行定时清理数据,在清理数据的时候会使得该表的索引变得和/或索引变得支离破碎和/或被锁定,同时处理这些数据的时候将会使系统阻塞的情况,容易使得正常的操作无法正常运行,处理后还需要重修复索引;整个工作比较毫时和影响业务模组的正常运行(虽然可以把这个工作放在周末进行,但是随着业务数据的增加将会变得庞大)B 查询速度由于Data较多的原因,查询和修改数据比较慢,一次查詢會掃描整個表的數據 2.3.3 DBA需要充分了解分区技术每一个关键点; 包括创建,维护方法 2.3.4用SQL PROFILER找出性能存在的瓶颈的SQL 2.4成功后的保证
2.4.1建立分区函数,分区架构,和分区表;2.4.2 DBA应知道如何维护其架构;2.4.3 SA了解其技术;2.5基本流程
[描述能够满足项目相关人员兴趣的典型的成功路径]2.5.1确定系统存在的瓶颈问题2.5.2 确定需要分区的表2.5.3确定分区键和分区数目;2.5.4建立分区函数,分区架构,分区表;2.5.5维护分区函数,架构, 分区表2.5.6在删除数据和备份中使用分区表 2.6扩展流程(替代流程)
扩展流程主要在,分区表设置前后的对比2.6.1 查询成本的比例变化2.6.2 windows性能检视器看看对磁盘i/o,和cpu内存变化(能在测试服务器里测试需要压力测试才能查看结果) 2.7特殊流程
2.8技术与数据的变化列表
2.9发生频率
经常 2.10待解决的问题.
2.10.1建立完分区表后,建立不指定分区的聚集索引,可能会导致分区表变成非分区表 2.10.2 如果以DataTime类型做函数区分,可能带来3微秒的误差,参考msdn提出http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true必须更改日期范围。因为您要处理的是 datetime 数据,而在时间的存储方式方面又存在舍入问题,所以必须能够通过编程方式确定正确的毫秒值。要确定月末最后的 datetime 值,最容易的方法是将正在处理的月份加上 1 个月,然后再减去 2 或 3 毫秒。不能只减去 1 毫秒,因为 59.999 会上舍入为 .000,即下个月的第一天。可以减去 2 或 3 毫秒,因为 2 毫秒将向下舍入为 .997,而 3 毫秒等于 .997;.997 是可以存储的有效值。这样即可确定 datetime 范围的正确结束值:三、 系统设计
3.1系统流程设计
建立分区函数,分区架构,分区表,维护分区表结构,数据备份SQL脚本3.2数据库设计
先处理StLotLdg表 3.3模块功能详细设计
3.3.1建立多个文件组该步骤是创建数据库文件分布到多个磁盘中,以获取更好的性能(也可以创建在一个磁盘中) 1 创建文件组 ALTER DATABASE DC_CECCSADD FILEGROUP [DC_CECCS_FG1]GO ALTER DATABASE DC_CECCSADD FILEGROUP [DC_CECCS_FG2]GO …. 2 把文件组加入到驱动器中 INSERT dbo.FilegroupInfo VALUES (1, 1, N'C:\SalesDB')INSERT dbo.FilegroupInfo VALUES (2, 2, N'D:\SalesDB') ….. 3查看分组信息 创建函数BaseDB..FnPartitionInfo或sql 4 更改文件组语句 ALTER DATABASE Dc_ceccs ADD FILE 5 查看文件组大小exec sp_helpfile3.3.2 创建分区表函数,架构,分区表,索引 1创建分区表函数CREATE PARTITION FUNCTION RangeByMonth (datetime) 注:这里有 RANGE 和 Left 的方式 更改分区函数为ALTER PARTITION FUNCTION 2创建分区表架构 CREATE PARTITION SCHEME [SchemeByMonth] 注:可更改分区架构 更改分区架构方式为: ALTER PARTITION SCHEME 3 在表中使用分区架构在sqlserver2000 里需要手工把数据分别插入不用时间段的表 在sql 2005里只要把数据从原表一次插入分区表就可以了,系统自动根据分区函数分别插入数据(暂不支持直接在原表上创建分区函数) 那么创建步骤如下: A先建立一个备份表 创建新表上使用分区表CREATE TABLE dc_ceccs.[dbo].[ StLotLdgBak] (….) on RangeByMonth B 再把旧表数据插入至新表中use dc_ceccsSELECT * INTO StLotLdgbakFROM StLotLdg C 删除旧表Drop Table StLotLdg D 改新表的名称为旧表 sp_rename 'StLotLdgbak1' , 'StLotLdg' 4 查看分区表信息SELECT $partition.TwoYearDateRangePFN(o.date) AS [Partition Number] , min(o. date) AS [Min Order Date] , max(o. date) AS [Max Order Date] , count(*) AS [Rows In Partition]FROM dbo.StLotLdg AS oGROUP BY $partition.TwoYearDateRangePFN(o. date)ORDER BY [Partition Number]GO 5 添加索引ALTER TABLE StLotLdgADD CONSTRAINT StLotLdgPK PRIMARY KEY CLUSTERED (Date, nvrDocno) ON SchemeByMonth (Date)GO 6 使用SQL Server 为分区表提供的各种连接策略的查询SELECT o. nvrDocno, o.DateFROM dbo.StLotLdg AS o INNER JOIN dbo.stocklot AS od ON o.lot_no = od. lot_no WHERE o. Date >= '20050701' AND o. Date <= '20040930 11:59:59.997'注,该查询已经使用到了分区表,不用做整个表的扫描 7 备份数据操作 A備份exec xp_cmdshell'bcp "SELECT * FROM StLotLdg WHERE date < ''2006/02/01'' AND date > ''2001/01/01'' " queryout "StLotLdg.txt" -T -c"'或 备份至本机 或备份至其他表 Create table StLotLdg200601 … on [DC_CECCS_FG1] 在同一个分区里执行插入数据 INSERT into StLotLdg200601 SELECT * from StLotLdg where date = < '2006/02/01' AND date > '2001/01/01')插入后建索引ALTER TABLE [StLotLdg200601]ADD CONSTRAINT StLotLdg200601PK PRIMARY KEY CLUSTERED (Date, nvrDocno)ON [DC_CECCS_FG1]GO 此时已经使用到分区表,查询只会进行在相应的历史分区进行查询,对现在进行的分区数据影响较小 B删除原表的数据truncate table 你的表 或 建立一个作业指定操作时间在晚上进行 while exists (select 1 from StLotLdg where date = < '2006/02/01' AND date > '2001/01/01')begin set rowcount 10000 delete StLotLdg where here date = < '2006/02/01' AND date > '2001/01/01' set rowcount 0end 注 时间可以取 getdate,判断时间为 datediff(Year,date,getdate())<=1 C恢复exec xp_cmdshell‘bcp dbname.dbo.table_name in d:\ StLotLdg.txt -c -q -S -Usa –P’ 或insert into StLotLdg select * from StLotLdg200601 或ALTER TABLE StLotLdg SWITCH PARTITION 1TO OrdersOctober2002GO 8修改分区表 A 添加一个文件组到数据库
Use masterGOALTER DATABASE Dc_ceccs ADD FILEGROUP DC_CECCS_FG13GOALTER DATABASE Dc_ceccsADD FILE (NAME = N' DC_CECCS_FG13',FileName = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Dc_ceccs13.mdf')TO FILEGROUP DC_CECCS_FG13GOUse Dc_ceccsGO B 修改分区SchemeALTER PARTITION SCHEME RangeByMonthSchemeNEXT USED DC_CECCS_FG13;GO
C 修改分区函数ALTER PARTITION FUNCTION RangeByMonth()SPLIT RANGE ('T/N');GO
一、 概要
1.1 实现目标
SQL Server 2005 中基于表的分区功能简化了分区表的创建和维护过程,给数据库提供了灵活性和更好的性能;如果能良好应用将改善数据库大型表性能,设计人员管理性的的设计和实现;并方便于DBA相关维护工作1.2 参考资料
1 微软msdnhttp://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true 2 WebCastsSQL Server2005中的表分区功能和索引 http://www.microsoft.com/china/msdn/events/webcasts/shared/webcast/episode.aspx?newsID=1242313 3 sqlserver2005 中文帮助ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/f1745145-182d-4301-a334-18f799d361d1.htm
二、 需求描述
2.1主要参与者
2.1.1数据库管理人员DBA2.1.2 负责相关模块的SA2.2系统相关人员及其兴趣
2.2.1 User ,希望系统能快速准确的新增,修改,删除,查询,到业务数据;同時因為業務需要和審計需要更多的數據2.2.2 PM 使管理设计维护功能进一步分开,让数据库维护部分交给DBA进行性能调优;同时系统能更好的相应用户操作;2.2.3 SA 简化对数据库表的设计,并把可以把数据库设计,可把优化的工作交给DBA处理2.2.3 Develper 简化开发工作2.2.4 DBA减少备份,聚合数据带来的用户响应等待;充分利用多硬盘,多CPU能并发处理数据相关工作;利用分區,减能少磁盘I/O读取;方便实现负载平衡,均衡I/O;建立起良好的数据库架构工作,方便数据库服务器性扩展,減少約束和表的結構的維護; 2.3前置条件
2.3.1了解基础环境,包括数据库,硬件的准备和基本配置情况数据库是SQLServer2005 SQL Server Enterprise的版本硬件 系统是多硬盘,多CPU为佳 2.3.2现在业务中存在需要性能优化和管理方便的地方可以优化的有A. 如何處理 transaction 數據多的 Table1. 按定時抄去 _Arc 的 database,只留有用(e.g. report要用) 的 data 在 現行的DB
2. 按年份月份 開 Table上面的方法存在的问题有每年需要对该表进行定时清理数据,在清理数据的时候会使得该表的索引变得和/或索引变得支离破碎和/或被锁定,同时处理这些数据的时候将会使系统阻塞的情况,容易使得正常的操作无法正常运行,处理后还需要重修复索引;整个工作比较毫时和影响业务模组的正常运行(虽然可以把这个工作放在周末进行,但是随着业务数据的增加将会变得庞大)B 查询速度由于Data较多的原因,查询和修改数据比较慢,一次查詢會掃描整個表的數據 2.3.3 DBA需要充分了解分区技术每一个关键点; 包括创建,维护方法 2.3.4用SQL PROFILER找出性能存在的瓶颈的SQL 2.4成功后的保证
2.4.1建立分区函数,分区架构,和分区表;2.4.2 DBA应知道如何维护其架构;2.4.3 SA了解其技术;2.5基本流程
[描述能够满足项目相关人员兴趣的典型的成功路径]2.5.1确定系统存在的瓶颈问题2.5.2 确定需要分区的表2.5.3确定分区键和分区数目;2.5.4建立分区函数,分区架构,分区表;2.5.5维护分区函数,架构, 分区表2.5.6在删除数据和备份中使用分区表 2.6扩展流程(替代流程)
扩展流程主要在,分区表设置前后的对比2.6.1 查询成本的比例变化2.6.2 windows性能检视器看看对磁盘i/o,和cpu内存变化(能在测试服务器里测试需要压力测试才能查看结果) 2.7特殊流程
2.8技术与数据的变化列表
2.9发生频率
经常 2.10待解决的问题.
2.10.1建立完分区表后,建立不指定分区的聚集索引,可能会导致分区表变成非分区表 2.10.2 如果以DataTime类型做函数区分,可能带来3微秒的误差,参考msdn提出http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true必须更改日期范围。因为您要处理的是 datetime 数据,而在时间的存储方式方面又存在舍入问题,所以必须能够通过编程方式确定正确的毫秒值。要确定月末最后的 datetime 值,最容易的方法是将正在处理的月份加上 1 个月,然后再减去 2 或 3 毫秒。不能只减去 1 毫秒,因为 59.999 会上舍入为 .000,即下个月的第一天。可以减去 2 或 3 毫秒,因为 2 毫秒将向下舍入为 .997,而 3 毫秒等于 .997;.997 是可以存储的有效值。这样即可确定 datetime 范围的正确结束值:三、 系统设计
3.1系统流程设计
建立分区函数,分区架构,分区表,维护分区表结构,数据备份SQL脚本3.2数据库设计
先处理StLotLdg表 3.3模块功能详细设计
3.3.1建立多个文件组该步骤是创建数据库文件分布到多个磁盘中,以获取更好的性能(也可以创建在一个磁盘中) 1 创建文件组 ALTER DATABASE DC_CECCSADD FILEGROUP [DC_CECCS_FG1]GO ALTER DATABASE DC_CECCSADD FILEGROUP [DC_CECCS_FG2]GO …. 2 把文件组加入到驱动器中 INSERT dbo.FilegroupInfo VALUES (1, 1, N'C:\SalesDB')INSERT dbo.FilegroupInfo VALUES (2, 2, N'D:\SalesDB') ….. 3查看分组信息 创建函数BaseDB..FnPartitionInfo或sql 4 更改文件组语句 ALTER DATABASE Dc_ceccs ADD FILE 5 查看文件组大小exec sp_helpfile3.3.2 创建分区表函数,架构,分区表,索引 1创建分区表函数CREATE PARTITION FUNCTION RangeByMonth (datetime) 注:这里有 RANGE 和 Left 的方式 更改分区函数为ALTER PARTITION FUNCTION 2创建分区表架构 CREATE PARTITION SCHEME [SchemeByMonth] 注:可更改分区架构 更改分区架构方式为: ALTER PARTITION SCHEME 3 在表中使用分区架构在sqlserver2000 里需要手工把数据分别插入不用时间段的表 在sql 2005里只要把数据从原表一次插入分区表就可以了,系统自动根据分区函数分别插入数据(暂不支持直接在原表上创建分区函数) 那么创建步骤如下: A先建立一个备份表 创建新表上使用分区表CREATE TABLE dc_ceccs.[dbo].[ StLotLdgBak] (….) on RangeByMonth B 再把旧表数据插入至新表中use dc_ceccsSELECT * INTO StLotLdgbakFROM StLotLdg C 删除旧表Drop Table StLotLdg D 改新表的名称为旧表 sp_rename 'StLotLdgbak1' , 'StLotLdg' 4 查看分区表信息SELECT $partition.TwoYearDateRangePFN(o.date) AS [Partition Number] , min(o. date) AS [Min Order Date] , max(o. date) AS [Max Order Date] , count(*) AS [Rows In Partition]FROM dbo.StLotLdg AS oGROUP BY $partition.TwoYearDateRangePFN(o. date)ORDER BY [Partition Number]GO 5 添加索引ALTER TABLE StLotLdgADD CONSTRAINT StLotLdgPK PRIMARY KEY CLUSTERED (Date, nvrDocno) ON SchemeByMonth (Date)GO 6 使用SQL Server 为分区表提供的各种连接策略的查询SELECT o. nvrDocno, o.DateFROM dbo.StLotLdg AS o INNER JOIN dbo.stocklot AS od ON o.lot_no = od. lot_no WHERE o. Date >= '20050701' AND o. Date <= '20040930 11:59:59.997'注,该查询已经使用到了分区表,不用做整个表的扫描 7 备份数据操作 A備份exec xp_cmdshell'bcp "SELECT * FROM StLotLdg WHERE date < ''2006/02/01'' AND date > ''2001/01/01'' " queryout "StLotLdg.txt" -T -c"'或 备份至本机 或备份至其他表 Create table StLotLdg200601 … on [DC_CECCS_FG1] 在同一个分区里执行插入数据 INSERT into StLotLdg200601 SELECT * from StLotLdg where date = < '2006/02/01' AND date > '2001/01/01')插入后建索引ALTER TABLE [StLotLdg200601]ADD CONSTRAINT StLotLdg200601PK PRIMARY KEY CLUSTERED (Date, nvrDocno)ON [DC_CECCS_FG1]GO 此时已经使用到分区表,查询只会进行在相应的历史分区进行查询,对现在进行的分区数据影响较小 B删除原表的数据truncate table 你的表 或 建立一个作业指定操作时间在晚上进行 while exists (select 1 from StLotLdg where date = < '2006/02/01' AND date > '2001/01/01')begin set rowcount 10000 delete StLotLdg where here date = < '2006/02/01' AND date > '2001/01/01' set rowcount 0end 注 时间可以取 getdate,判断时间为 datediff(Year,date,getdate())<=1 C恢复exec xp_cmdshell‘bcp dbname.dbo.table_name in d:\ StLotLdg.txt -c -q -S -Usa –P’ 或insert into StLotLdg select * from StLotLdg200601 或ALTER TABLE StLotLdg SWITCH PARTITION 1TO OrdersOctober2002GO 8修改分区表 A 添加一个文件组到数据库
Use masterGOALTER DATABASE Dc_ceccs ADD FILEGROUP DC_CECCS_FG13GOALTER DATABASE Dc_ceccsADD FILE (NAME = N' DC_CECCS_FG13',FileName = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Dc_ceccs13.mdf')TO FILEGROUP DC_CECCS_FG13GOUse Dc_ceccsGO B 修改分区SchemeALTER PARTITION SCHEME RangeByMonthSchemeNEXT USED DC_CECCS_FG13;GO
C 修改分区函数ALTER PARTITION FUNCTION RangeByMonth()SPLIT RANGE ('T/N');GO
解决方案 »
- 如何求得未出现问题的某型号次数
- Excel表导入数据库的问题,,,求教帮忙解决一下 努了一天了。。实在不懂了。。。
- 帮忙优化
- 我的数据库1周一次完全备份,一小时一次差异备份。这样备份已经5年了。但是我需要恢复3年前某一周中某天的数据该如何手动操作?
- 请问大力和各位高手,关于instead of 触发器!不好意思没分了
- 如何提取往前7天到运行查询代码时的数据?
- 可按任意字段排序分页的一个sql存储过程问题
- 有关存储过程的问题
- 这个存储过程要如何写?
- 请教sqlserver7.0在win2k域服务器上的登陆设置和在独立服务器上的设置有什么不同?
- 新手请教一个 UPDEAT 的问题
- 为什么清除SQL里日志后无法进行备份
USE master
-- 备份
BACKUP DATABASE AdventureWorks
TO DISK = 'AdventureWorks.bak'
WITH FORMAT---- 恢复
--RESTORE DATABASE AdventureWorks
-- FROM DISK = 'AdventureWorks.bak'
-- WITH REPLACE
GO--=========================================
-- 转换为分区表
--=========================================
USE AdventureWorks
GO-- 1. 创建分区函数
-- a. 适用于存储历史存档记录的分区表的分区函数
DECLARE @dt datetime
SET @dt = '20020101'
CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)
AS RANGE RIGHT
FOR VALUES(
@dt,
DATEADD(Year, 1, @dt))-- b. 适用于存储历史记录的分区表的分区函数
--DECLARE @dt datetime
SET @dt = '20030901'
CREATE PARTITION FUNCTION PF_History(datetime)
AS RANGE RIGHT
FOR VALUES(
@dt,
DATEADD(Month, 1, @dt), DATEADD(Month, 2, @dt), DATEADD(Month, 3, @dt),
DATEADD(Month, 4, @dt), DATEADD(Month, 5, @dt), DATEADD(Month, 6, @dt),
DATEADD(Month, 7, @dt), DATEADD(Month, 8, @dt), DATEADD(Month, 9, @dt),
DATEADD(Month, 10, @dt), DATEADD(Month, 11, @dt), DATEADD(Month, 12, @dt))
GO-- 2. 创建分区架构
-- a. 适用于存储历史存档记录的分区表的分区架构
CREATE PARTITION SCHEME PS_HistoryArchive
AS PARTITION PF_HistoryArchive
TO([PRIMARY], [PRIMARY], [PRIMARY])-- b. 适用于存储历史记录的分区表的分区架构
CREATE PARTITION SCHEME PS_History
AS PARTITION PF_History
TO([PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY])
GO-- 3. 删除索引
-- a. 删除存储历史存档记录的表中的索引
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductID
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID-- b. 删除存储历史记录的表中的索引
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ProductID
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
GO-- 4. 转换为分区表
-- a. 将存储历史存档记录的表转换为分区表
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH(
MOVE TO PS_HistoryArchive(TransactionDate))-- b.将存储历史记录的表转换为分区表
ALTER TABLE Production.TransactionHistory
DROP CONSTRAINT PK_TransactionHistory_TransactionID
WITH(
MOVE TO PS_History(TransactionDate))
GO-- 5. 恢复主键
-- a. 恢复存储历史存档记录的分区表的主键
ALTER TABLE Production.TransactionHistoryArchive
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)-- b. 恢复存储历史记录的分区表的主键
ALTER TABLE Production.TransactionHistory
ADD CONSTRAINT PK_TransactionHistory_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
GO-- 6. 恢复索引
-- a. 恢复存储历史存档记录的分区表的索引
CREATE INDEX IX_TransactionHistoryArchive_ProductID
ON Production.TransactionHistoryArchive(
ProductID)CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
ON Production.TransactionHistoryArchive(
ReferenceOrderID,
ReferenceOrderLineID)-- b. 恢复存储历史记录的分区表的索引
CREATE INDEX IX_TransactionHistory_ProductID
ON Production.TransactionHistory(
ProductID)CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
ON Production.TransactionHistory(
ReferenceOrderID,
ReferenceOrderLineID)
GO-- 7. 查看分区表的相关信息
SELECT
SchemaName = S.name,
TableName = TB.name,
PartitionScheme = PS.name,
PartitionFunction = PF.name,
PartitionFunctionRangeType = CASE
WHEN boundary_value_on_right = 0 THEN 'LEFT'
ELSE 'RIGHT' END,
PartitionFunctionFanout = PF.fanout,
SchemaID = S.schema_id,
ObjectID = TB.object_id,
PartitionSchemeID = PS.data_space_id,
PartitionFunctionID = PS.function_id
FROM sys.schemas S
INNER JOIN sys.tables TB
ON S.schema_id = TB.schema_id
INNER JOIN sys.indexes IDX
on TB.object_id = IDX.object_id
AND IDX.index_id < 2
INNER JOIN sys.partition_schemes PS
ON PS.data_space_id = IDX.data_space_id
INNER JOIN sys.partition_functions PF
ON PS.function_id = PF.function_id
GO
-- 移动分区表数据
--=========================================
-- 1. 为存储历史存档记录的分区表增加分区, 并接受从历史记录分区表移动过来的数据
-- a. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]-- b. 修改分区函数, 增加分区用以接受从历史记录分区表移动过来的数据
DECLARE @dt datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)-- c. 将历史记录表中的过期数据移动到历史存档记录表中
ALTER TABLE Production.TransactionHistory
SWITCH PARTITION 2
TO Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(@dt)-- d. 将接受到的数据与原来的分区合并
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)
GO-- 2. 将存储历史记录的分区表中不包含数据的分区删除, 并增加新的分区以接受新数据
-- a. 合并不包含数据的分区
DECLARE @dt datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_History()
MERGE RANGE(@dt)-- b. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_History
NEXT USED [PRIMARY]-- c. 修改分区函数, 增加分区用以接受新数据
SET @dt = '20041001'
ALTER PARTITION FUNCTION PF_History()
SPLIT RANGE(@dt)
GO
--=========================================
-- 清除历史存档记录中的过期数据
--=========================================
-- 1. 创建用于保存过期的历史存档数据的表
CREATE TABLE Production.TransactionHistoryArchive_2001_temp(
TransactionID int NOT NULL,
ProductID int NOT NULL,
ReferenceOrderID int NOT NULL,
ReferenceOrderLineID int NOT NULL
DEFAULT ((0)),
TransactionDate datetime NOT NULL
DEFAULT (GETDATE()),
TransactionType nchar(1) NOT NULL,
Quantity int NOT NULL,
ActualCost money NOT NULL,
ModifiedDate datetime NOT NULL
DEFAULT (GETDATE()),
CONSTRAINT PK_TransactionHistoryArchive_2001_temp_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
)-- 2. 将数据从历史存档记录分区表移动到第1步创建的表中
ALTER TABLE Production.TransactionHistoryArchive
SWITCH PARTITION 1
TO Production.TransactionHistoryArchive_2001_temp-- 3. 删除不再包含数据的分区
DECLARE @dt datetime
SET @dt = '20020101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)-- 4. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]-- 5. 修改分区函数, 增加分区用以接受新数据
SET @dt = '20040101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)
查询分区信息:;WITH
TBINFO AS(
SELECT
SchemaName = S.name,
TableName = TB.name,
PartitionScheme = PS.name,
PartitionFunction = PF.name,
PartitionFunctionRangeType = CASE
WHEN boundary_value_on_right = 0 THEN 'LEFT'
ELSE 'RIGHT' END,
PartitionFunctionFanout = PF.fanout,
SchemaID = S.schema_id,
ObjectID = TB.object_id,
PartitionSchemeID = PS.data_space_id,
PartitionFunctionID = PS.function_id
FROM sys.schemas S
INNER JOIN sys.tables TB
ON S.schema_id = TB.schema_id
INNER JOIN sys.indexes IDX
on TB.object_id = IDX.object_id
AND IDX.index_id < 2
INNER JOIN sys.partition_schemes PS
ON PS.data_space_id = IDX.data_space_id
INNER JOIN sys.partition_functions PF
ON PS.function_id = PF.function_id
),
PF1 AS(
SELECT
PFP.function_id, PFR.boundary_id, PFR.value,
Type = CONVERT(sysname,
CASE T.name
WHEN 'numeric' THEN 'decimal'
WHEN 'real' THEN 'float'
ELSE T.name END
+ CASE
WHEN T.name IN('decimal', 'numeric')
THEN QUOTENAME(RTRIM(PFP.precision)
+ CASE WHEN PFP.scale > 0 THEN ',' + RTRIM(PFP.scale) ELSE '' END, '()')
WHEN T.name IN('float', 'real')
THEN QUOTENAME(PFP.precision, '()')
WHEN T.name LIKE 'n%char'
THEN QUOTENAME(PFP.max_length / 2, '()')
WHEN T.name LIKE '%char' OR T.name LIKE '%binary'
THEN QUOTENAME(PFP.max_length, '()')
ELSE '' END)
FROM sys.partition_parameters PFP
LEFT JOIN sys.partition_range_values PFR
ON PFR.function_id = PFP.function_id
AND PFR.parameter_id = PFP.parameter_id
INNER JOIN sys.types T
ON PFP.system_type_id = T.system_type_id
),
PF2 AS(
SELECT * FROM PF1
UNION ALL
SELECT
function_id, boundary_id = boundary_id - 1, value, type
FROM PF1
WHERE boundary_id = 1
),
PF AS(
SELECT
B.function_id, boundary_id = ISNULL(B.boundary_id + 1, 1),
value = STUFF(
CASE
WHEN A.boundary_id IS NULL THEN ''
ELSE ' AND [partition_column_name] ' + PF.LessThan + ' ' + CONVERT(varchar(max), A.value) END
+ CASE
WHEN A.boundary_id = 1 THEN ''
ELSE ' AND [partition_column_name] ' + PF.MoreThan + ' ' + CONVERT(varchar(max), B.value) END,
1, 5, ''),
B.Type
FROM PF1 A
RIGHT JOIN PF2 B
ON A.function_id = B.function_id
AND (A.boundary_id - 1 = B.boundary_id
OR(A.boundary_id IS NULL AND B.boundary_id IS NULL))
INNER JOIN(
SELECT
function_id,
LessThan = CASE
WHEN boundary_value_on_right = 0 THEN '<='
ELSE '<' END,
MoreThan = CASE
WHEN boundary_value_on_right = 0 THEN '>'
ELSE '>=' END
FROM sys.partition_functions
)PF
ON B.function_id = PF.function_id
),
PS AS(
SELECT
DDS.partition_scheme_id, DDS.destination_id,
FileGroupName = FG.name, IsReadOnly = FG.is_read_only
FROM sys.destination_data_spaces DDS
INNER JOIN sys.filegroups FG
ON DDS.data_space_id = FG.data_space_id
),
PINFO AS(
SELECT
RowID = ROW_NUMBER() OVER(ORDER BY SchemaID, ObjectID, PS.destination_id),
TB.SchemaName, TB.TableName,
TB.PartitionScheme, PS.destination_id, PS.FileGroupName, PS.IsReadOnly,
TB.PartitionFunction, TB.PartitionFunctionRangeType, TB.PartitionFunctionFanout,
PF.boundary_id, PF.Type, PF.value
FROM TBINFO TB
INNER JOIN PS
ON TB.PartitionSchemeID = PS.partition_scheme_id
LEFT JOIN PF
ON TB.PartitionFunctionID = PF.function_id
AND PS.destination_id = PF.boundary_id
)
SELECT
RowID,
SchemaName = CASE destination_id
WHEN 1 THEN SchemaName
ELSE N'' END,
TableName = CASE destination_id
WHEN 1 THEN TableName
ELSE N'' END,
PartitionScheme = CASE destination_id
WHEN 1 THEN PartitionScheme
ELSE N'' END,
destination_id, FileGroupName, IsReadOnly,
PartitionFunction = CASE destination_id
WHEN 1 THEN PartitionFunction
ELSE N'' END,
PartitionFunctionRangeType = CASE destination_id
WHEN 1 THEN PartitionFunctionRangeType
ELSE N'' END,
PartitionFunctionFanout = CASE destination_id
WHEN 1 THEN CONVERT(varchar(20), PartitionFunctionFanout)
ELSE N'' END,
boundary_id = ISNULL(CONVERT(varchar(20), boundary_id), ''),
Type = ISNULL(Type, N''),
value = CASE PartitionFunctionFanout
WHEN 1 THEN '<ALL Data>'
ELSE ISNULL(value, N'<NEXT USED>') END
FROM PINFO
ORDER BY RowID
--==================================
--drop database dbPartitionTest
--测试数据库
create database dbPartitionTest
go
use
dbPartitionTest
go
--增加分组
alter database dbPartitionTest ADD FILEGROUP P200801
alter database dbPartitionTest ADD FILEGROUP P200802
alter database dbPartitionTest ADD FILEGROUP P200803
go
--分区函数
CREATE PARTITION FUNCTION part_Year(datetime)
AS RANGE LEFT FOR VALUES
(
’20080131 23:59:59.997’,’20080229 23:59:59.997’,’20080331 23:59:59.997’
)
go
--增加文件组
ALTER DATABASE dbPartitionTest ADD FILE (NAME = N’P200801’,FILENAME = N’c:tb_P200801.ndf’,SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB)TO FILEGROUP P200801
ALTER DATABASE dbPartitionTest ADD FILE (NAME = N’P200802’,FILENAME = N’c:tb_P200802.ndf’,SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB)TO FILEGROUP P200802
ALTER DATABASE dbPartitionTest ADD FILE (NAME = N’P200803’,FILENAME = N’c:tb_P200803.ndf’,SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB)TO FILEGROUP P200803
go
--分区架构
CREATE PARTITION SCHEME part_YearScheme AS PARTITION part_Year TO (P200801,P200802,P200803,[PRIMARY])
go
CREATE TABLE [dbo].t_part
(name varchar(100) default newid(),date datetime NOT NULL)
ON part_YearScheme (date)
go
--添加测试数据,每天1条
declare @date datetime
set @date=’2007-12-31’
while @date<=’2008-04-0’
1 begin
insert into t_part(date)values(@date)
set @date=@date+1
end
go
--查询数据分布在哪些分区
select $partition.part_Year(date) as 分区编号,* from t_part order by date
--查询数据库文件
go
sp_helpfile