SQL Server表分区(一):基础知识和实现方式

1、什么是表分区？
SQL Server使用三种不同类型的文件存储数据，它们分别是.mdf、.ndf和.ldf。主要数据存储在
.mdf文件中，比如表，索引，存储过程等。.ndf文件也用于储存这些数据。.ldf文件用于存储操作日志。
表默认存储在.mdf文件中。更新表时SQL Server会对表锁。那么其它的操作必须等待正在更新操作完成。
如果一个表很大，那么无论是查询操作还是更新操作，性能都会很差。如果将表分别存储在物理上独立但逻辑上连续的
分区中，那么SQL Server可以大幅优化查询操作和更新操作的性能。

2、如何实现表分区？
1)确定分区策略，即划分数据的逻辑规则。比如将整形值小于100的数据放在一个分区上，将大于等于100的数据放在第二个
分区上，等等。
SQL Server中的使用分区函数来实现分区策略：

CREATE PARTITION FUNCTION PF_Order(DATETIME)
AS RANGE RIGHT FOR VALUES
('20090101','20100101','20110101')

2)确定分区存储架构，即划分数据的物理存储规则。最终分区可以存储在主文件组，即.mdf文件中，也可以存储在非主文件组，
即.ndf文件中。
SQL Server中的使用分区架构来实现分区存储架构：

CREATE PARTITION SCHEME PS_Order
AS PARTITION PF_Order
TO ([FG1],[FG2],[FG3],[FG4])

3）将表创建在分区存储架构上，并指定使用哪一列做为划分数据的依据。注意做为划分数据依据的列必须在聚集索引中
在更新数据时，SQL Server首先使用列的数据做为参数调用分区函数确定应该将该数据存储在哪个文件组（逻辑分区），
然后将数据写入文件组对应的物理文件中。

SQL Server中创建分区表:

CREATE TABLE dbo.[Order]
(
OrderID BIGINT IDENTITY(1,1),
OrderDate DATETIME NOT NULL,
CONSTRAINT PK_Order_OrderID_OrderDate PRIMARY KEY CLUSTERED(OrderID,OrderDate)
)
ON PS_Order(OrderDate)

 

下面是详细的示例代码：

--表分区测试代码
USE [master]
GO

--创建具有多个文件组的测试数据库
IF EXISTS(SELECT 1 FROM sys.databases WHERE [name]=N'TestTablePartitionDB')
BEGIN
    DROP DATABASE [TestTablePartitionDB]
END

CREATE DATABASE [TestTablePartitionDB]
ON PRIMARY
(
Name='PrimaryFG',
FileName='D:\Test\TablePartition\TestTablePartitionDB.mdf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG1]
(
Name='FG1',
FileName='D:\Test\TablePartition\FG1.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG2]
(
Name='FG2',
FileName='D:\Test\TablePartition\FG2.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG3]
(
Name='FG3',
FileName='D:\Test\TablePartition\FG3.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG4]
(
Name='FG4',
FileName='D:\Test\TablePartition\FG4.ndf',
Size=5,
FileGrowth=1
)

USE TestTablePartitionDB
GO

--创建分区函数
CREATE PARTITION FUNCTION PF_Order(DATETIME)
AS RANGE LEFT FOR VALUES
('20090101','20100101','20110101')

--创建分区架构
CREATE PARTITION SCHEME PS_Order
AS PARTITION PF_Order
TO([FG1],[FG2],[FG3],[FG4])

--创建分区表
CREATE TABLE dbo.[Order]
(
OrderID BIGINT IDENTITY(1,1),
OrderDate DATETIME NOT NULL,
CONSTRAINT PK_Order_OrderID_OrderDate PRIMARY KEY CLUSTERED(OrderID,OrderDate)
)
ON PS_Order(OrderDate)

--测试数据的逻辑分区是哪个
SELECT $PARTITION.PF_Order('20090101')

--测试分区表的分区情况
SELECT partition_number,rows
FROM sys.partitions
WHERE [object_id]=object_id(N'dbo.[Order]')

--Drop index MyTable_IXC on MyTable with (Move To [Data Partition Scheme] (ID) )