Раздел SQL Server Существующая таблица

Шаги по разбиению существующей таблицы следующие:

• Создать файловые группы
• Создать функцию разделения
• Создать схему раздела
• Создайте кластеризованный индекс для таблицы на основе схемы секционирования.

Мы разделим таблицу sales.orders в базе данных BikeStores по годам.

Создать файловые группы

Сначала создадим две новые группы файлов, в которых будут храниться строки с датами заказов в 2016 и 2017 годах:

ALTER DATABASE bikestores
ADD FILEGROUP salesorders_2016;
ALTER DATABASE bikestores
ADD FILEGROUP salesorders_2017;

Во-вторых, сопоставьте файловые группы с физическими файлами. Обратите внимание, что вам необходимо иметь папку D:\data на сервере перед выполнением следующих операторов:

ALTER DATABASE bikestores    
ADD FILE    (
    NAME = salesorders_2016,
    FILENAME = 'D:\data\salesorders_2016.ndf',
        SIZE = 10 MB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP salesorders_2016;
ALTER DATABASE bikestores    
ADD FILE    (
    NAME = salesorders_2017,
    FILENAME = 'D:\data\salesorders_2017.ndf',
        SIZE = 10 MB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP salesorders_2017;

Создать функцию разделения

Создайте функцию секционирования, которая принимает дату и возвращает три секции:

CREATE PARTITION FUNCTION sales_order_by_year_function(date)
AS RANGE LEFT 
FOR VALUES('2016-12-31', '2017-12-31');

Создать схему раздела

Создайте схему секционирования на основе функции секционирования sales_order_by_year_function:

CREATE PARTITION SCHEME sales_order_by_year_scheme
AS PARTITION sales_order_by_year_function
TO([salesorders_2016], [salesorders_2017], [primary]);

Создайте кластеризованный индекс по столбцу секционирования

Таблица заказов имеет order_id в качестве первичного ключа. Этот столбец первичного ключа также включен в кластеризованный индекс.

Чтобы разбить таблицу заказов по столбцу order_date, необходимо создать кластеризованный индекс для столбца order_date по схеме разбиения sales_order_by_year_scheme.

Для этого вам необходимо изменить кластеризованный индекс, включающий столбец order_id, на некластеризованный индекс, чтобы можно было создать новый кластеризованный индекс, включающий столбец order_date.

Но на order_id ссылается внешний ключ в таблице order_items. Поэтому вам необходимо выполнить следующие шаги:

Сначала удалите внешний ключ order_id из таблицы order_items:

ALTER TABLE [sales].[order_items] 
DROP CONSTRAINT [FK__order_ite__order__3A81B327]

Обратите внимание, что имя ограничения FK__order_ite__order__3A81B327 в вашей базе данных может отличаться.

Во-вторых, удалите ограничение первичного ключа из таблицы заказов:

ALTER TABLE [sales].[orders] 
DROP CONSTRAINT [PK__orders__46596229EDE70106];

В-третьих, добавьте order_id как некластеризованный первичный ключ в раздел PRIMARY:

ALTER TABLE [sales].[orders] 
ADD PRIMARY KEY NONCLUSTERED([order_id] ASC) 
ON [PRIMARY];

В-четвертых, создайте кластеризованный индекс, включающий столбец order_date:

CREATE CLUSTERED INDEX ix_order_date 
ON [sales].[orders]
(
 [order_date]
) ON [sales_order_by_year_scheme]([order_date])

В-пятых, удалите кластеризованный индекс:

DROP INDEX ix_order_date 
ON [sales].[orders];

Наконец, добавьте ограничение внешнего ключа обратно в таблицу order_items:

ALTER TABLE [sales].[order_items]  
WITH CHECK ADD FOREIGN KEY([order_id])
REFERENCES [sales].[orders]([order_id])
ON UPDATE CASCADE
ON DELETE CASCADE;

Лучше выполнить все приведенные выше операторы в транзакции следующим образом:

BEGIN TRANSACTION;
ALTER TABLE [sales].[order_items] 
DROP CONSTRAINT [FK__order_ite__order__3A81B327];
ALTER TABLE [sales].[orders] 
DROP CONSTRAINT [PK__orders__46596229EDE70106];
ALTER TABLE [sales].[orders] ADD PRIMARY KEY NONCLUSTERED 
(
 [order_id] ASC
) ON [PRIMARY];
CREATE CLUSTERED INDEX ix_order_date 
 ON [sales].[orders]
(
 [order_date]
) ON [sales_order_by_year_scheme]([order_date]);
DROP INDEX ix_order_date 
ON [sales].[orders];
ALTER TABLE [sales].[order_items]  
WITH CHECK ADD FOREIGN KEY([order_id])
REFERENCES [sales].[orders]([order_id])
ON UPDATE CASCADE
ON DELETE CASCADE;
COMMIT TRANSACTION;

Чтобы проверить количество строк в каждом разделе, используйте следующий запрос:

SELECT 
 p.partition_number AS partition_number,
 f.name AS file_group, 
 p.rows AS row_count
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'orders'
order by partition_number;

Краткое содержание

• Создайте кластеризованный индекс по столбцу секционирования на основе схемы секционирования для секционирования существующей таблицы.