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

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

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

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

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

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

Примечание.

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

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

Разрешения

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

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

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

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

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

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

Этот пример подходит для База данных SQL Azure, который не поддерживает добавление файлов и файловых групп. Секционирование таблиц поддерживается в База данных SQL Azure путем создания секций в файловой PRIMARY группе. Для SQL Server и Управляемый экземпляр SQL Azure может потребоваться указать созданную пользователем файловую группу в зависимости от методов управления файлами и файлов.

В этом примере показано, как создать секционированную таблицу в SQL Server Management Studio (SSMS) с помощью Transact-SQL и назначить все разделы файловой PRIMARY группе. Пример.

  • Создает функцию myRangePF1 секции RANGE RIGHT с тремя значениями границ с помощью типа данных datetime2. Три значения границ приводят к секционированной таблице с четырьмя секциями.
  • Создает схему секционирования с именем myRangePS1 , которая использует ALL TO синтаксис для назначения всех секций в myRangePF1 функции секционирования файловой PRIMARY группе.
  • Создает таблицу с именем PartitionTable схемы myRangePS1 секционирования, указывая столбец с именем col1 столбца секционирования.

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

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

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

CREATE PARTITION FUNCTION myRangePF1 (datetime2(0))
    AS RANGE RIGHT FOR VALUES ('2022-04-01', '2022-05-01', '2022-06-01') ;
GO

CREATE PARTITION SCHEME myRangePS1
    AS PARTITION myRangePF1
    ALL TO ('PRIMARY') ;
GO

CREATE TABLE dbo.PartitionTable (col1 datetime2(0) PRIMARY KEY, col2 char(10))
    ON myRangePS1 (col1) ;
GO

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

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

SQL Server и Управляемый экземпляр SQL Azure поддерживают создание файловых групп и файлов. Управляемый экземпляр SQL Azure автоматически настраивает путь для всех добавленных файлов базы данных, поэтому ALTER DATABASE ADD FILE команда в Управляемый экземпляр SQL Azure не разрешает FILENAME параметр. База данных SQL Azure поддерживает создание секционированных таблиц только в файловой PRIMARY группе. Найдите пример кода для База данных SQL Azure в создании секционированных таблиц в одной файловой группе с помощью Transact-SQL.

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

  • Добавляет четыре новых файловых групп в базу данных.
  • Добавляет один файл в каждую файловую группу.
  • Создает функцию секции RANGE RIGHT, вызванную myRangePF1 тремя значениями границ, которые будут разделять таблицу на четыре секции.
  • Создает схему секционирования, которая myRangePS1 применяется myRangePF1 к четырем новым файловыми группами.
  • Создает секционированную таблицу, которая PartitionTable используется myRangePS1 для секционирования col1.

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

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

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

    Если вы используете управляемый экземпляр, удалите FILENAME параметр и связанное значение из ALTER DATABASE ADD FILE команды. Управляемый экземпляр определяет путь к файлу автоматически.

    Если вы используете экземпляр SQL Server, настройте значение параметра FILENAME в расположение, соответствующее вашему экземпляру.

    Если вы хотите использовать существующую базу данных, удалите CREATE DATABASE команду и измените USE инструкцию на соответствующее имя базы данных.

    SELECT Execute.

    CREATE DATABASE PartitionTest;
GO

USE PartitionTest;
GO

ALTER DATABASE PartitionTest
ADD FILEGROUP test1fg;
GO
ALTER DATABASE PartitionTest
ADD FILEGROUP test2fg;
GO
ALTER DATABASE PartitionTest
ADD FILEGROUP test3fg;
GO
ALTER DATABASE PartitionTest
ADD FILEGROUP test4fg;

