SQL 2008 将已经存在数据的表进行分区

SQL2008 创建分区(手动/分区向导)

以上是手动 创建分区... 下面是使用sql2008 分区向导 自动创建(生成上面 手动写 sql 语句)

  上面图片请勾选： 将存储区中的所有非唯一索引和唯一索引与索引分区列对齐   (否则后面无法使用 切入和切出)

上面图片请勾选： 将存储区中的所有非唯一索引和唯一索引与索引分区列对齐   (否则后面无法使用 切入和切出)

//--------------------------------------------- 手动创建 sql----------------------------------------------

--1 【创建分区组】

--  SELECT *FROM sys.filegroups      --查看现有分区

--  SELECT * FROM sys.database_files --查看分区所在目录

 

--ALTER DATABASE PartitionTable

--ADD FILEGROUP FG02

--GO

--ALTER DATABASE PartitionTable

 

--    ADD FILE 

--    (

--       NAME = 'FG02', 

--        FILENAME = N'D:\PartitionTableTest\FG02.ndf', 

--        SIZE = 3MB, 

--        MAXSIZE = UNLIMITED, 

--        FILEGROWTH = 10%

        

--    ) TO FILEGROUP FG02

 

--GO

 

 

--2 【创建分区函数】（根据OrderDate 时间来分区）

 

CREATE  PARTITIONFUNCTIONFN_OrderYearPartition (DATETIME)ASRANGEFOR 

 

VALUES (N'2002-01-01T00:00:00',N'2003-01-01T00:00:00',N'2004-01-01T00:00:00',N'2005-01-01T00:00:00',N'2006-01-01T00:00:00',N'2007-01-01T00:00:00',N'2008-01-01T00:00:00',N'2009-01-01T00:00:00',N'2010-01-01T00:00:00',N'2011-01-01T00:00:00',N'2012-01-01T00:00:00',N'2013-01-01T00:00:00');

 

--3 【创建分区方案】

CREATEPARTITIONSCHEMEFR_OrderYearPartitionASPARTITIONFN_OrderYearPartitionTO ('FG02','FG03','FG04','FG05','FG06','FG07','FG08','FG09','FG10','FG11','FG12','FG13',[PRIMARY]);

 

-- SELECT * FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='T_OrderDetails'  --各个分区对应的数据量

 

--4 【然后把表链接到分区方案，这里使用OrderDetailsID 主键作为分区列。】

 

 

CREATECLUSTEREDINDEX[ClusteredIndex_on_FR_OrderYearPartition_634923009202347204]ON[dbo].[T_OrderDetails] 

(

    [OrderDate]

)WITH (SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF)ON[FR_OrderYearPartition]([OrderDate])

 

 

DROPINDEX[ClusteredIndex_on_FR_OrderYearPartition_634923009202347204]ON[dbo].[T_OrderDetails]WITH (ONLINE=OFF)

 

 

--======================================= 查询测试=================

SELECT*FROMsys.partitionsWHEREOBJECT_NAME(OBJECT_ID)='T_OrderDetails'  --各个分区对应的数据量

 

SELECT*FROMdbo.T_OrderDetailsWHERE$PARTITION.FN_OrderYearPartition(OrderDate)=1 

 

-- 143  

SELECT*FROMT_OrderDetailsWHERE  OrderDate='2012-09-04 11:03:23.580'--CONVERT(DATE,OrderDate)='2012-9-4'

 

--

SELECT*FROMdbo.T_OrderDetailsWHERE$PARTITION.FN_OrderYearPartition(OrderDate)=12 

ANDOrderDate='2012-09-04 11:03:23.580'

--AND CONVERT(DATE,OrderDate)='2012-9-4'

 

 

 

--【归档数据】

