SQL Server 2005分区表学习笔记

最新推荐文章于 2025-10-21 22:09:11 发布
原创 最新推荐文章于 2025-10-21 22:09:11 发布 · 964 阅读 · 1 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
标签
#sql server #null #table #insert #database
#scheme
本文介绍SQL Server中分区表的创建步骤及滑动窗口方案实施过程，包括创建文件组、定义分区函数与架构、创建及切换分区表等关键技术点。

参考自：http://msdn.microsoft.com/zh-cn/library/ms345146(SQL.90).aspx
本文分两部分讲解，首先介绍分区表怎么创建，然后介绍滑动窗口方案，即如何移出旧分区和移入新分区。

一、分区表

1、创建文件组

alter database myDB add filegroup FG1 
alter database myDB add filegroup FG2 
alter database myDB add filegroup FG3

2、创建分区信息表

create table FilegroupInfo 
( 
    PartitionNumber    tinyint, 
    FilegroupNumber    tinyint, 
    Location nvarchar(50) 
) 
insert FilegroupInfo values(1,1,N'C:/db') 
insert FilegroupInfo values(2,2,N'C:/db') 
insert FilegroupInfo values(3,3,N'C:/db')

3、创建文件

declare @PartitionNumber tinyint,@Location nvarchar(50),@ExecStr nvarchar(300) 
declare FilegroupsToCreate cursor for 
    select PartitionNumber,Location from FileGroupInfo order by PartitionNumber 
open FilegroupsToCreate 
fetch next from FilegroupsToCreate into @PartitionNumber,@Location 
while(@@fetch_status<>-1) 
begin 
    if(@@fetch_status<>-2) 
    begin 
    select @ExecStr=N'alter database myDB add file'+ 
              N'(name=N''myDBFG'+CONVERT(nvarchar,@PartitionNumber)+N'File'','+ 
              N'filename=N'''+@Location+N'/myDBFG'+CONVERT(nvarchar, @PartitionNumber)+'File.ndf'','+ 
              N'size=1MB,maxsize=100MB,filegrowth=5MB)'+ 
              N'to filegroup FG'+CONVERT(nvarchar, @PartitionNumber) 
    exec(@ExecStr) 
    end 
fetch next from FilegroupsToCreate into @PartitionNumber,@Location 
end 
deallocate FilegroupsToCreate

4、创建分区函数

create partition function OneYearDateRangePFN(datetime) as 
range left for values('20020331 23:59:59.997', 
            '20020430 23:59:59.997', 
            '20020531 23:59:59.997')

5、创建分区架构

create partition scheme OneYearDateRangePScheme as 
partition OneYearDateRangePFN to(FG1,FG2,FG3,[PRIMARY])

6、创建表

create table Orders 
( 
    OrderID int not null, 
    EmployeeID int null, 
    VendorID int null, 
    TaxAmt money null, 
    Freight money null, 
    SubTotal money null, 
    Status tinyint not null, 
    RevisionNumber tinyint null, 
    ModifiedDate datetime null, 
    ShipMethodID tinyint null, 
    ShipDate datetime not null, 
    OrderDate datetime not null constraint OrdersRangeYearCK check(OrderDate>='20020301' and OrderDate<'20020601'), 
    TotalDue money null 
)on OneYearDateRangePScheme(OrderDate)

7、创建索引

alter table Orders add constraint OrdersPK primary 
key clustered(OrderDate, OrderID) on 
OneYearDateRangePScheme(OrderDate)

8、加载数据

insert Orders select 
    PurchaseOrderID, 
    EmployeeID, 
    VendorID, 
    TaxAmt, 
    Freight, 
    SubTotal, 
    Status, 
    RevisionNumber, 
    ModifiedDate, 
    ShipMethodID, 
    ShipDate, 
    OrderDate, 
    TotalDue 
from AdventureWorks.Purchasing.PurchaseOrderHeader where(OrderDate>='20020301' and OrderDate<'20020601')

二、滑动窗口方案

1、确定文件组

select ps.name as PSName,dds.destination_id as PartitionNumber,fg.name as FileGroupName 
from (((sys.tables as t inner join sys.indexes as i on (t.object_id=i.object_id)) 
    inner join sys.partition_schemes as ps on (i.data_space_id=ps.data_space_id)) 
    inner join sys.destination_data_spaces as dds on (ps.data_space_id=dds.partition_scheme_id)) 
    inner join sys.filegroups as fg on dds.data_space_id=fg.data_space_id 
where (t.name='Orders') and (i.index_id in (0,1)) 
    and dds.destination_id = $partition.OneYearDateRangePFN('20020301')

2、创建移入分段表

create table OrdersNew 
( 
    OrderID int not null, 
    EmployeeID int null, 
    VendorID int null, 
    TaxAmt money null, 
    Freight money null, 
    SubTotal money null, 
    Status tinyint not null, 
    RevisionNumber tinyint null, 
    ModifiedDate datetime null, 
    ShipMethodID tinyint null, 
    ShipDate datetime not null, 
    OrderDate datetime not null, 
    TotalDue money null 
) on FG1

