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

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

  • Статья

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

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

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

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

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

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

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

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

    Ограничения

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

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

    Среда 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

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

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

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

  2. В диалоговом окне Свойства базы данных — database_name в области Выбор страницы щелкните Файловые группы.

  3. Ниже Строки щелкните Добавить. В новой строке введите имя файловой группы.

    ПредупреждениеВнимание!

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

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

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

  6. Ниже Выбор страницы щелкните Файлы.

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

  8. Продолжайте добавлять строки до создания не менее одного файла для каждой файловой группы.

  9. Разверните папку Таблицы и создайте таблицу, как обычно. Дополнительные сведения см. в разделе Создание таблиц (компонент Database Engine). В качестве альтернативы можно указать существующую таблицу в следующей процедуре.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Значок стрелки, используемый со ссылкой «В начало»[В начало]

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

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

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

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

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

    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\MSSQL110.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\MSSQL110.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\MSSQL110.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\MSSQL110.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

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

Значок стрелки, используемый со ссылкой «В начало»[В начало]