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

Статья
06/13/2017

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

Создание секционированной таблицы или индекса обычно включает четыре этапа:

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

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

Перед началом:

Ограничения

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

Среда SQL Server Management Studio

Transact-SQL

Перед началом

Ограничения

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

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

Разрешения

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

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

Использование среды SQL Server Management Studio

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

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

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

Предупреждение

В дополнение к числу файловых групп, указанному в качестве граничных значений при создании секций, всегда требуется еще одна дополнительная файловая группа.
Продолжайте добавлять строки до создания всех файловых групп для секционированной таблицы.
Щелкните OK.
Ниже Выбор страницыщелкните Файлы.
Ниже Строкищелкните Добавить. В новой строке введите имя файла и выберите файловую группу.
Продолжайте добавлять строки до создания не менее одного файла для каждой файловой группы.
Разверните папку Таблицы и создайте таблицу, как обычно. Дополнительные сведения см. в разделе "Создание таблиц" (ядро СУБД). В качестве альтернативы можно указать существующую таблицу в следующей процедуре.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При выборе Расписаниещелкните Изменить расписание.
1. В диалоговом окне Создание расписания задания в поле Имя введите имя расписания задания.
2. В списке Тип расписания выберите тип расписания:
  - Запускать автоматически при запуске агента SQL Server
  - Запускать при бездействии процессоров
  - Повторяющееся. Выберите этот параметр, если новая секционированная таблица регулярно обновляется с учетом новых данных.
  - Однократно. Это параметр выбирается по умолчанию.
3. Установите или снимите флажок Включен , чтобы включить или отключить расписание.
4. При выборе Повторяющееся:
  1. В разделе Частотав списке Выполняется укажите частоту выполнения:
    - При выборе Ежедневнов поле Выполняется каждые укажите частоту повторного выполнения расписания задания в днях.
    - При выборе Еженедельнов поле Выполняется каждые укажите частоту повторного выполнения расписания задания в неделях. Выберите день или дни недели, в которые выполняется расписание задания.
    - При выборе Ежемесячнощелкните День или Определенный.
      - При выборе Деньвведите дату месяца, в которую должно выполняться расписание задания, и укажите частоту повторного выполнения расписания задания в месяцах. Например, если требуется, чтобы расписание задания выполнялось 15 числа каждого второго месяца, выберите День и введите в первом поле 15 и 2 — во втором поле. Обратите внимание, что число, введенное во втором поле, не должно превышать 99.
      - При выборе Определенныйвыберите определенный день недели в месяце, в котором должно выполняться расписание задания, и укажите частоту повторного выполнения расписания задания в месяцах. Например, если требуется, чтобы расписание задания выполнялось в последний день недели каждого второго месяца, выберите День, затем последний в первом списке и рабочий день во втором списке, а потом введите "2" во втором поле. Еще можно выбрать первый, второй, третийили четвертый, а также конкретные дни недели (например, воскресенье или среду) в первых двух списках. Обратите внимание, что число, введенное в последнем поле, не должно превышать 99.
  2. В поле Сколько раз в деньукажите частоту повторного выполнения расписания задания в день запуска расписания задания:
    - При выборе Выполнять раз вукажите определенное время дня для запуска расписания задания в поле Выполнять раз в . Укажите время дня: час, минуту и секунду.
    - При выборе Выполняется каждыеукажите частоту выполнения задания в выбранный день в поле Частота. Например, если требуется, чтобы расписание задания выполнялось каждые 2 часа в день запуска расписания задания, выберите Выполняется кажд., введите "2" в первом поле, а затем выберите в списке часы. В этом списке также можно выбрать минуты и секунды. Обратите внимание, что число, введенное в первом поле, не должно превышать 100.
      
      В поле Начинать в введите время для начала запуска расписания задания. В поле Заканчивать в введите время для завершения повторного выполнения расписания задания. Укажите время дня: час, минуту и секунду.
  3. В разделе Длительность, в области Дата началавведите дату начала запуска расписания задания. Выберите Дата окончания или Без даты окончания , чтобы указать дату завершения выполнения расписания задания. При выборе Дата окончаниявведите дату завершения запуска расписания задания.
5. При выборе значения Однократнов Однократное выполнениев поле Дата введите дату запуска расписания задания. В поле Время введите время запуска расписания задания. Укажите время дня: час, минуту и секунду.
6. В разделе Сводкав Описаниепроверьте правильность всех параметров расписания задания.
7. Щелкните OK.
После завершения работы с этой страницей нажмите кнопку Далее.
На странице Просмотр сводки в разделе Просмотр выбранных параметровразверните все доступные параметры, чтобы убедиться в правильности всех настроек секции. Если все настройки правильные, нажмите кнопку Готово.
Страница Выполнение мастера создания секций используется для отслеживания сведений о состоянии действий мастера создания секций. В зависимости от действий, выбранных в мастере, страница выполнения может содержать одно или несколько действий. В верхнем поле показано общее состояние мастера и число полученных им сообщений о состоянии, предупреждений и сообщений об ошибках.

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

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

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

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

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

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

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

Сохранить отчет в файл
Открытие диалогового окна Сохранить отчет как .

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

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

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

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

Использование Transact-SQL

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

В обозревателе объектов подключитесь к экземпляру ядра СУБД.
На стандартной панели выберите пункт Создать запрос.

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

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\MSSQL12.MSSQLSERVER\MSSQL\DATA\t1dat1.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP test1fg;  
ALTER DATABASE AdventureWorks2012   
ADD FILE   
(  
    NAME = test2dat2,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\t2dat2.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP test2fg;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD FILE   
(  
    NAME = test3dat3,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\t3dat3.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP test3fg;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD FILE   
(  
    NAME = test4dat4,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\t4dat4.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
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 секционирована. Если таблица не секционирована, не возвращается ни одна строка.
```
SELECT *   
FROM sys.tables AS t   
JOIN sys.indexes AS i   
    ON t.[object_id] = i.[object_id]   
    AND i.[type] IN (0,1)   
JOIN sys.partition_schemes ps   
    ON i.data_space_id = ps.data_space_id   
WHERE t.name = 'PartitionTable';   
GO  
```

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

Следующий запрос возвращает граничные значения для каждой секции в таблице 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.[object_id] 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.[object_id] = i.[object_id]   
    AND i.[type] <= 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.[object_id] = i.[object_id]   
    AND ic.index_id = i.index_id   
    AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column   
JOIN sys.columns AS c   
    ON t.[object_id] = c.[object_id]   
    AND ic.column_id = c.column_id   
WHERE t.name = 'PartitionTable' ;   
GO

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

Поделиться через

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

Перед началом

Ограничения

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

Разрешения

Использование среды SQL Server Management Studio

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

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

Использование Transact-SQL

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

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

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

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

Дополнительные ресурсы