Можно создать секционированную таблицу или индекс в SQL Server 2016 с помощью SQL Server Management Studio или Transact-SQL. Данные в секционированной таблице и индексах горизонтально разделены на блоки, которые могут быть распределены между несколькими файловыми группами в базе данных. Секционирование может улучшить управляемость и масштабируемость больших таблиц и индексов.

Или индекса обычно включает четыре этапа:

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

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

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

Создание или изменение таблицы или индекса и указание схемы секционирования в качестве местоположения хранения.

В этом разделе

Перед началом работы выполните следующие действия.

Ограничения

Безопасность

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

Среда SQL Server Management Studio

Ограничения

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

Если у каких-либо строк в функции секционирования имеются столбцы секционирования со значениями NULL, эти строки размещаются в крайней левой секции. Однако если значение NULL указано в качестве граничного значения и указан параметр RIGHT, крайняя левая секция остается пустой, а значения NULL располагаются во второй секции.

Безопасность

Разрешения

Для создания секционированной таблицы требуется разрешение CREATE TABLE в базе данных и разрешение ALTER для схемы, в которой создается таблица. Для создания секционированного индекса требуется разрешение ALTER на таблицу или представление, в которых создается индекс. Создание секционированной таблицы или индекса требует любого из следующих дополнительных разрешений:

Разрешение ALTER ANY DATASPACE. Это разрешение назначено по умолчанию членам предопределенной роли сервера sysadmin и предопределенных ролей базы данных db_owner и db_ddladmin .

Разрешение CONTROL или ALTER для базы данных, в которой создаются функция и схема секционирования.

Разрешение CONTROL SERVER или ALTER ANY DATABASE для сервера базы данных, в которой создаются функция и схема секционирования.

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

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

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

Щелкните правой кнопкой мыши таблицу для секционирования, выберите Хранение и щелкните Создать секцию…

В Мастере создания секций на странице Приветствия мастера создания секций щелкните Далее .

На странице Выбор столбца секционирования в сетке выберите столбец, по которому необходимо секционировать таблицу. В сетке Доступные столбцы секционирования отображаются только столбцы с типами данных, по которым можно секционировать данные. Если выбрать в качестве столбца секционирования вычисляемый столбец, его необходимо назначить постоянным.

Выбор столбца секционирования и диапазона значений определяется прежде всего степенью, до которой данные должны быть логически сгруппированы. Например, можно разделить данные на логические группы по месяцам или кварталам года. Планируемые запросы к данным определяют, адекватно ли такое логическое группирование для управления секциями таблицы. В качестве столбцов секционирования могут использоваться данные любого типа, кроме text , ntext , image , xml , timestamp , varchar(max) , nvarchar(max) , varbinary(max) , псевдонимов типов данных, а также определяемых пользователем типов данных CLR.

Совместное размещение этой таблицы с выбранной секционированной таблицей
Позволяет выбрать секционированную таблицу, которая содержит связанные данные, для соединения с данной таблицей по столбцу секционирования. Таблицы с секциями, объединенные по столбцам секционирования, обычно более эффективны в запросах.

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

После выбора столбца секционирования и других столбцов щелкните Далее .

На странице Выбор функции секционирования в разделе Выберите функцию секционирования щелкните или . При выборе Создать функцию секционирования введите имя функции. Если выбран вариант Существующая функция секционирования , то выберите в списке имя функции, которая будет использоваться для секционирования. Обратите внимание, что при отсутствии в базе данных других функций секционирования параметр Существующая функция секционирования будет недоступен.

Далее .

На странице Выбор схемы секционирования в разделе Выберите схему секционирования щелкните или . При выборе Создать схему секционирования введите имя схемы. Если выбран вариант Существующая схема секционирования , то выберите в списке имя схемы, которая будет использоваться. При отсутствии других схем секционирования в базе данных параметр Существующая схема секционирования будет недоступен.

После завершения работы с этой страницей нажмите кнопку Далее .

