2005 SQL 数据库分区表-优快云博客

本文介绍如何使用 SQL Server 对数据库进行分区和管理文件组。内容包括创建新的文件组、添加文件到指定文件组、创建分区函数及分区方案等操作。此外还介绍了如何通过修改分区函数来调整分区范围。

-- 增加文件组
Alter DataBase TEST ADD FILEGROUP [User01]
ALTER DATABASE TEST
   ADD FILE
(NAME = 'User01',
   FILENAME = 'D:\SqlData\UserFS01.NDF',
   SIZE = 5MB,
   FILEGROWTH = 5MB)
   TO FILEGROUP User01

Alter DataBase TEST ADD FILEGROUP [User02]
ALTER DATABASE TEST
   ADD FILE
(NAME = 'User02',
   FILENAME = 'D:\SqlData\UserFS02.NDF',
   SIZE = 5MB,
   FILEGROWTH = 5MB)
   TO FILEGROUP User02

-- 增加分区范围
CREATE PARTITION FUNCTION [UserRange]
(int) AS RANGE RIGHT FOR VALUES
(1000,1100)
go

-- 增加分区架构
CREATE PARTITION SCHEME [UserScheme]
AS PARTITION [UserRange]
TO ([User01], [User02],[PRIMARY])

-- 删除一个点
ALTER PARTITION FUNCTION
[UserRange]()
MERGE RANGE ('1100')

-- 增加一个点
ALTER PARTITION FUNCTION
[UserRange]()
SPLIT RANGE ('1200')
GO

-- 在最后增加一个文件组
ALTER PARTITION SCHEME [UserScheme124]
NEXT USED [User03]
GO

-- 查找一条记录在那个分区
select $partition.[UserRange](id),userid FROM usertable where userid = 1000120
SELECT *, $PARTITION.[Data Partition Range](ID) FROM MyTable