SQL Server 创建表分区.docx

SQL Server 创建表分区原文转自：/panchunting/p/SQLServer_TablePartition.html先准备测试表CREATE TABLE [dbo].[Employee] ( EmployeeNo INT IDENTITY(1,1) PRIMARY KEY, EmployeeName NVARCHAR(50) NULL);插入一些数据INSERT Employee DEFAULT VALUES;GO 10000UPDATE Employee set EmployeeName = Employee + CONVERT(VARCHAR, EmployeeNo) 创建文件组创建文件（为了性能，可以设定文件Path分布在不同的磁盘分区上）创建分区准备选择分区列创建分区函数创建分区框架定义边界值，分区，因为有5个边界值，所以需6个分区产生的脚本文件（换句话说上面的步骤等同于下面的语句）USE [TestingDB]GOBEGIN TRANSACTIONCREATE 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而分区框架有依赖于分区函数，即分区表依赖分区框架，分区框架又依赖于分区函数查看表分区结果作者：/舍长出处：//本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利.