3、为移入分段表建立约束

alter table OrdersNew with check add constraint OrdersNewRangeYearCK 
    check(OrderDate='20020601' and OrderDate='20020630 23:59:59.997')

4、为移入分段表建立索引

alter table OrdersNew add constraint OrdersNewPK 
    primary key clustered(OrderDate,OrderID) 
on FG1

5、为移入分段表加载数据

insert OrdersNew select 
    PurchaseOrderID, 
    EmployeeID, 
    VendorID, 
    TaxAmt, 
    Freight, 
    SubTotal, 
    Status, 
    RevisionNumber, 
    ModifiedDate, 
    ShipMethodID, 
    ShipDate, 
    OrderDate, 
    TotalDue 
from AdventureWorks.Purchasing.PurchaseOrderHeader where(OrderDate>='20020601' and OrderDate<'20020701')

6、创建移出分段表

create table OrdersOld 
( 
    OrderID int not null, 
    EmployeeID int null, 
    VendorID int null, 
    TaxAmt money null, 
    Freight money null, 
    SubTotal money null, 
    Status tinyint not null, 
    RevisionNumber tinyint null, 
    ModifiedDate datetime null, 
    ShipMethodID tinyint null, 
    ShipDate datetime not null, 
    OrderDate datetime not null, 
    TotalDue money null 
) on FG1

7、为移出分段表建立索引

alter table OrdersOld add constraint OrdersOldPK 
    primary key clustered(OrderDate,OrderID) 
on FG1

8、将旧数据移出分区表

alter table Orders switch partition 1 to OrdersOld

9、删除旧边界

alter partition function OneYearDateRangePFN() 
merge range('20020331 23:59:59.997')

10、设置下一个使用的分区

alter partition scheme OneYearDateRangePScheme next used FG1

11、添加新边界

alter partition function OneYearDateRangePFN() split range('20020630 23:59:59.997')

12、更改约束

alter table Orders add constraint OrdersRangeMax 
    check(OrderDate<'20020701')
 
alter table Orders add constraint OrdersRangeMin 
    check(OrderDate>='20020401')
 
alter table Orders drop constraint OrdersRangeYearCK

13、将新数据移入分段表

alter table OrdersNew switch to Orders partition 3

14、删除分段表

drop table OrdersNew 
drop table OrdersOld

三、验证

1、查看文件和文件组

exec sp_helpfile

2、查看分区和行数

select $partition.OneYearDateRangePFN(o.OrderDate) as 分区编号, 
    min(o.OrderDate) as 最小日期, 
    max(o.OrderDate) as 最大日期, 
    count(*) as 行数 
from Orders as o 
group by $partition.OneYearDateRangePFN(o.OrderDate) 
order by 分区编号
[sql server] SQL Server 2005中的分区表
永生天地
07-22 1964
SQL Server 2005中的分区表（一）：什么是分区表？为什么要用分区表？如何创建分区表？SQL Server 2005中的分区表（二）：如何添加、查询、修改分区表中的数据SQL Server 2005中的分区表（三）：将普通表转换成分区表 SQL Server 2005中的分区表（四）：删除（合并）一个分区SQL Server 2005中的分区表（五）：添加一个分区SQL Server 2005中的分区表（六）：将已分区表转换成普通表
T-SQL查询进阶--理解SQL SERVER中的分区表
weixin_34082177的博客
12-30 318
简介     分区表是在SQL SERVER2005之后的版本引入的特性。这个特性允许把逻辑上的一个表在物理上分为很多部分。而对于SQL SERVER2005之前版本，所谓的分区表仅仅是分布式视图,也就是多个表做union操作.     分区表在逻辑上是一个表，而物理上是多个表.这意味着从用户的角度来看，分区表和普通表是一样的。这个概念可以简单如下图所示：          而对于...
SQL Server 2005中的分区表（三）：将普通表转换成分区表
没有标题，只有问题
12-03 2万+
    在设计数据库时，经常没有考虑到表分区的问题，往往在数据表承重的负担越来越重时，才会考虑到分区方式，这时，就涉及到如何将普通表转换成分区表的问题了。    那么，如何将一个普通表转换成一个分区表呢？说到底，只要将该表创建一个聚集索引，并在聚集索引上使用分区方案即可。    不过，这回说起来简单，做起来就复杂了一点。还是接着上面的例子，我们先使用以下SQL语句将原有的Sale表删除
