Перемещение таблицы в другую файловую группу

Tsql теория > Перемещение таблицы в другую файловую группу
27.03.2019 10:35:50



Статья:

В SQL Server 2005 в ALTER TABLE было добавлено новое предложение 'MOVE TO', позволяющее переместить таблицу в другую файловую группу. Предложение MOVE TO используется наряду с предложением DROP CONSTRAINT в синтаксисе команды ALTER TABLE. Когда удаляется ограничение первичного ключа или ограничение, которое создавало кластерный индекс, строки данных листового уровня кластерного индекса размещаются в некластеризованной таблице. В SQL Server 2005, когда удаляется кластерный индекс (путем удаления ограничения, которое создавало кластерный индекс), можно переместить таблицу в новую файловую группу или схему фрагментации в той же самой транзакции при использовании этой новой опции 'MOVE TO'. Давайте рассмотрим это на примере. В целях тестирования, мы создадим тестовую базу данных, пару файловых групп и таблицу, которую наполним некоторыми данными.

USE master
GO
CREATE DATABASE TEST
GO
ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_1
GO
ALTER DATABASE TEST ADD FILEGROUP TEST_DATA_2
GO
ALTER DATABASE TEST
ADD FILE
( NAME = TEST1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_1.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP TEST_DATA_1
GO
ALTER DATABASE TEST
ADD FILE
( NAME = TEST2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP Test_DATA_2
GO
USE TEST
GO
CREATE TABLE TAB1
(
TAB1_ID INT IDENTITY(1,1),
TAB1_NAME VARCHAR(100),
CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
) ON TEST_DATA_1 -- Filegroup we created.
GO
INSERT INTO TAB1(TAB1_NAME)
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO

Выполнение системной хранимой процедуры sp_help для таблицы TAB1 покажет, что файловой группой для таблицы является TEST_DATA_1.

sp_help TAB1

Вот выборочный результат выполнения этой команды

Data_located_on_filegroup
-------------------------
TEST_DATA_1

index_name index_description
---- ------------------------
PK_TAB1 clustered, unique, primary key located on TEST_DATA_1

constraint_type constraint_name
-------------------------------
PRIMARY KEY (clustered) PK_TAB1

Вышеприведенный результат показывает, что ограничение PK_TAB1 используется для создания кластерного индекса на таблице TAB1. Пожалуйста, отметьте, что всякий раз, когда определяется ограничение первичного ключа, и на таблице не существует кластерный индекс, SQL Server создаст кластерный индекс для ограничения первичного ключа. Это не справедливо для UNIQUE ограничения. Ограничение/индекс UNIQUE будет всегда некластеризованным, если не определено обратное при создании ограничения или индекса. 

Чтобы переместить таблицу в другую файловую группу, мы должны использовать команду удаления ограничения наряду с опцией MOVE TO, как это показано ниже. Как только таблица перемещена в новую файловую группу, мы можем снова создать ограничение первичного ключа.

ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO TEST_DATA_2)
GO
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
GO

После выполнения вышеприведенной команды таблица TAB1 будет уже находиться в файловой группе TEST_DATA_2. Если вы еще раз выполните хранимую процедуру sp_help, она покажет изменение файловой группы для таблицы TAB1.

sp_help TAB1
Data_located_on_filegroup
-------------------------
TEST_DATA_2

Необходимо помнить следующие ограничения при использовании опции 'MOVE TO':
- MOVE TO не применима для индексных представлений или некластеризованных индексов.
- Схема фрагментации или файловая группа уже должна существовать. 
- Если MOVE TO не указана, таблица будет размещаться в той же самой схеме фрагментации или файловой группе, которая была определена для кластерного индекса.

Это оказывается очень удобным, когда мы хотим переместить некоторые большие таблицы в их собственную файловую группу с целью повышения производительности или для создания резервных копий отдельных таблиц посредством резервной копии файловой группы. Мы можем также иметь это в виду, когда нам требуется клонировать определение таблицы и данных.