关于SQLSERVER2005的分区

[hmlhmlb]

如题，一天一个分区，92天循环（3个月），第94天存储在第一个分区上。。。这个分区函数怎么写？

[dobear_0922]

看看，关注一下

[dobear_0922]

楼主可以在表里加一个字段，存储(插入日期是本年的第几天+1)%92，然后按这个字段分区

--计算某个日期是本年的第几天
select datepart(dy, '2008-09-22')

/*
-----------
266

(1 row(s) affected)
*/

[yangjiexi]

期待一下,学习!

[dawugui]

94个分区?

[dawugui]

SQL Server 2005中处理表分区问题

作者：Baya Pavliashvili 2007-06-18

数据库性能调优是每一个优秀SQL Server管理员最终的责任。虽然保证数据的安全和可用性是我们的最高的目标，但是假如数据库应用程序无法满足用户的要求，那么DBA们会因为性能低下的设计和实现而受到指责。SQL Server 2005在数据库性能方面得到了很多提高，尤其是表分区的技术。如果你还没不了解表分区的特征，那么请你花点时间读这篇文章。

表分区的概念不是一个新的概念；只要你当过一段时间的SQL Server DBA，那么你可能已经对一些频繁访问的表进行过归档，当这个表中的历史数据变的不再经常被访问的时候。比如，假设你有一个打印时间报表的应用，你的报告很少会查询1995年的数据，因为绝大部分的预算规划会基于最近几年的数据。

在SQL Server的早期版本中，你可以创建多个表。每一个表都具有相同的列结构，用来保存不同年份的数据。这样，当存在着对历史数据访问的必要的时候，你可以创建一个视图来对这些表进行查询处理。将数据保存在多个表中是很方便的，因为相对于查询时扫描整个大表，扫描小表会更快。但是这种好处只有在你预先知道哪些时间段的数据会被访问。同时，一旦数据过期，你还需要创建新表并且转移新产生的历史数据。

SQL Server 7和SQL Server 2000支持分布式分区视图(distributed partitioned views，又称为物化视图，materialized views)。分布式分区视图由分布于多台服务器上的、具有相同表结构的表构成，而且你还需要为每一个服务器增加链接服务器定义(linked server definitions)，最后在其中一台服务器上创建一个视图将每台服务器上返回的数据合并起来。这里的设计思想是数据库引擎可以利用多台服务器的处理能力来满足查询。

但是，分布式分区视图(DPV)受到很多限制，你可以在SQL Server的在线帮助文档中阅读到。虽然DPV在一些情况下能够提供性能上的提高，但是这种技术不能被广泛的应用。已经被证明它们不能满足逐步增长的企业级应用的要求。何况，DPV的实现是一个费力的过程，需要DBA进行很多工作。

SQL Server 2005开始支持表分区，这种技术允许所有的表分区都保存在同一台服务器上。每一个表分区都和在某个文件组(filegroup)中的单个文件关联。同样的一个文件/文件组可以容纳多个分区表。

在这种设计架构下，数据库引擎能够判定查询过程中应该访问哪个分区，而不用扫描整个表。如果查询需要的数据行分散在多个分区中，SQL Server使用多个处理器对多个分区进行并行查询。你可以为在创建表的时候就定义分区的索引。 对小索引的搜索或者扫描要比扫描整个表或者一张大表上的索引要快很多。因此，当对大表进行查询，表分区可以产生相当大的性能提升。

现在让我们通过一个简单的例子来了解表分区是如何发挥作用的。在这篇文章中，我不想深入到分区的语法细节当中，这些你可以在SQL Server的在线帮助文档中找到。下面的例子基于存储着一个时间报表系统的数据的数据仓库。除了默认的文件组，我另外创建了7个文件组，每一个文件组仅包含一个文件，这个文件将存储由分区函数定义的一部分数据。

为了测试表分区的性能提升，我向这个分区表中插入了一千五百万行，同时向另外一个具有相同表结构、但是没有进行分区的表插入了同样的数据。对分区表执行的INSERT语句运行的更快一些。甚至在我的内存不到1G的笔记本电脑上，对分区表的INSERT语句比不分区的表的INSERT语句要快上三倍。当然，查询的执行时间依据硬件资源的差异而所有变化，但是你还是能够在你的环境中感到不同程度的提升。

我将检查更深入了一步，通过分别检查同一条返回所有行的、简单SELECT语句在分区表和非分区表上的执行计划，返回的数据范围通过WHERE语句来指定。同一条语句在这两个不同的表上有不同的执行计划。对于分区表的查询显示出一个嵌套的循环和索引的扫描。从本质上来说，SQL Server将两个分区视为独立的表，因此使用一个嵌套循环将它们连接起来。对非分区的表的同一个查询则使用索引扫描来返回同样的列。当你使用同样的分区策略创建多个表，同时在查询中连接这些表，那么性能上的提升会更加明显。