На странице Сопоставление секций в разделе Диапазон выберите Левая граница или Правая граница , чтобы выбрать для включения во все создаваемые файловые группы наибольшее или наименьшее ограничивающее значение. В дополнение к числу файловых групп, указанному в качестве граничных значений при создании секций, необходимо всегда вводить еще одну дополнительную файловую группу.

В сетке Выбор файловых групп и указание граничных значений в поле Файловая группа выберите файловую группу, в которую будут секционироваться данные. В разделе Граница введите граничное значение для каждой файловой группы. Если граничное значение не указано, функция секционирования сопоставляет всю таблицу или индекс с одной секцией, используя имя функции секционирования.

На этой странице доступны следующие дополнительные параметры:

Установить границы…
Открытие диалогового окна Установка граничных значений , в котором можно выбрать граничные значения и диапазоны дат для секций. Этот параметр доступен, только если выбран столбец секционирования, содержащий данные одного из следующих типов: date , datetime , smalldatetime , datetime2 или datetimeoffset .

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

В диалоговом окне Задание граничных значений можно задать следующие дополнительные параметры:

Дата начала
Выбор даты начала для значений диапазона секций.

Дата окончания
Выбор даты окончания для значений диапазона секций. При выборе Левая граница на странице Сопоставление секций эта дата будет последним значением для каждой из файловых групп и секций. При выборе Правая граница на странице Сопоставление секций эта дата будет первым значением в предпоследней файловой группе.

Диапазон даты
Выбор степени детализации дат или шага значения диапазона для каждой секции.

После завершения работы с этой страницей нажмите кнопку Далее .

На странице Выбор выходного параметра укажите способ заполнения секционированной таблицы. Выберите Создать скрипт для создания скрипта SQL на основе данных на предыдущих страницах мастера. Выберите Запустить немедленно , чтобы создать новую секционированную таблицу после завершения работ со всеми оставшимися страницами мастера. Выберите Расписание , чтобы создать новую секционированную таблицу в заранее заданное время в будущем.

При выборе Создать скрипт в Параметры скрипта будут доступны следующие параметры:

Вывести скрипт в файл
Создание скрипта как SQL-файла. Введите имя и местоположение файла в поле Имя файла или щелкните Обзор , чтобы открыть диалоговое окно Расположение файла скрипта . В разделе Сохранить как выберите Текст в Юникоде или Текст ANSI .

Вывести скрипт в буфер обмена
Сохранение скрипта в буфере обмена.

Вывести скрипт в новое окно запроса
Скрипт создается в новом окне редактора запросов. Это параметр выбирается по умолчанию.

При выборе Расписание щелкните Изменить расписание .

1. В диалоговом окне Создание расписания задания в поле Имя введите имя расписания задания.

   В списке Тип расписания выберите тип расписания:

   • Запускать автоматически при запуске агента SQL Server

     Запускать при бездействии процессоров

     Повторяющееся . Выберите этот параметр, если новая секционированная таблица регулярно обновляется с учетом новых данных.

     Однократно . Это параметр выбирается по умолчанию.

Установите или снимите флажок Включен , чтобы включить или отключить расписание.

При выборе Повторяющееся :

1. В разделе Частота в списке Выполняется укажите частоту выполнения:

   • При выборе Ежедневно в поле Выполняется каждые укажите частоту повторного выполнения расписания задания в днях.

     При выборе Еженедельно в поле Выполняется каждые укажите частоту повторного выполнения расписания задания в неделях. Выберите день или дни недели, в которые выполняется расписание задания.

     При выборе Ежемесячно щелкните День или Определенный .

     • При выборе День введите дату месяца, в которую должно выполняться расписание задания, и укажите частоту повторного выполнения расписания задания в месяцах. Например, если требуется, чтобы расписание задания выполнялось 15 числа каждого второго месяца, выберите День и введите в первом поле «15» и «2» - во втором поле. Обратите внимание, что число, введенное во втором поле, не должно превышать «99».

       При выборе Определенный выберите определенный день недели в месяце, в котором должно выполняться расписание задания, и укажите частоту повторного выполнения расписания задания в месяцах. Например, если требуется, чтобы расписание задания выполнялось в последний день недели каждого второго месяца, выберите День , выберите последний в первом списке и рабочий день во втором списке, а затем введите «2» во втором поле. Еще можно выбрать первый , второй , третий или четвертый , а также конкретные дни недели (например, воскресенье или среду) в первых двух списках. Обратите внимание, что число, введенное в последнем поле, не должно превышать «99».

