sql表分区

1、单表达多少条数据后需要分区呢?
   a.个人认为要似情况而定，有些常操作的表，分区反而带来麻烦，可以采用物理分表以及其它方法处理；
   b.对于一些日志、历史订单类的查询数据，500w左右即可享受分区带来的优越性；

   c.可以将分区映射到文件组，每个分区访问一个不同的物理磁盘驱动器，以便提高 I/O 性能

 

在设计数据库时，经常没有考虑到表分区的问题，往往在数据表承重的负担越来越重时，才会考虑到分区方式，这时，就涉及到如何将普通表转换成分区表的问题了。

  那么，如何将一个普通表转换成一个分区表 呢？说到底，只要将该表创建一个聚集索引，并在聚集索引上使用分区方案即可。

 

---1. 添加文件组及文件（一般用于独立服务器或有创建子数据库文件、 文件组权限的服务器，其它可跳过）
--根据需要可添加合适数量的分区及设置表分区文件所在磁盘
alter database yd_erp_db20140429 add filegroup [year_fg1]
alter database yd_erp_db20140429 add filegroup [year_fg2]
alter database yd_erp_db20140429 add filegroup [year_fg3]

alter database yd_erp_db20140429 add file
(name='year_fg1',filename='E:\DataBase\year_fg1.ndf',maxsize=UNLIMITED,filegrowth=10%) to filegroup year_fg1
alter database yd_erp_db20140429 add file
(name='year_fg2',filename='E:\DataBase\year_fg2.ndf',maxsize=UNLIMITED,filegrowth=10%) to filegroup year_fg2
alter database yd_erp_db20140429 add file
(name='year_fg3',filename='E:\DataBase\year_fg3.ndf',maxsize=UNLIMITED,filegrowth=10%) to filegroup year_fg3

 

--新建一个普通的数据表 

CREATE TABLE [dbo].[bs_BFlowDetail](
[Id] [int] NOT NULL,
[BFlowFileID] [nvarchar](50) NOT NULL,
[BFDDate] [datetime] NULL,
[InCstmId] [nvarchar](50) NULL,
[ItemId] [varchar](20) NULL,
[Batch] [nvarchar](50) NULL,
[Qty] [decimal](18, 2) NULL,
[YuanShiId] [nvarchar](50) NULL,
[Remarks] [nvarchar](200) NULL,
[IsExist] [bit] NULL,
[ClientName] [nvarchar](300) NULL,
[AppQty] [int] NULL,
[AppRemarks] [nvarchar](200) NULL,
[BFDMonth] [varchar](20) NOT NULL,
[price] [decimal](18, 4) NULL,
[level] [varchar](20) NULL,

CONSTRAINT [PK_Sale] PRIMARY KEY CLUSTERED  --创建主键  

  (  

     [Id] ASC  

   )  

)

---添加一些数据

 

--现在开始针bs_BFlowDetail分区

 

-----------------------首先创建分区函数------------------------

--对若分区函数存在则先drop掉 
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'years_partFunc') 
DROP PARTITION FUNCTION [years_partFunc] 
GO

---2. 创建分区函数
--分区函数用于定义分区的边界条件
create partition function years_partFunc(varchar(20))
as 
range left for values(
'2014-01',
'2015-01')
GO

 

----------------------其次，创建分区方案--------------------

--看分区方案是否存在，若存在先drop掉 
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'years_scheme') 
DROP PARTITION SCHEME years_scheme 
GO

-- 创建分区架构 
create partition scheme years_scheme
as partition years_partFunc to (year_fg1,year_fg2,year_fg3)

 

我们可以看出，bs_BFlowDetail表拥有一般普通表的特性——有主键，同时这个主键还是聚集索引。分区表是以某个字段为分区条件，所以，除了这个字段以外的其他字段，是不能创建聚集索引的。因此，要想将普通表转换成分区表，就必须要先删除聚集索引，然后再创建一个新的聚集索引，在该聚集索引中使用分区方案。

    可惜的是，在SQL Server中，如果一个字段既是主键又是聚集索引时，并不能仅仅删除聚集索引。因此，我们只能将整个主键删除，然后重新创建一个主键，只是在创建主键时，不将其设为聚集索引，如以下代码所示：

 

--删掉主键
alter table bs_BFlowDetail drop constraint PK_Sale

-------如果所要分区的字段即使主键也是分区字段，那么请使用如下代码：

--创建主键BFDMonth，并设为聚集索引
ALTER TABLE bs_BFlowDetail
ADD CONSTRAINT PK_Sale
PRIMARY KEY CLUSTERED (BFDMonth) on years_scheme(BFDMonth)
GO

-----如果所要分区的字段不是主键，那么请使用如下代码：

--创建主键id，但不设为聚集索引
ALTER TABLE bs_BFlowDetail
ADD CONSTRAINT PK_Sale
PRIMARY KEY NONCLUSTERED (Id) on [PRIMARY]
GO

--删除分区索引

DROP INDEX ix_fdate ON bs_BFlowDetail

--创建分区聚集索引，分区字段必须为聚集索引
create CLUSTERED index ix_fdate ON bs_BFlowDetail(BFDMonth) ON years_scheme(BFDMonth)

 

--统计所有分区表中的记录总数
select $PARTITION.years_partFunc(BFDMonth) as 分区编号,count(id) as 记录数
from bs_BFlowDetail group by $PARTITION.years_partFunc(BFDMonth)

-- 现在我们可以看一下我们刚才插入的行都分布在哪个Partition
SELECT *, $PARTITION.years_partFunc(BFDMonth) FROM bs_BFlowDetail 
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('bs_BFlowDetail')

--查询单个分区
select * from Sale where $PARTITION.partfunSale(SaleTime)=1

--更改增加分区函数
ALTER PARTITION FUNCTION years_partFunc() SPLIT RANGE ('2016-01')