数据库分区表 二

3）创建分区函数

创建一个分区函数，创建分区函数的目的是告诉SQL Server以什么方式对分区表进行分区。这一步必须要什么SQL脚本来完成。以上面的例子，我们要将销售记录表按时间分成15个小表。划分的时间为：

第1个小表：2011-01-01以前的数据（不包含2010-01-01）。

    第2个小表：2011-01-01（包含2011-01-01）到2010-01-01之间的数据。

    第3个小表：2011-03-01（包含2011-03-01）到2011-05-01之间的数据。

第4个小表：2011-05-01（包含2011-05-01）到2011-07-01之间的数据。

第5个小表：……

第6个小表：……

 

--创建分区函数

CREATE PARTITION FUNCTION partfun_CX (datetime) 

AS RANGE RIGHT FOR VALUES ('20110101','20110301','20110501','20110701','20110901','20111101','20120101','20120301','20120501','20120701','20120901','20121101','20130101','20130301','20130501','20130701')

 

1、CREATE PARTITION FUNCTION意思是创建一个分区函数。

    2、partfun_CX为分区函数名称。

    3、AS RANGE RIGHT为设置分区范围的方式为Right，也就是右置方式。

4、FOR VALUES ('20110101','20110301','20110501','20130101',……)为按这些个值来分区，Values中的值就是分区的条件

 

（4）创建分区方案

分区方案的作用是将分区函数生成的分区映射到文件组中去。分区函数的作用是告诉SQL Server，如何将数据进行分区，而分区方案的作用则是告诉SQL Server将已分区的数据放在哪个文件组中。

--.创建分区方案

CREATE PARTITION SCHEME partsch_CX 

AS PARTITION partfun_CX 

TO ( 

    CX2010, 

    CX2011, CX2011, CX2011, CX2011, CX2011,CX2011,

   CX2012, CX2012, CX2012, CX2012, CX2012,CX2012,

   CX2013, CX2013, CX2013, CX2013)

1、CREATE PARTITION SCHEME意思是创建一个分区方案。

    2、partsch_CX为分区方案名称。

    3、AS PARTITION partfun_CX说明该分区方案所使用的数据划分条件（也就是所使用的分区函数）为partfun_CX。

    4、TO后面的内容是指partfun_CX分区函数划分出来的数据对应存放的文件组。

    到此为止，分区函数和分区方案就创建完毕了。创建后的分区函数和分区方案在数据库的“存储”中可以看到

 

（5）创建分区表

       创建分区表，创建方式和创建普遍表类似，如下所示：

       CREATE TABLE t_partition3( 

    ptId int IDENTITY(1,1) NOT NULL primary key NONCLUSTERED, 

    ptName varchar(16) NOT NULL, 

    sellTime[datetime] NOT NULL 

) ON partsch_CX(sellTime)

如果你按照上面的代码来实现的话出出现下图所示的错误代码提示：

消息1908，级别16，状态1，第1 行

列'sellTime' 是索引'PK__t_partition3__671F4F74' 的分区依据列。唯一索引的分区依据列必须是索引键的子集。

消息1750，级别16，状态0，第1 行

无法创建约束。请参阅前面的错误消息。

     这里是不能创建除分区表中除分区字段以外的其它字段为聚集索引，因为聚集索引是在物理上顺序存储的，而分区表是将数据分别存储在不同的表中，这两个概念是冲突的。如果我们创建了其它字段的聚集索引，那么就会按照其它字段在物理上顺序存储，而我们的分区表是根据分区字段进行物理上的顺序存储的。

 

我们的数据库已经投入使用一段时间了，但是当时没有创建创建分区表，现在我们需要做的是将普通表转换成分区表，但是并不能影响我们数据库里面的数据，那么我们应该如何做呢？只需在该表上创建一个聚集索引，并在该聚集索引中使用分区方案即可。

     说的很简单，但是在实现实现可就没有那么容易了，因为你的数据库中存在主键，外键等约束关系，那么我们在将普通表转换成分区表时，首先就需要解决这些问题。

     我们知道分区表时某个字段为分区条件的，除了这个字段之外的其他字段是不能创建聚集索引的，所以我们将普通表转换成分区表时，必须要删除聚集索引，然后再重新创建一个新的聚集索引，在该聚集索引中使用分区方案。

     但是我们需要修改的t_sellLog表中的orderId既是主键又是聚集索引，而且还是其它表的外键。因此，我们只能先删除外键关联，再删除主键，然后重新创建orderId为主键，但是设置为非聚集索引，然后将我们的sellTime字段设置为聚集索引，最后添加上我们的外键约束，至此普通表转换成分区表的工作结束,代码如下：

     --查看外键约束

use CX_Partiton_Scheme

exec sp_helpconstraint t_SellLog

--删除外键约束