你可以使用下面的查询来了解每一个分区中的行的个数：

SELECT $PARTITION.TimeEntryDateRangePFN(time_entry_date) AS Partition,COUNT(*) AS [COUNT] FROM fact_time_entry GROUP BY $PARTITION.TimeEntryDateRangePFN(time_entry_date) ORDER BY Partition
表分区对交易环境和数据仓库环境来说，都是一个重要的特征。数据仓库用户最主要的抱怨是移动事实表(fact table)会花费太多时间。当装载数据到事实表的时候，用户查询(立方体处理查询)的性能会明显下降，甚至是完全无法成功。因此，装载大量的数据到事实表的时候常常需要停机。如果使用表分区，就不再出现这样的情况——确切的讲，你一眨眼的工夫就可以移动事实表。为了演示这是如何生效的，我使用上面例子中相同的分区函数和表结构来创建一个新的表，这个表叫做fact_time_entry2。表的主键从五千万开始，这样fact_time_entry2就不会包含表fact_time_entry中已经有的数据。

现在我把2007年的数据移动到这张fact_time_entry2中。同时让我们假设fact_time_entry表中包含着2007年之前的数据。在fact_time_entry2表完成数据的转移，我执行下面的语句： ALTER TABLE fact_time_entry2 SWITCH PARTITION 8 TO fact_time_entry PARTITION 8
这条语句将编号为8的分区，这个分区恰好包含着2007年的数据，从fact_time_entry2移动到了fact_time_entry表中，在我的笔记本电脑上，这个过程只花费了3毫秒。在这短短的3毫秒中，我的事实表就增加了五百万条记录!的确，我需要在交换分区之前，将数据移动到中间表，但是我的用户不需要担心——事实表随时都可以查询!在这幕后，实际上没有数据移动——只是两张表的元数据发生了变化。

我可以使用类似的查询删除事实表中不在需要的数据。例如，假设我们决定我们不再关心2004年的记录。下面的语句可以将这些记录转移到我们创建的工作表中： ALTER TABLE fact_time_entry SWITCH PARTITION 2 TO fact_time_entry2 PARTITION 2
这样的语句依旧在毫秒级内完成了。现在，我可以删除fact_time_entry2或者将它移到其他的服务器上。我的事实表不会包含2004年的任何记录。这个分区还是需要在目的表中存在，而且它必须是空的。你不能将分区转移到一个包含重复数据的表中。源表和目的表的分区必须一致，同时被转移的数据必须在同一个文件组中。即使受到这么多的限制，转换分区和无需停机就可以移动数据表的功能必将让数据仓库的实现变的前所未有的轻松。

[dobear_0922]

create table tb1
(
	dy int  primary key
	, col1 int
	--, ...
	,check(dy=1)
)
create table tb2
(
	dy int primary key
	, col1 int
	--, ...
	,check(dy=2)
)
--...

go
create view tb as
select * from tb1
union all select * from tb2
--union all select * from tb3
--...

go
insert tb select datepart(dy, '2008-01-01'),1
union all select datepart(dy, '2008-01-02'),2
--union all select datepart(dy, '2008-01-03'),3

select * from tb1
/*
dy          col1
----------- -----------
1           1

(1 row(s) affected)
*/

select * from tb2
/*
dy          col1
----------- -----------
2           2

(1 row(s) affected)
*/

drop view tb
drop table tb1,tb2

[dobear_0922]

create table tb1
(
    dy int  primary key
    , col1 int
    --, ...
    ,check(dy=1)
)
create table tb2
(
    dy int primary key
    , col1 int
    --, ...
    ,check(dy=2)
)
--...

go
create view tb as
select * from tb1
union all select * from tb2
--union all select * from tb3
--...

go
insert tb select datepart(dy, '2008-01-01'),1
union all select datepart(dy, '2008-01-02'),2
--union all select datepart(dy, '2008-01-03'),3

--查看tb1中的数据
select * from tb1
/*
dy          col1
----------- -----------
1           1

(1 row(s) affected)
*/

--查看tb2中的数据
select * from tb2
/*
dy          col1
----------- -----------
2           2

(1 row(s) affected)
*/

--从视图中查
select * from tb where dy=datepart(dy, '2008-01-02')
/*
dy          col1
----------- -----------
2           2

(1 row(s) affected)
*/

drop view tb
drop table tb1,tb2

[dobear_0922]

修改下插入方式，，，

