数据库多文件组表分区

最新推荐文章于 2021-11-02 23:03:16 发布

darnellking

最新推荐文章于 2021-11-02 23:03:16 发布

阅读量748

点赞数

分类专栏： sql--SqlServer 文章标签：数据库分区文件组 sqlserver

本文链接：https://blog.csdn.net/darnellking/article/details/54600039

版权

sql--SqlServer 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1. 创建多文件，文件组。

1. 创建文件组

USE study;
ALTER DATABASE study ADD FILEGROUP [db1_fg1]; --创建一个文件组
ALTER DATABASE study ADD FILEGROUP [db1_fg2];
ALTER DATABASE study ADD FILEGROUP [db1_fg3];
ALTER DATABASE study ADD FILEGROUP [db1_fg4];

2.在各文件组下面指定数据库文件

ALTER DATABASE study ADD FILE (
    name = N'byoul',
    filename = N'D:\DevSoft\sqlser\MSSQL12.MSSQLSERVER\MSSQL\DATA\byoul.ndf',
    SIZE = 5MB,
    maxsize = 100mb,
    filegrowth = 5MB
) --size 为文件的初始大小，maxsize为文件的最大大小，filegrouwth自动增量大小
TO filegroup [db1_fg1];

ALTER DATABASE study ADD FILE (
    name = N'byou2',
    filename = N'D:\DevSoft\sqlser\MSSQL12.MSSQLSERVER\MSSQL\DATA\byou2.ndf',
    SIZE = 5MB,
    maxsize = 100mb,
    filegrowth = 5MB
) --size 为文件的初始大小，maxsize为文件的最大大小，filegrouwth自动增量大小
TO filegroup [db1_fg2];

ALTER DATABASE study ADD FILE (
    name = N'byou3',
    filename = N'D:\DevSoft\sqlser\MSSQL12.MSSQLSERVER\MSSQL\DATA\byou3.ndf',
    SIZE = 5MB,
    maxsize = 100mb,
    filegrowth = 5MB
) --size 为文件的初始大小，maxsize为文件的最大大小，filegrouwth自动增量大小
TO filegroup [db1_fg3];

ALTER DATABASE study ADD FILE (
    name = N'byou4',
    filename = N'D:\DevSoft\sqlser\MSSQL12.MSSQLSERVER\MSSQL\DATA\byou4.ndf',
    SIZE = 5MB,
    maxsize = 100mb,
    filegrowth = 5MB
) --size 为文件的初始大小，maxsize为文件的最大大小，filegrouwth自动增量大小
TO filegroup [db1_fg4];

2.创建分区函数和分区方案

1. 创建分区函数

CREATE Partition FUNCTION employeePF (INT) AS range LEFT FOR
VALUES
    (
        N'1000000',
        N'2000000',
        N'3000000'
    );

2.创建分区方案

CREATE PARTITION SCHEME [empScheme] AS PARTITION [employeePF] TO (
    [db1_fg1],
    [db1_fg2],
    [db1_fg3],
    [db1_fg4]
);

3. 对表进行分区

1. 删除原有主键索引

declare  @pkname varchar(1000)
SELECT @pkname= name
FROM
        sysobjects
WHERE
        xtype='pk'
AND object_name(parent_obj) ='Employee'
EXEC( 'alter table Employee drop constraint '+@pkname );

2. 创建新的主键聚集索引

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 [empScheme] ([EmployeeNo])