В поле Сколько раз в день укажите частоту повторного выполнения расписания задания в день запуска расписания задания:

• При выборе Выполнять раз в укажите определенное время дня для запуска расписания задания в поле Выполнять раз в . Укажите время дня: час, минуту и секунду.

  При выборе Выполняется каждые укажите частоту выполнения задания в выбранный день в поле Частота . Например, если требуется, чтобы расписание задания выполнялось каждые 2 часа в день запуска расписания задания, выберите Выполняется кажд. , введите "2" в первом поле, а затем выберите в списке часы . В этом списке также можно выбрать минуты и секунды . Обратите внимание, что число, введенное в первом поле, не должно превышать «100».

  В поле Начинать в введите время для начала запуска расписания задания. В поле Заканчивать в введите время для завершения повторного выполнения расписания задания. Укажите время дня: час, минуту и секунду.

В разделе Длительность , в области Дата начала введите дату начала запуска расписания задания. Выберите Дата окончания или Без даты окончания , чтобы указать дату завершения выполнения расписания задания. При выборе Дата окончания введите дату завершения запуска расписания задания.

При выборе значения Однократно в Однократное выполнение в поле Дата введите дату запуска расписания задания. В поле Время введите время запуска расписания задания. Укажите время дня: час, минуту и секунду.

В разделе Сводка в Описание проверьте правильность всех параметров расписания задания.

Нажмите кнопку ОК .

После завершения работы с этой страницей нажмите кнопку Далее .

На странице Просмотр сводки в разделе Просмотр выбранных параметров разверните все доступные параметры, чтобы убедиться в правильности всех настроек секции. Если все настройки правильные, нажмите кнопку Готово .

Страница Выполнение мастера создания секций используется для отслеживания сведений о состоянии действий мастера создания секций. В зависимости от действий, выбранных в мастере, страница выполнения может содержать одно или несколько действий. В верхнем поле показано общее состояние мастера и число полученных им сообщений о состоянии, предупреждений и сообщений об ошибках.

На странице Выполнение мастера создания секций доступны следующие параметры:

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

Действие
Задает тип и имя каждого действия.

Состояние
Указывает, вернуло ли действие мастера в целом значение Успешно или Ошибка .

Сообщение
Любые сообщения об ошибках или предупреждения от процесса.

Отчет
Создание отчета, содержащего результаты мастера создания секций. Доступные параметры: Просмотреть отчет , Сохранить отчет в файл , и Отправить отчет по электронной почте .

Просмотр отчета
Открытие диалогового окна Просмотр отчета , которое содержит текстовый отчет о работе мастера создания секций.

Копировать отчет в буфер обмена
Копирование результатов отчета о работе мастера в буфер обмена.

Отправить отчет электронной почтой
Копирование результатов отчета о состоянии мастера в сообщение электронной почты.

Завершив выбор параметров, нажмите кнопку Закрыть .

Мастер создания секций создаст функцию и схему секционирования, а затем применит секционирование к указанной таблице. Чтобы проверить секционирование таблицы, в обозревателе объектов щелкните правой кнопкой мыши таблицу и выберите Свойства . Перейдите на страницу Хранилище . На странице отображается информация, в том числе имя функции секционирования, схема и число секций.

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

В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.

На стандартной панели выберите пункт Создать запрос .

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