create table tb1
(
    dy int  primary key
    , col1 int
    --, ...
    ,check(dy=1)
)
create table tb2
(
    dy int primary key
    , col1 int
    --, ...
    ,check(dy=2)
)
--...

go
create view tb as
select * from tb1
union all select * from tb2
--union all select * from tb3
--...

go
insert tb select (datepart(dy, '2008-01-01')-1)%92+1,1
union all select (datepart(dy, '2008-01-02')-1)%92+1,2
--union all select datepart(dy, '2008-01-03'),3

--查看tb1中的数据
select * from tb1
/*
dy          col1
----------- -----------
1           1

(1 row(s) affected)
*/

--查看tb2中的数据
select * from tb2
/*
dy          col1
----------- -----------
2           2

(1 row(s) affected)
*/

--从视图中查
select * from tb where dy=(datepart(dy, '2008-01-02')-1)%92+1
/*
dy          col1
----------- -----------
2           2

(1 row(s) affected)
*/

drop view tb
drop table tb1,tb2

[kk19840210]

[code=SQL]
1.先在表里面添加一个分区列 该列为日期计算出来的 计算列 datediff(day,'1900-01-01',时间)%92 来划分该记录保存的分区
2. 创建分区函数
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 2, 3,4……91) ;
3.创建分区架构
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg……test92fg)
4. 创建分区表
CREATE TABLE PartitionTable (col1 int, col2 char(10),fq_flag int as datediff(day,'1900-01-01',时间)%92)
ON myRangePS1 (fq_flag) ;
/code]

[kk19840210]

熊兄 好像用的是分区视图

[dobear_0922]

熊兄 好像用的是分区视图

-------------
我服务器版本不支持你那样的分区，只能用分区视图，不过原理是一样的，，，

[hmlhmlb]

呵呵。。为啥SQLSERVER不适合呢？？主要是因为一个分区不能用200w的数据量，如果这样，我干脆分表，然后再分区得了。。希望给个理由吧，简单的说明一下。看来那个200W的数据是不是有这个限制，我需要自己测试一下啦。。。

[rouqu]

看看

[hmlhmlb]

我也是刚刚接触SQLSERVER 2005,以前都是用2000。我准备采用大家所说的另外增加一个列的方法去做分区健。。那么在CSDN上的一个blog看到SqlServer 2005每个分区的数据最好不要超过200W，，，，不知道有哪位仁兄做过这方面的测试，是否真的如此。。希望各位给予解答。。。
===200W数据量限制的 BLOG地址：http://www.cnblogs.com/yizhu2000/archive/2007/12/13/992901.html

=== 微软msdn关于2005建立方案   http://msdn2.microsoft.com/zh-cn/library/ms181036.aspx  

[hmlhmlb]

因为一天这张表的数据量可能有5亿，或者更多。。。

[中国风]

2005分区表，好用。。。

select cast('2008-01-01' as datetime)+92+92+92+92

00:00:00.000



Create PARTITION FUNCTION F_PARTITION (datetime)
AS RANGE RIGHT FOR VALUES
('2008-01-01', '2008-04-02', '2008-07-03',
'2008-10-03', '2009-01-03')

go
Create PARTITION SCHEME S_PARTITION
as PARTITION F_PARTITION
ALL to ([PRIMARY])

go
CREATE TABLE dbo.Partition_Table(
	ID int identity(1,1),
	Date datetime not null
) ON S_PARTITION (Data) 
go
---插入数据
insert Partition_Table(Date)
select '2008-01-01' union all
select '2008-04-02' union all
select '2008-07-03' union all
select '2008-10-03' union all
select '2009-01-03'

------------查看分区表存储：


select 
	convert(varchar(16), ps.name) as partition_scheme,
	p.partition_number,
	convert(varchar(10), ds2.name) as filegroup,
	convert(varchar(19), isnull(v.value, ‘’), 120) as range_boundary,
	str(p.rows, 9) as rows
from 
	sys.indexes i
join 
	sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join 
	sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id
join 
	sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id

[dobear_0922]

因为一天这张表的数据量可能有5亿，或者更多。。。

------------
建议换Oracle吧，，，SQL不适合处理这样的大数据。

[-狙击手-]

看看

[zjcxc]

设置好分区列的算法就行了吧? 与分区设置没有关系

你只要取一个时间为基准点, 然后定义一个计算列做为分区列, 计算列使用公式: DATEDIFF(Day, 基本日期, 日期列) % 92
就行了

(分区函数的分区边界值就是 0- 92)

[kelph]

分区表的确好用
数据量过大，肯定要在多个磁盘上建立多个文件组，然后在创建分区方案时不同分区指向不同文件组。

有强大的硬件匹配，5亿数据绝对没有问题。