SQL Server 创建表分区

先准备测试表

CREATE TABLE [dbo].[Employee] (
    EmployeeNo INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeName NVARCHAR(50) NULL
);

插入一些数据

INSERT Employee DEFAULT VALUES;
GO 10000

UPDATE Employee set EmployeeName = Employee  + CONVERT(VARCHAR, EmployeeNo) 

创建文件组

创建文件(为了性能,可以设定文件Path分布在不同的磁盘分区上)

创建分区准备

选择分区列

创建分区函数

创建分区框架

定义边界值,分区,因为有5个边界值,所以需6个分区

产生的脚本文件(换句话说上面的步骤等同于下面的语句)

USE [TestingDB]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [EmpFunction](int) AS RANGE RIGHT FOR VALUES (N2000, N4000, N6000, N8000, N10000)


CREATE PARTITION SCHEME [FunScheme] AS PARTITION [EmpFunction] TO ([FileGroup1], [FileGroup2], [FileGroup3], [FileGroup4], [FileGroup5], [PRIMARY])


ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [PK__Employee__7AD0F1B633D4B598]


ALTER TABLE [dbo].[Employee] ADD PRIMARY KEY CLUSTERED 
(
    [EmployeeNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FunScheme]([EmployeeNo])




COMMIT TRANSACTION

执行上面的SQL语句

其中语句ON [FunScheme]([EmployeeNo])是关键,表明了表Employee依赖分区框架FunScheme来进行分区,分区的列为EmployeeNo

而分区框架有依赖于分区函数,即分区表依赖分区框架,分区框架又依赖于分区函数

查看表分区结果

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。