USE AdventureWorks2012; GO -- Adds four new filegroups to the AdventureWorks2012 database ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test1fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test2fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test3fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test4fg; -- Adds one file for each filegroup. ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test1dat1, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat1.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test1fg; ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test2dat2, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t2dat2.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test2fg; GO ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test3dat3, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t3dat3.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test3fg; GO ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test4dat4, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t4dat4.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test4fg; GO -- Creates a partition function called myRangePF1 that will partition a table into four partitions CREATE PARTITION FUNCTION myRangePF1 (int ) AS RANGE LEFT FOR VALUES (1 , 100 , 1000 ) ; GO -- Creates a partition scheme called myRangePS1 that applies myRangePF1 to the four filegroups created above CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) ; GO -- Creates a partitioned table called PartitionTable that uses myRangePS1 to partition col1 CREATE TABLE PartitionTable (col1 int PRIMARY KEY , col2 char (10 )) ON myRangePS1 (col1) ; GO

Определение секционирования таблицы

Следующий запрос возвращает одну или несколько строк, если таблица PartitionTable секционирована. Если таблица не секционирована, не возвращается ни одна строка.

Определение граничных значений для секционированной таблицы

Следующий запрос возвращает граничные значения для каждой секции в таблице PartitionTable .

SELECT t .name AS TableName, i .name AS IndexName, p .partition_number, p .partition_id, i .data_space_id, f .function_id, f .type_desc, r.boundary_id, r.value AS BoundaryValue FROM sys .tables AS t JOIN sys .indexes AS i ON t .object_id = i .object_id JOIN sys .partitions AS p ON i .object_id = p .object_id AND i .index_id = p .index_id JOIN sys .partition_schemes AS s ON i .data_space_id = s.data_space_id JOIN sys .partition_functions AS f ON s.function_id = f .function_id LEFT JOIN sys .partition_range_values AS r ON f .function_id = r.function_id and r.boundary_id = p .partition_number WHERE t .name = "PartitionTable" AND i .type <= 1 ORDER BY p .partition_number;

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

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

SELECT t . AS ObjectID , t .name AS TableName , ic.column_id AS PartitioningColumnID , c .name AS PartitioningColumnName FROM sys .tables AS t JOIN sys .indexes AS i ON t . = i . AND i . <= 1 -- clustered index or a heap JOIN sys .partition_schemes AS ps ON ps.data_space_id = i .data_space_id JOIN sys .index_columns AS ic ON ic. = i . AND ic.index_id = i .index_id AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column JOIN sys .columns AS c ON t . = c . AND ic.column_id = c .column_id WHERE t .name = "PartitionTable" ; GO

Дополнительные сведения см. в разделе.

Добрый вечер/день/утро уважаемые хабралюди! Продолжаем развивать и дополнять блог о моей любимой open source rdbms Postgresql. Чудесным образом так получилось, что тема сегодняшнего топика еще ни разу здесь не подымалась. Надо сказать, что секционирование в postgresql очень хорошо описано в документации , но разве ж это меня остановит?).

Вступление

Вообще под секционированием в общем случае понимают не какую-то технологию, а скорее подход к проектированию БД, появившийся задолго до того, как СУБД начали поддерживать т.н. секционированные таблицы. Мысль очень простая - разделить таблицу на несколько частей меньшего размера. Различают два подвида - горизонтальное и вертикальное секционирование.

Горизонтальное секционирование

Части таблицы содержат разные ее строки. Положим у нас есть таблица логов некоего абстрактного приложения - LOGS. Мы можем разбить ее на части - одна для логов за январь 2009, другая - за февраль 2009, и т.д.

Вертикальное секционирование

Части таблицы содержат разные ее столбцы. Найти применение для вертикального секционирования (когда оно действительно оправдано) несколько сложнее, чем для горизонтального. В качестве сферического коня предлагаю рассмотреть такой вариант: таблица NEWS имеет столбцы ID, SHORTTEXT, LONGTEXT, и пусть поле LONGTEXT используется намного реже первых двух. В таком случае имеет смысл разбить таблицу NEWS по столбцам (создать две таблицы для SHORTTEXT и LONGTEXT соответственно, связанных первичными ключами + создать view NEWS, содержащую оба столбца). Таким образом, когда нам нужно только описание новости, СУБД не придется читать с диска еще и весь текст новости.