ALTER DATABASE PartitionTest
ADD FILE
(
    NAME = partitiontest1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest1.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP test1fg;
ALTER DATABASE PartitionTest
ADD FILE
(
    NAME = partitiontest2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest2.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP test2fg;
GO
ALTER DATABASE PartitionTest
ADD FILE
(
    NAME = partitiontest3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest3.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP test3fg;
GO
ALTER DATABASE PartitionTest
ADD FILE
(
    NAME = partitiontest4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest4.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP test4fg;
GO

CREATE PARTITION FUNCTION myRangePF1 (datetime2(0))
    AS RANGE RIGHT FOR VALUES ('2022-04-01', '2022-05-01', '2022-06-01') ;
GO

CREATE PARTITION SCHEME myRangePS1
    AS PARTITION myRangePF1
    TO (test1fg, test2fg, test3fg, test4fg) ;
GO

CREATE TABLE PartitionTable (col1 datetime2(0) PRIMARY KEY, col2 char(10))
    ON myRangePS1 (col1) ;
GO

Секционирование таблицы с помощью SSMS

Выполните действия, описанные в этом разделе, чтобы при необходимости создать файловые группы и соответствующие файлы, а затем создать секционированную таблицу или секционировать существующую таблицу с помощью мастера создания секций в SQL Server Management Studio (SSMS). Мастер создания секций доступен в SSMS для SQL Server и Управляемый экземпляр SQL Azure. Сведения о База данных SQL Azure см. в статье "Создание секционированных таблиц в одной файловой группе с помощью Transact-SQL".

Создание файловых групп (необязательно)

Если вы хотите разместить секционированную таблицу в одной или нескольких новых файловых группах, выполните действия, описанные в этом разделе. SQL Server и Управляемый экземпляр SQL Azure поддерживают создание файловых групп и файлов. Для Управляемый экземпляр SQL Azure путь для всех созданных файлов будет автоматически настроен для вас.

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

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

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

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

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

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

  5. Нажмите ОК.

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

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

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

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

  1. При необходимости разверните папку "Таблицы" и создайте таблицу, как правило. Дополнительные сведения см. в разделе "Создание таблиц" (ядро СУБД). Кроме того, можно указать существующую таблицу на следующем шаге.

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

  3. В мастере создания секций на странице "Добро пожаловать в мастер создания секций" нажмите кнопку "Далее".

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    7. Нажмите ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Запрос метаданных секционированных таблиц и индексов

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

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

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

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, *
FROM sys.tables AS t
JOIN sys.indexes AS i
    ON t.[object_id] = i.[object_id]
JOIN sys.partition_schemes ps
    ON i.data_space_id = ps.data_space_id
WHERE t.name = 'PartitionTable';
GO

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

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

Запрос использует type столбец в sys.indexes для возврата только сведений о кластеризованном индексе таблицы или базовой таблицы, если таблица является кучей. Чтобы включить все секционированные некластеризованные индексы в результаты запроса, удалите или закомментируйте AND i.type <= 1 из запроса.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, 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 SchemaName, t.name, i.name, p.partition_number;

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

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

Запрос использует type столбец в sys.indexes для возврата только сведений о кластеризованном индексе таблицы или базовой таблицы, если таблица является кучей. Чтобы включить все секционированные некластеризованные индексы в результаты запроса, удалите или закомментируйте AND i.type <= 1 из запроса.

SELECT
    t.[object_id] AS ObjectID
    , SCHEMA_NAME(t.schema_id) AS SchemaName
    , t.name AS TableName
    , ic.column_id AS PartitioningColumnID
    , c.name AS PartitioningColumnName
    , i.name as IndexName
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

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

Следующий запрос возвращает строки по секциям для таблицы PartitionTableи описание операторов сравнения для используемой функции секции. Исходный запрос, предоставленный Кален Деланей (Kalen Delaney).

Запрос использует type столбец в sys.indexes для возврата только сведений о кластеризованном индексе таблицы или базовой таблицы, если таблица является кучей. Чтобы включить все секционированные некластеризованные индексы в результаты запроса, удалите или закомментируйте AND i.type <= 1 из запроса.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, i.name AS IndexName,
    p.partition_number AS PartitionNumber, f.name AS PartitionFunctionName, p.rows AS Rows, rv.value AS BoundaryValue,
CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A'
ELSE
    CASE WHEN f.boundary_value_on_right = 0 AND rv2.value IS NULL THEN '>='
        WHEN f.boundary_value_on_right = 0 THEN '>'
        ELSE '>='
    END + ' ' + ISNULL(CONVERT(varchar(64), rv2.value), 'Min Value') + ' ' +
        CASE f.boundary_value_on_right WHEN 1 THEN 'and <'
                ELSE 'and <=' END
        + ' ' + ISNULL(CONVERT(varchar(64), rv.value), 'Max Value')
END AS TextComparison
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
LEFT JOIN sys.partition_range_values AS rv
    ON f.function_id = rv.function_id
    AND p.partition_number = rv.boundary_id
LEFT JOIN sys.partition_range_values AS rv2
    ON f.function_id = rv2.function_id
    AND p.partition_number - 1= rv2.boundary_id
WHERE
    t.name = 'PartitionTable'
    AND i.type <= 1
ORDER BY t.name, p.partition_number;

Столбец TextComparison описывает возможный диапазон значений в каждой секции на основе определения функции секции. Ниже приведено представление примеров результатов запроса:

Имя схемы TableName IndexName PartitionNumber PartitionFunctionName строк BoundaryValue TextComparison
dbo PartitionTable PK_PartitionTable 1 PFTest 0 2022-03-01 00:00:00.000 >= минимальное значение и < мар 1 2022 12:00AM
dbo PartitionTable PK_PartitionTable 2 PFTest 2 2022-04-01 00:00:00.000 >= 1 марта 2022 12:00AM и < 1 апреля 2022 12:00AM
dbo PartitionTable PK_PartitionTable 3 PFTest 1 2022-05-01 00:00:00.000 >= 1 апреля 2022 12:00 УТРА и < 1 мая 2022 г. 12:00 УТРА
dbo PartitionTable PK_PartitionTable 4 PFTest 0 2022-06-01 00:00:00.000 >= 1 мая 2022 12:00AM и < 1 июня 2022 г. 12:00 УТРА
dbo PartitionTable PK_PartitionTable 5 PFTest 1 2022-07-01 00:00:00.000 >= 1 июня 2022 12:00AM и < 1 июля 2022 12:00AM
dbo PartitionTable PK_PartitionTable 6 PFTest 0 NULL >= 1 июля 2022 12:00AM и < максимальное значение

Ограничения

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

Связанный контент