34,590
社区成员
发帖
与我相关
我的任务
分享
/*
分区表实验结果:--SQL2005
一:先建分区函数;分区方案之后,将表建立在分区方案上,(暂未找到先建表,后修改普通表为分区表的方案,应该可以,需要验证)。
二:如果需要删除 分区函数,分区方案,必须先删除建立在分区方案上的表,然后删除分区方案,最后删除分区函数。
三:分区函数,和分区方案建立后,允许修改,但不是传统意义上的修改,
a.分区函数修改:只允许对原定义的函数分区,进行合并和拆分(继续添加)
b.分区方案:只允许继续添加新的已存在的文件组
C.添加新的分区拆分方法(修改分区函数),必须使分区方案中的分区文件组数>=分区函数的段
四:数据还原,分区还原 不可以使用临时表,表变量
*/
-- 创建分区函数
IF EXISTS (SELECT * FROM sys.partition_functions
WHERE name = 'PF_Orders_OrderDateRange')
DROP PARTITION FUNCTION PF_Orders_OrderDateRange;
GO
create partition function PF_Orders_OrderDateRange(datetime)
as
range right for values (
'2006-01-01',
'2007-01-01',
'2008-01-01'
)
go
/*
ALTER PARTITION FUNCTION PF_Orders_OrderDateRange()
SPLIT RANGE ('2009-01-01')
*/
-- 创建分区方案
IF EXISTS (SELECT * FROM sys.partition_schemes
WHERE name = 'PS_Orders')
DROP PARTITION scheme PS_Orders;
GO
create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary], [primary], [primary])
go
/*
ALTER PARTITION SCHEME PS_Orders
NEXT USED [primary]
*/
-- 创建分区表
IF EXISTS (SELECT * FROM sys.objects
WHERE name = 'Orders' and type = 'U')
DROP TABLE Orders
GO
create table dbo.Orders
(
OrderID int not null,
OrderDate datetime not null
)
on PS_Orders(OrderDate)
go
-- 创建聚集分区索引
IF EXISTS (SELECT * FROM sys.indexes
WHERE name = 'IXC_Orders_OrderDate' )
DROP index IXC_Orders_OrderDate on Orders.OrderDate
GO
create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
go
-- 为分区表设置主键
alter table dbo.Orders add constraint PK_Orders
primary key (OrderID, OrderDate)
go
-- 查看分区表每个分区的数据分布情况
select partition = $partition.PF_Orders_OrderDateRange(OrderDate)
,rows = count(*)
,minval = min(OrderDate)
,maxval = max(OrderDate)
from dbo.Orders
group by $partition.PF_Orders_OrderDateRange(OrderDate)
order by partition
go
truncate table Orders
-- 导入数据到分区表
DECLARE @ST DATETIME
DECLARE @ET DATETIME
DECLARE @T DATETIME
declare @i int
SET @ST = '2005-10-01'
SET @ET = '2008-08-01' --CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE()))
set @i = 1
SET @T = @ST
WHILE(@T<=@ET)
BEGIN
INSERT INTO Orders(OrderID,OrderDate)
--PRINT CONVERT(VARCHAR(19),@T,120)
SELECT @i,@T
SET @T = DATEADD(dd,1,@T)
set @i = @i + 1
END
--select * from Orders
--if object_id('tempdb..#Orders_2006') is not null
--begin
-- drop table #Orders_2006
--end
drop table Orders_2006
create table Orders_2006
(
OrderID int not null,
OrderDate datetime not null
) on [primary]
go
create clustered index IXC_Orders2006_OrderDate on dbo.Orders_2006(OrderDate)
go
alter table dbo.Orders_2006 add constraint PK_Orders_2006
primary key nonclustered (OrderID, OrderDate)
go
alter table dbo.Orders_2006 add constraint CK_Orders2006_OrderDate
check (OrderDate>='2006-01-01' and OrderDate<'2007-01-01')
--截取数据
alter table dbo.Orders switch partition 2 to dbo.Orders_2006
select * from Orders_2006
--还原数据
alter table dbo.Orders_2006 switch to dbo.Orders partition 2
/*为分区创建存储文件*****************************************************************/
ALTER DATABASE Test ADD FILEGROUP RegMailFile2007
ALTER DATABASE Test ADD FILEGROUP RegMailFile2008
ALTER DATABASE Test ADD FILEGROUP RegMailFile2009
--为文件组设置存储文件
ALTER DATABASE Test
ADD FILE (NAME = 'RegMailFile2007', FILENAME = 'D:\DBData\RegMailFile2007.NDF')
TO FILEGROUP RegMail2007
ALTER DATABASE Test
ADD FILE (NAME = 'RegMailFile2008', FILENAME = 'D:\DBData\RegMailFile2008.NDF')
TO FILEGROUP RegMail2008
ALTER DATABASE Test
ADD FILE (NAME = 'RegMailFile2009', FILENAME = 'D:\DBData\RegMailFile2009.NDF')
TO FILEGROUP RegMail2009