Поддержка секционирования в современных СУБД

Большинство современных СУБД поддерживают секционирование таблиц в том или ином виде.

• Oracle - поддерживает секционирование начиная с 8й версии. Работа с секциями с одной стороны очень простая (вообще можно о них не думать, работаешь как с обычной таблицей*), а с другой - все очень гибко. Секции можно разбивать на «subpartitions», удалять, делить, переносить. Поддерживаются разные варианты индексирования секционированной таблицы (глобальный индекс, секционированный индекс). Ссылочка на объемное описание.
• Microsoft SQL Server - поддержка секционирования появилась недавно (в 2005). Первое впечатление от использования - «Ну наконец-то!!:)», второе - «Работает, вроде все ок». Документация на msdn
• MySQL - поддерживает начиная с версии 5.1.
• И так далее…

*-вру, конечно, есть стандартный набор сложностей - создать вовремя новую секцию, старую выкинуть и т.д., но все равно как-то все просто и понятно.

Секционирование в Postgresql

Секционирование таблиц в postgresql несколько отличается в реализации от остальных БД. Основой для секционирования служит наследование таблиц (вещь присущая исключительно postgresql). То есть, у нас должна быть основная таблица (master table), а ее секциями будут таблицы-наследники. Будем рассматривать секционирование на примере задачи, приближенной к реальности.

Постановка задачи

База данных используется для сбора и анализа данных о посетителях сайта/сайтов. Объемы данных достаточно велики для того, чтобы задуматься о секционировании. При анализе в большинстве случаев используются данные за последний день.
1. Создаем основную таблицу:

CREATE TABLE analytics.events

user_id UUID NOT NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
url VARCHAR (1024) NOT NULL ,
referrer VARCHAR (1024),
ip INET NOT NULL
);

2. Секционировать будем по дням по полю event_time. На каждый день будем создавать новую секцию. Именовать секции будем по правилу: analytics.events_DDMMYYYY. Вот например секция для 1го января 2010 года.

CREATE TABLE analytics.events_01012010
event_id BIGINT DEFAULT nextval("analytics.seq_events" ) PRIMARY KEY ,
CHECK (event_time >= TIMESTAMP "2010-01-01 00:00:00" AND event_time < TIMESTAMP "2010-01-02 00:00:00" )
) INHERITS (analytics.events);

* This source code was highlighted with Source Code Highlighter .

При создании секции явно задаем поле event_id (PRIMARY KEY не наследуется) и создаем CHECK CONSTRAINT на поле event_time, дабы не вставить лишнего.

3. Создаем индекс на поле event_time. При разбиении таблицы на секции, мы подразумеваем, что большинство запросов к таблице events будут использовать условие на поле event_time, так что индекс на этом поле нам очень поможет.

CREATE INDEX events_01012010_event_time_idx ON analytics.events_01012010 USING btree(event_time);

* This source code was highlighted with Source Code Highlighter .

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

CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW .event_time >= TIMESTAMP "2010-01-01 00:00:00" AND
NEW .event_time < TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 VALUES (NEW .*);
ELSE
RAISE EXCEPTION "Date % is out of range. Fix analytics.events_insert_trigger" , NEW .event_time;
END IF ;
RETURN NULL ;
END ;
$$
LANGUAGE plpgsql;

* This source code was highlighted with Source Code Highlighter .

CREATE TRIGGER events_before_insert
BEFORE INSERT ON analytics.events
FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();

* This source code was highlighted with Source Code Highlighter .

5. Все готово, у нас теперь есть секционированная таблица analytics.events. Можем начинать яростно анализировать ее данные. Кстати, CHECK constraints мы создавали не только для того, чтобы защитить секции от некорректных данных. Postgresql может их использовать при составлении плана запроса (правда при живом индексе на event_time выигрыш это даст минимальный), достаточно воспользоваться директивой constraint_exclusion:

SET constraint_exclusion = on ;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE ;

* This source code was highlighted with Source Code Highlighter .

Конец первой части

Итак, что мы имеем? Давайте по пунктам:
1. Таблицу events, разбитую на секции, анализ имеющихся данных за последние сутки становится проще и быстрее.
2. Ужас от осознания того, что все это нужно как-то поддерживать, создавать вовремя секции, не забывая менять триггер соответствующим образом.

О том как просто и беззаботно работать с секционированными таблицами расскажу во второй части.

UPD1: Заменил партиционирование на секционирование
UPD2:
По мотивам замечания одного из читателей, не имеющего, к сожалению, аккаунта на хабре:
С наследованием связано несколько моментов, которые стоит учитывать при проектировании. Секции не наследуют первичный ключ и внешние ключи на их столбцы. То есть, при создании секции, нужно явно создавать PRIMARY KEY и FOREIGN KEYs на столбцы секции. От себя замечу, что создавать FOREIGN KEY на столбцы секционированной таблицы не лучший путь. В большинстве случаев секционированная таблица является «таблицей фактов» и сама ссылается на «dimension» таблицы.

В ходе работ над большими таблицами мы постоянно сталкиваемся с проблемами с производительностью их обслуживания и обновления данных. Одним из наиболее продуктивных и удобных решений возникающих проблем является секционирование.
Если в общих словах, то секционирование – это разбиение таблицы или индекса на блоки. В зависимости от настройки секционирования блоки могут быть различных размеров, могут храниться в разных файловых группах и файлах.
У секционирования есть как преимущества, так и недостатки.
Преимущества хорошо расписаны на сайте компании Microsoft, приведу выдержку:

«Секционирование больших таблиц или индексов может дать следующие преимущества в управляемости и производительности.

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

В процессе сортировки данных для операций ввода-вывода в SQL Server сначала проводится сортировка данных по секциям. SQL Server может одновременно обращаться только к одному диску, что может снизить производительность. Для ускорения сортировки данных рекомендуется распределить файлы данных в секциях по нескольким жестким дискам, создав RAID. Таким образом, несмотря на сортировку данных по секциям, SQL Server сможет одновременно осуществлять доступ ко всем жестким дискам каждой секции.
Кроме того, можно повысить производительность, применяя блокировки на уровне секций, а не всей таблицы. Это может уменьшить количество конфликтов блокировок для таблицы ».

К недостаткам же можно отнести сложность в администрировании и поддержке работы секционированных таблиц.

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

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

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

1. Найти нужные строки в большой таблице;
2. Удалить найденные строки из таблицы и индекса;
3. Вставить новые строки в таблицу, обновить индекс.

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

Перейдем от слов к делу и покажем, как же это реализовать.

1. Для начала настраиваем секционированную таблицу так, как написано в статье, указанной выше.
2. Создаем таблицы, необходимые для обмена.

Для обновления данных нам потребуется мини-копия целевой таблицы. Мини-копией она является потому, что в ней будут храниться данные, которые должны добавиться в целевую таблицу, т.е. данные всего за 1 месяц. Так же потребуется третья пустая таблица для реализации обмена данных. Зачем она нужна – объясню позже.

К мини-копии и таблице для обмена ставятся жесткие условия:

• До использования оператора SWITCH должны существовать обе таблицы. Перед выполнением операции переключения в базе данных должны существовать и таблица, откуда перемещается секция (исходная таблица), и таблица, получающая секцию (целевая таблица).
• Секция-получатель должна существовать и должна быть пустой. Если таблица добавляется как секция в уже существующую секционированную таблицу или секция перемещается из одной секционированной таблицы в другую, то секция-получатель должна существовать и быть пустой.
• Несекционированная таблица-получатель должна существовать и должна быть пустой. Если секция предназначена для формирования единой несекционированной таблицы, то необходимо, чтобы таблица, получающая новую секцию, существовала и являлась пустой несекционированной таблицей.
• Секции должны быть из одного и того же столбца. Если секция переключена из одной секционированной таблицы в другую, то обе таблицы должны быть секционированы по одному и тому же столбцу.
• Исходная и целевая таблицы должны находиться в одной и той же файловой группе. Исходная и целевая таблицы в инструкции ALTER TABLE...SWITCH должны храниться в одной и той же файловой группе, так же как и их столбцы с большими значениями. Любые соответствующие индексы, секции индексов или индексированные представления секций также должны храниться в той же файловой группе. Однако она может отличаться от файловой группы для соответствующих таблиц или других соответствующих индексов.

