INDEXPROPERTY: примеры (SQL)

Короткие примеры использования

Пример проверки типа индекса для таблицы Employees.

SELECT INDEXPROPERTY(OBJECT_ID('Employees'), 'PK_EmployeeID', 'IsClustered') AS IsClustered;

IsClustered
-------------
1

Пример получения значения Fill Factor.

SELECT INDEXPROPERTY(OBJECT_ID('Orders'), 'IX_OrderDate', 'IndexFillFactor') AS FillFactor;

FillFactor
----------
0

Пример проверки, является ли индекс уникальным.

SELECT INDEXPROPERTY(OBJECT_ID('Products'), 'AK_ProductNumber', 'IsUnique') AS IsUniqueIndex;

IsUniqueIndex
--------------
1

Пример запроса глубины индекса.

SELECT INDEXPROPERTY(OBJECT_ID('Sales'), 'IDX_Sales_Date', 'IndexDepth') AS Depth;

Depth
-----
3

Пример проверки, отключен ли индекс.

SELECT INDEXPROPERTY(OBJECT_ID('OldData'), 'IX_OldIndex', 'IsDisabled') AS IsDisabled;

IsDisabled
----------
1

Похожие функции в MS SQL

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

SELECT name, type_desc, is_unique, fill_factor
FROM sys.indexes
WHERE object_id = OBJECT_ID('Employees');

sys.stats и sys.stats_columns — представления для работы со статистикой, которая также может быть целью INDEXPROPERTY.

OBJECTPROPERTY и OBJECTPROPERTYEX — функции для получения свойств объектов, но не специфичных для индексов.

INDEXKEY_PROPERTY — функция, возвращающая информацию о ключевых столбцах индекса (например, порядок сортировки).

INDEXPROPERTY удобна для быстрого получения одного конкретного свойства индекса в рамках простого запроса.

Типичные ошибки

Основная ошибка — неверное указание имени объекта или индекса, что приводит к возврату NULL.

-- Таблица не существует
SELECT INDEXPROPERTY(OBJECT_ID('NonExistentTable'), 'SomeIndex', 'IsClustered') AS Result;

Result
------
NULL

Ошибка при передаче имени статистики вместо имени индекса для свойств, неприменимых к статистике.

-- _WA_Sys_00000005_3D5E1FD2 — имя автоматически созданной статистики
SELECT INDEXPROPERTY(OBJECT_ID('Orders'), '_WA_Sys_00000005_3D5E1FD2', 'IsClustered') AS Result;

Result
------
NULL

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

Некорректное имя свойства.

SELECT INDEXPROPERTY(OBJECT_ID('Employees'), 'PK_EmployeeID', 'WrongProperty') AS Result;

Result
------
NULL

Изменения в последних версиях

В SQL Server 2019 было добавлено новое свойство IsOptimizedForSequentialKey, которое возвращает 1, если для индекса включена оптимизация под последовательную вставку в конец (last-page insert optimization).

-- Пример для SQL Server 2019 и выше
SELECT INDEXPROPERTY(OBJECT_ID('BigTable'), 'IX_SequentialID', 'IsOptimizedForSequentialKey') AS IsOptimized;

В более ранних версиях SQL Server набор доступных свойств был меньше. Рекомендуется сверяться с документацией для конкретной версии.

Расширенные примеры применения

Пример динамического SQL для отключения всех некластеризованных индексов в таблице.

DECLARE @TableName NVARCHAR(128) = N'LargeTable';
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 
    'ALTER INDEX ' + QUOTENAME(name) + ' ON ' + QUOTENAME(@TableName) + ' DISABLE; '
FROM sys.indexes
WHERE object_id = OBJECT_ID(@TableName)
    AND INDEXPROPERTY(object_id, name, 'IsClustered') = 0
    AND INDEXPROPERTY(object_id, name, 'IsDisabled') = 0
    AND INDEXPROPERTY(object_id, name, 'IsHypothetical') = 0;

PRINT @sql;
-- EXEC sp_executesql @sql;

Пример анализа индексов таблицы с выводом нескольких свойств сразу.

SELECT 
    i.name AS IndexName,
    i.type_desc AS Type,
    INDEXPROPERTY(i.object_id, i.name, 'IsUnique') AS IsUnique,
    INDEXPROPERTY(i.object_id, i.name, 'IndexFillFactor') AS FillFactor,
    INDEXPROPERTY(i.object_id, i.name, 'IsDisabled') AS IsDisabled,
    INDEXPROPERTY(i.object_id, i.name, 'IndexDepth') AS Depth
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('Sales.SalesOrderHeader');

IndexName               Type        IsUnique FillFactor IsDisabled Depth
----------------------- ----------- -------- ---------- ---------- -----
PK_SalesOrderHeader     CLUSTERED   1        0          0          3
IX_SalesOrderHeader     NONCLUSTERED 0      0          0          3

Пример проверки, является ли объект статистикой, созданной автоматически.

SELECT 
    name AS StatisticName,
    INDEXPROPERTY(object_id, name, 'IsStatistics') AS IsStat,
    INDEXPROPERTY(object_id, name, 'IsAutoStatistics') AS IsAutoStat
FROM sys.stats
WHERE object_id = OBJECT_ID('Production.Product')
    AND INDEXPROPERTY(object_id, name, 'IsStatistics') = 1;

Пример использования в процедуре для принятия решений по обслуживанию на основе fillfactor.

IF INDEXPROPERTY(OBJECT_ID('CriticalTable'), 'IDX_Main', 'IndexFillFactor') > 90
BEGIN
    PRINT 'Индекс имеет высокий Fill Factor. Возможно, требуется перестроение.';
END

Пример запроса, который определяет, разрешены ли блокировки страниц для индекса.

SELECT 
    CASE INDEXPROPERTY(OBJECT_ID('ContendedTable'), 'IX_Column', 'IsPageLockDisallowed')
        WHEN 1 THEN 'Блокировка страниц запрещена'
        WHEN 0 THEN 'Блокировка страниц разрешена'
        ELSE 'Индекс не найден'
    END AS LockInfo;

Альтернативы в других СУБД

MySQL: Информация об индексах доступна через запрос к INFORMATION_SCHEMA.STATISTICS или команду SHOW INDEX.

SHOW INDEX FROM Employees;

Table     Non_unique Key_name Seq_in_column Column_name Collation Cardinality
Employees 0          PRIMARY  1             EmployeeID  A         500

Oracle: Свойства индексов можно получить из представлений USER_INDEXES, ALL_INDEXES, DBA_INDEXES.

SELECT index_name, uniqueness, status FROM user_indexes WHERE table_name = 'EMPLOYEES';

PostgreSQL: Используются системные каталоги pg_index и pg_class, а также представление pg_indexes.

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';

SQLite: Для получения информации об индексах таблицы используется pragma index_info или запрос к sqlite_master.

PRAGMA index_info('idx_email');

Sybase ASE: Поддерживает функцию INDEXPROPERTY с похожим синтаксисом, но набор свойств может отличаться.

В отличие от INDEXPROPERTY, которая возвращает скалярное значение, многие альтернативы в других СУБД возвращают наборы строк.