INDEXKEY PROPERTY: примеры (SQL)

Примеры использования INDEXKEY_PROPERTY

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

CREATE TABLE TestTable (
    ID INT PRIMARY KEY,
    Col1 INT,
    Col2 INT,
    Col3 INT
);
CREATE INDEX IX_Test ON TestTable(Col1 DESC, Col2) INCLUDE (Col3);

Пример 1: Получение ID столбца по позиции в индексе.

SELECT INDEXKEY_PROPERTY(OBJECT_ID('TestTable'), 2, 1, 'ColumnId') AS ColumnID;

ColumnID
4

Пример 2: Проверка направления сортировки для первого ключевого столбца.

SELECT INDEXKEY_PROPERTY(OBJECT_ID('TestTable'), 2, 1, 'IsDescending') AS IsDesc;

IsDesc
1

Пример 3: Проверка, является ли столбец на позиции 3 включенным.

SELECT INDEXKEY_PROPERTY(OBJECT_ID('TestTable'), 2, 3, 'IsIncludedColumn') AS IsIncluded;

IsIncluded
1

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

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

• sys.index_columns: Это системное представление, предоставляющее более полную информацию о столбцах индексов, включая порядок, направление сортировки и тип. Оно часто предпочтительнее для сложных запросов, так как позволяет делать соединения с другими системными представлениями.
• INDEX_COL(): Функция возвращает имя столбца индекса по его позиции. Удобна для быстрого получения имен столбцов, но не дает информации о свойствах, таких как направление сортировки.

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

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

Концепция получения метаданных об индексах существует во всех основных СУБД, но реализуется через системные представления или специфичные функции.

MySQL / MariaDB

SHOW INDEX FROM TableName;

Результат – таблица с информацией, включая порядок столбцов (Seq_in_index) и сортировку (Collation).

PostgreSQL

SELECT * FROM pg_indexes WHERE tablename = 'tablename';
-- Детальная информация о столбцах индекса доступна через pg_index и pg_attribute.

Oracle

SELECT column_name, descend FROM user_ind_columns 
WHERE index_name = 'INDEX_NAME';

Поле descend указывает направление сортировки (ASC/DESC).

SQLite

PRAGMA index_info('index_name');

Возвращает список столбцов индекса, но без информации о направлении сортировки.

Отличия: В отличие от INDEXKEY_PROPERTY, большинство других СУБД используют запросы к системным каталогам, а не отдельную функцию.

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

Ошибки часто возникают из-за неверных идентификаторов или несуществующих свойств.

Пример 1: Использование несуществующего ID индекса.

SELECT INDEXKEY_PROPERTY(OBJECT_ID('TestTable'), 999, 1, 'ColumnId');

NULL

Пример 2: Запрос свойства IsIncludedColumn для позиции, превышающей количество столбцов в индексе.

SELECT INDEXKEY_PROPERTY(OBJECT_ID('TestTable'), 2, 10, 'IsIncludedColumn');

NULL

Пример 3: Опечатка в имени свойства.

SELECT INDEXKEY_PROPERTY(OBJECT_ID('TestTable'), 2, 1, 'ColumnID'); -- 'ColumnId' с маленькой d

NULL

История изменений функции

Функция INDEXKEY_PROPERTY была представлена в SQL Server 2000 и с тех пор ее синтаксис и поведение остаются стабильными. В последних версиях SQL Server (2012 и новее) не было внесено существенных изменений в работу этой функции.

Основные изменения в экосистеме SQL Server связаны с появлением новых типов индексов (например, columnstore), для которых поведение функции может отличаться или быть неопределенным. Для таких индексов рекомендуется использовать специализированные системные представления, такие как sys.column_store_segments.

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

Пример 1: Динамический SQL для получения полной информации о всех индексах таблицы.

DECLARE @TableName NVARCHAR(128) = 'TestTable';
SELECT 
    i.name AS IndexName,
    ic.key_ordinal AS Position,
    COL_NAME(ic.object_id, ic.column_id) AS ColumnName,
    INDEXKEY_PROPERTY(ic.object_id, ic.index_id, ic.key_ordinal, 'IsDescending') AS IsDescending,
    INDEXKEY_PROPERTY(ic.object_id, ic.index_id, ic.key_ordinal, 'IsIncludedColumn') AS IsIncludedColumn
FROM 
    sys.indexes i
JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE 
    i.object_id = OBJECT_ID(@TableName)
ORDER BY 
    i.index_id, ic.key_ordinal;

IndexName Position ColumnName IsDescending IsIncludedColumn
IX_Test    1        Col1       1            0
IX_Test    2        Col2       0            0
IX_Test    3        Col3       NULL         1

Пример 2: Определение, можно ли удалить столбец из таблицы, проверив его участие в индексах.

DECLARE @ColumnName SYSNAME = 'Col1';
DECLARE @TableName SYSNAME = 'TestTable';
IF EXISTS (
    SELECT 1 
    FROM sys.indexes i
    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE i.object_id = OBJECT_ID(@TableName) 
    AND c.name = @ColumnName
    AND INDEXKEY_PROPERTY(ic.object_id, ic.index_id, ic.key_ordinal, 'IsIncludedColumn') = 0 -- Ключевой столбец
)
    PRINT 'Столбец является ключевым в индексе, удаление может повлиять на производительность.';
ELSE
    PRINT 'Столбец не является ключевым в индексах или отсутствует в них.';

Пример 3: Сравнение направления сортировки в двух индексах.

SELECT 
    CASE 
        WHEN INDEXKEY_PROPERTY(OBJECT_ID('TestTable'), 2, 1, 'IsDescending') = 1 
        THEN 'Индекс использует сортировку по убыванию для первого столбца.'
        ELSE 'Индекс использует сортировку по возрастанию для первого столбца.'
    END AS SortInfo;