Объясню ограничения на нашем примере:

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

2. Таблица для обмена должна быть пустой и так же должна быть секционированна по тому же столбцу или же должна храниться в той же файловой группе.

3. Реализуем обмен.

Сейчас мы имеем следующее:
Таблица с данными за все времена (далее Table_A)
Таблица с данными за 1 месяц (далее Table_B)
Пустая таблица (далее Table_C)

Первым делом нам нужно узнать в какой секции у нас хранятся данные.
Узнать это можно запросом:

SELECT
count(*) as
, $PARTITION.(dt) as
, rank() over (order by $PARTITION.(dt))
FROM dbo. (nolock)
group by $PARTITION.(dt)

В этом запросе мы получаем секции, в которых есть строки с информацией. Количество можно не подсчитывать – мы это делали для проверки необходимости обмена данных. Rank же используем, чтобы можно было идти в цикле и обновлять несколько секций в одной процедуре.

Как только узнали в каких секциях у нас хранятся данные – их можно менять местами. Допустим, что данные хранятся в секции 1.

Тогда нужно выполнить следующие операции:
Поменять секции из целевой таблицы с таблицей для обмена.
ALTER TABLE . SWITCH PARTITION 1 TO . PARTITION 1
Теперь мы имеем следующее:
В целевой таблице не осталось данных в нужной нам секции, т.е. секция пуста
Поменять местами секции из целевой таблицы и мини-копии
ALTER TABLE . SWITCH PARTITION 1 TO . PARTITION 1
Теперь мы имеем следующее:
В целевой таблице появились данные за месяц, а в мини-копии теперь пустота
Очистить или удалить таблицу для обмена.

Если у вас на таблице имеется кластерный индекс, то так же не проблема. Его необходимо создать на всех 3 таблицах с секционированием по одному и тому же столбцу. При смене секций индекс автоматически обновится, не перестраиваясь.

В этой статье я собираюсь продемонстрировать особенности планов исполнения запросов при обращении к секционированным таблицам. Обратите внимание, что существует большая разница между секционированными таблицами (которые стали доступны только с SQL Server 2005) и секционированными представлениями (которые были доступных ещё в SQL Server 2000, и по-прежнему доступны в SQL Server 2005 и последующих версиях). Особенности планов запросов к секционированным представлениям я продемонстрирую в другой статье.

Просмотр таблицы

Давайте создадим простую секционированную таблицу:

create partition function pf(int) as range for values (0, 10, 100)

create partition scheme ps as partition pf all to ()

create table t (a int, b int) on ps(a)

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

PtnId	Values
1	t.a <= 0
2	0 < t.a <= 10
3	10 < t.a <= 100
4	100 < t.a

Теперь давайте рассмотрим план такого запроса, который бы вынудил оптимизатор использовать просмотр всей таблицы (Table Scan):

……|–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
…….|–Table Scan(OBJECT:([t]))

В представленном выше плане, SQL Server явно указывает все идентификаторы секции в операторе «Constant Scan», который реализует просмотр таблицы и поставляет данные оператору соединения вложенных циклов. Тут следует напомнить, что оператор соединения вложенных циклов выполняет проход по внутренней таблице (в данном случае это полный просмотр таблицы) один раз для каждого значения из внешней таблицы (в нашем случае это «Constant Scan»). Таким образом, мы выполняем просмотр таблицы четыре раза; один раз для каждого идентификатора секции.

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