SQL SERVER表分区

表分区技术，能够让用户把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能。

 

表分区主要包含三个步骤：

1、创建分区函数

CREATE PARTITION FUNCTION customer_partfunc(int) AS RANGE RIGHT FOR VALUES(250000,500000,750000)

2、创建分区架构

添加文件组

ALTER DATABASE [databasename] ADD FILEGROUP [FG1]
ALTER DATABASE [databasename] ADD FILEGROUP [FG3]
ALTER DATABASE [databasename] ADD FILEGROUP [FG4]
ALTER DATABASE [databasename] ADD FILEGROUP [FG2]

添加文件

ALTER DATABASE [databasename] ADD FILE ( NAME = N'FG1', FILENAME = N'D:\DeanDataFG1.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG1]

ALTER DATABASE [databasename] ADD FILE ( NAME = N'FG2', FILENAME = N'D:\DeanDataFG2.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG2]

ALTER DATABASE [databasename] ADD FILE ( NAME = N'FG3', FILENAME = N'D:\DeanDataFG3.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG3]

ALTER DATABASE [databasename] ADD FILE ( NAME = N'FG4', FILENAME = N'D:\DeanDataFG4.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG4]

ALTER DATABASE [databasename] remove FILE fg4  删除文件分区

CREATE PARTITION SCHEME customer_partscheme AS PARTITION customer_partfunc TO(fg1,fg2,fg3,fg4)

3、对表进行分区

CREATE TABLE customers(FirstName nvarchar(40),LastName nvarchar(40),CustomerNumber int) ON customer_partscheme(CustomerNumber)