SQL SERVER 2005 分区表实际应用例子
SimbaDan的专栏
01-09 5197
SQL SERVER 2005 分区表实际应用例子    定义,原理网上讲得多了.在这就不费口舌,记录下创建过程.    一.  最基本,最重要的一步就是创建分区函数.创建分区函数首先要确定分区键--既按照哪字段来进行分区.在这个例子里,我用记录的时间来作为分区键,由于数据量的问题,最终决定每个月的数据放一个单独的分区.CREATE PARTITION FUNCTION FiveYearD
SQL Server 2005 中的分区表和索引原理
dearbinge的专栏
03-31 2673
<br />      摘要 ：SQL Server 2005 中基于表的分区功能为简化分区表的创建和维护过程提供了灵活性和更好的性能。追溯从逻辑分区表和手动分区表的功能到最新分区功能的发展历程，探索为什么、 何时以及如何使用 SQL Server 2005 设计、实现和维护分区表。为什么要进行分区？ <br />　　什么是分区？为什么要使用分区？简单的回答是：为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。通常，创建表是为了存储某种实体 （例如客户或销售）的信息，并且每个表只具有描
SQL Server 2005 分区表实践——滑动窗口方案
wengene的专栏
09-29 1433
<br />[作/译者]：鹏城万里    [日期]：2008-07-15    [来源]：本站原创    [查看]: 1494 <br />【鹏城万里】 发表于 www.sqlstudy.com<br />SQL Server 2005 分区表——滑动窗口方案实践：采用滑动窗口机制，把分区表的分区依次移入到另一个分区表。值得一提的是，本文示例涉及了 SQL 分区表的方方面面：建立分区函数（partition function）、分区方案（partition scheme）；分区表创建；分区函数拆分、合并；分
SQL server 2005 切换分区表
anjichan4261的博客
11-10 237
如转载，请注明出处：http://blog.csdn.net/robinson_0612/archive/2009/11/10/4794371.aspx SQL server 2005 切换分区表 在日常工作中经常需要用到分区表切换的问题，笔者在此列出几种常见的分区表切换的问题，供参考。 一、分区表的切换无外乎以下三种形式： 1.将一个分区中的数据切换成单个表。 ...
Sqlserver2005分区表实现步骤
北亮的专栏
06-21 5178
在SqlServer2005中新增了分区表的支持，对于一些大数据量的表，我们可以对它进行分区，以便提供更好的性能，下面是我的一些体会和详细的分区步骤了， 假设要操作的数据库名为db1,表名为tb1(id 自增int, insertTime DateTime)，表内保存了2006~2010年共5年的数据，要做5个分区：   0、分区键的选择 分区的关键是要选择好分区键，就是在插入数据时，新的
SQL Server 2005中的分区表（二）：如何添加、查询、修改分区表中的数据
没有标题，只有问题
12-03 2万+
    在创建完分区表后，可以向分区表中直接插入数据，而不用去管它这些数据放在哪个物理上的数据表中。接上篇文章，我们在创建好的分区表中插入几条数据："50" rows="15" name="code" class="c-sharp">insert Sale ([Name],[SaleTime]) values (张三,2009-1-1)insert Sale ([Name],[Sa
SQL Server 2005中的分区表（五）：添加一个分区
没有标题，只有问题
12-04 1万+
    所谓天下大事，分久必合，合久必分，对于分区表而言也一样。前面我们介绍过如何删除（合并）分区表中的一个分区，下面我们介绍一下如何为分区表添加一个分区。     为分区表添加一个分区，这种情况是时常会发生的。比如，最初在数据库设计时，只预计了存放3年的数据，可是到了第4天怎么办？这样的话，我们就可以为分区表添加一个分区，让它把新的数据放在新的分区里。再比如，最初设计时，一个分区
SQL Server 2005中的分区表（一）：什么是分区表？为什么要用分区表？如何创建分区表？
热门推荐
没有标题，只有问题
12-03 3万+
    如果你的数据库中某一个表中的数据满足以下几个条件，那么你就要考虑创建分区表了。     1、数据库中某个表中的数据很多。很多是什么概念？一万条？两万条？还是十万条、一百万条？这个，我觉得是仁者见仁、智者见智的问题。当然数据表中的数据多到查询时明显感觉到数据很慢了，那么，你就可以考虑使用分区表了。如果非要我说一个数值的话，我认为是100万条。    2、但是，数据多了并不是创建分
架构设计过去十年与未来十年
weixin_40213018的博客
10-21 754
架构十年 过去的十年，刘海锋与架构一同成长。他认为，架构领域经历了快速演进的十年。架构领域在整个互联网的技术栈是偏底层的，但过去十年架构领域的演进速度，一点都不亚于应用开发，甚至移动端开发的速度。比如容器，新型的数据库，新型的中间件，大规模集群的调度，以及系统跟算法的结合，在很多方面都出现了新的变化，甚至是出现了里程碑式的进步。 在具体的变化方面，刘海锋列举了三点，第一点是容器。把所有的东西都放在容器里，这是以前从没有过的。到今天，我们不光把应用的部署放在容器里，很多有状态的服务，包括缓存，..
易语言源码易语言超级表格自动完成源码
06-23
易语言源码易语言超级表格自动完成源码
【Python + 半导体】设备日志自动解析与异常提取工具
最新发布
06-23
本资源提供半导体设备日志自动解析与异常提取的完整解决方案，适用于设备维护、故障诊断、日志审计等场景。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值