--备注：测试时候T_OrderDetails 和T_OrderDetailsHistory 都未建立索引(聚集/非聚集) ,实际是需要索引并且要建立分区索引(创建分区索引PK_T_OrderDetails 右键--属性--存储--选择分区方案-分区方案参数/表列) 注意:索引字段　必须是分区列字段（OrderDate  此处是非聚集不唯一）

[

 

在已分区的表上创建索引（分区索引）时，应该注意以下事项：
Ø 唯一索引
建立唯一索引（聚集或者非聚集）时，分区列必须出现在索引列中。此限制将使SQL Server只调查单个分区，并确保表中宠物的新键值。如果分区依据列不可能包含在唯一键中，则必须使用DML触发器，而不是强制实现唯一性。

Ø 非唯一索引
对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下SQL Server 将在聚集索引列中添加分区依据列。
对非唯一的非聚集索引进行分区时，默认情况下SQL Server 将分区依据列添加为索引的包含性列，以确保索引与基表对齐，若果索引中已经存在分区依据列，SQL Server 将不会像索引中添加分区依据列。

 

] 

 

 

  --假如现在是年年初，可以把记录归档到历史订单表HistroryOrderDetails 中

 

  --a: 创建一个与T_OrderDetails 结构一样的T_OrderDetailsHistrory 表

  --b: T_OrderDetailsHistrory 右键快速创建分区(使用现有的分区函数与分区方案)

  --bb: 如果存在索引请给索引进行分区(PK_T_OrderDetailsHistory 右键--属性--存储--选择分区方案-分区方案参数/表列)

  --c: 进行数据归档执行下面sql

  --ALTER TABLE T_OrderDetails SWITCH PARTITION 1 TO T_OrderDetailsHistory PARTITION 1  --(索引必须一致属性,此处是非聚集 不唯一)

  

   --SELECT * FROM dbo.T_OrderDetails WHERE $PARTITION.FN_OrderYearPartition(OrderDate)=1 --数据已经不存在

   --ALTER TABLE T_OrderDetailsHistory SWITCH PARTITION 1 TO T_OrderDetails PARTITION 1   --恢复

  

  

     --SELECT * FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='T_OrderDetailsHistory' 

     --SELECT * FROM dbo.T_OrderDetailsHistory WHERE $PARTITION.FN_OrderYearPartition(OrderDate)=1

 

--【添加分区】

-- 产生的数据需要添加分区, 具体操作如下

/*

1. 创建文件组FG14.ndf

 

ALTER DATABASE PartitionTable 

ADD FILEGROUP [FG14]

 

 

ALTER DATABASE PartitionTable ADD FILE 

( 

NAME = N'FG14', 

FILENAME = N'D:\MyProject\PartitionTableTest\FG14.ndf' , 

SIZE = 3072KB , 

FILEGROWTH = 1024KB ) TO FILEGROUP [FG14]

 

*/

--2. 修改分区方案和分区函数

 

ALTER  PARTITIONSCHEMEFR_OrderYearPartition  NEXTUSED[FG14]             --FG14 新的文件组

 

ALTERPARTITIONFUNCTION  FN_OrderYearPartition()SPLITRANGE('2014/01/01')--创建一个新分区

 

--3. 测试2014 年的一条数据(原先是被划分到的分区)

 

SELECT*FROMdbo.T_OrderDetailsWHERE$PARTITION.FN_OrderYearPartition(OrderDate)=14 --2014 的数据

 

 

 

--【删除分区】

 

 /*删除分区其实就是合并分区*/

 

 --1. 测试删除上面建的FG14 分区

 ALTERPARTITIONFUNCTION  FN_OrderYearPartition()MERGERANGE('2014/01/01')

 

 SELECT*FROMdbo.T_OrderDetailsWHERE$PARTITION.FN_OrderYearPartition(OrderDate)=13 --2014 的数据也在里

 

 --【查看元数据】

 SELECT*FROMsys.partition_functions   

 SELECT*FROMsys.partition_range_values

 SELECT*FROMsys.partition_schemes