alter table t_sellLog drop constraint FK_t_SellLog_t_User

--删掉主键

ALTER TABLE t_SellLog DROP constraint PK_t_SellLog 

--创建主键，但不设为聚集索引

ALTER TABLE t_SellLog ADD CONSTRAINT PK_t_SellLog PRIMARY KEY NONCLUSTERED ( 

    orderId ASC

)

 ON [PRIMARY]

 --创建一个新的聚集索引，在该聚集索引中使用分区方案

CREATE CLUSTERED INDEX CT_SellLog ON t_SellLog(sellTime) 

ON partsch_CX([sellTime])

 --添加删除掉的外键约束（具体自己根据实际情况自己实现）

 

     转换成功之后，我们可以通过下面代码查看每个分区表中的记录数：

   --统计所有分区表中的记录总数  

select $PARTITION.partfun_CX([sellTime]) as 分区编号,count(orderId) as 记录数from t_SellLog group by $PARTITION.partfun_CX([sellTime])

     我们还可以通过下面的代码，查看数据库库中的数据在哪个分区中：

   --查看数据库表中的数据在哪个分区中

select $PARTITION.partfun_CX('2010-10-1')  --查询年月日的数据在哪个分区中

select $PARTITION.partfun_CX('2011-01-1')  --查询年月日的数据在哪个分区中 如果你想比较一下我们使用分区方案之后和之前程序有多少效率提高，我们可以通过下面的语句来看看一下脚本的执行时间就OK了，我经过测试的数据是快了0.017秒，一方面由于我们的测试数据量比较小，另一方面我的机器配置还是蛮不错的。

     --查看SQL脚本的执行时间

   select getDate()

   select * from t_sellLog

   select getDate()

XX餐饮店数据库升级后的分区脚本  

--1.创建文件组

ALTER DATABASE YHXT ADD FILEGROUP SLFG2010

ALTER DATABASE YHXT ADD FILEGROUP SLFG2011

ALTER DATABASE YHXT ADD FILEGROUP SLFG2012

ALTER DATABASE YHXT ADD FILEGROUP SLFG2013

--2.创建文件

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog2010', FILENAME = N'D:\SellLog2010.ndf', SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2010

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201102', FILENAME = N'D:\program files\SellLog201102.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2011

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201104', FILENAME = N'D:\program files\SellLog201104.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2011

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201106', FILENAME = N'D:\program files\SellLog201106.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2011

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201108', FILENAME = N'D:\program files\SellLog201108.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2011

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201110', FILENAME = N'D:\program files\SellLog201110.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2011

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201112', FILENAME = N'D:\program files\SellLog201112.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2011

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201202', FILENAME = N'D:\program files\SellLog201202.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2012

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201204', FILENAME = N'D:\program files\SellLog201204.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2012

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201206', FILENAME = N'D:\program files\SellLog201206.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2012

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201208', FILENAME = N'D:\program files\SellLog201208.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2012

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201210', FILENAME = N'D:\program files\SellLog201210.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2012

ALTER DATABASE YHXT ADD FILE ( NAME = N'SellLog201212', FILENAME = N'D:\program files\SellLog201212.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP SLFG2012

 

 

--3.创建分区函数

CREATE PARTITION FUNCTION partfun_SL (datetime)  

AS RANGE RIGHT FOR VALUES ('20110101','20110301','20110501','20110701','20110901','20111101','20120101','20120301','20120501','20120701','20120901','20121101','20130101') 

 

--4.创建分区方案

CREATE PARTITION SCHEME partsch_YH  

AS PARTITION partfun_SL  

TO (  

    SLFG2010,  

    SLFG2011, SLFG2011, SLFG2011, SLFG2011, SLFG2011,SLFG2011,

SLFG2012, SLFG2012, SLFG2012, SLFG2012, SLFG2012,SLFG2012,SLFG2013)

 

--5.对销售记录表使用分区方案

--删除外键约束

alter table t_sellLogDetail drop constraint FK_t_SellLogDetail_t_SellLog 

 

--删掉主键  

ALTER TABLE t_SellLog DROP constraint PK_t_SellLog  

--创建主键，但不设为聚集索引  

ALTER TABLE t_SellLog ADD CONSTRAINT PK_t_SellLog PRIMARY KEY NONCLUSTERED (  

    orderId ASC 

) 

 ON [PRIMARY]

 

--创建一个新的聚集索引，在该聚集索引中使用分区方案  

CREATE CLUSTERED INDEX CT_SellLog ON t_SellLog(sellTime)  

ON partsch_YH([sellTime]) 

 

--添加外键

ALTER table t_sellLogDetail

add CONSTRAINT FK_t_SellLogDetail_t_SellLog foreign key(orderId) references t_sellLog(orderId) on delete cascade on update cascade

 

以上脚本在159803条数据中经测试，快了6秒，很有成就感。