Создание индекса на переменной таблицы в SQL Server 2000

Быстрый ответ

Чтобы создать индекс в табличной переменной, используется DECLARE в SQL Server 2014 и более новых версиях. Пример кода приведен ниже:

DECLARE @TempTable TABLE (
    ID INT,
    Data VARCHAR(100),
    INDEX IDX_Data NONCLUSTERED (Data)
);

В данном случае для табличной переменной @TempTable был создан некластерный индекс IDX_Data на основе колонки Data. Подобное действие улучшит производительность запросов, производящих фильтрацию или сортировку данных по этому полю.

Индексация в SQL Server 2000 до 2012: уникальные и первичные ключи

В версиях SQL Server 2000-2012 нельзя создавать неуникальные индексы напрямую, но возможно применение первичных ключей или уникальных ограничений, которые автоматически формируют индексы:

DECLARE @TempTable TABLE (
    ID INT PRIMARY KEY, -- Создает кластерный индекс
    Data VARCHAR(100) UNIQUE -- Создает некластерный индекс
);

PRIMARY KEY создает кластерный индекс, в то время как UNIQUE — некластерный индекс. Чтобы добавить некластерный индекс без ограничений уникальности, используйте столбец с IDENTITY, который выступит в роли "уникального элемента".

Как создать некластерный индекс без уникальности? Выход есть

В более ранних версиях SQL Server для создания некластерного индекса без уникальности вы можете применить трюк с добавлением столбца IDENTITY, который будет использован как артефактный первичный ключ:

DECLARE @TempTable TABLE (
    ID INT IDENTITY(1,1),
    Data VARCHAR(100),
    UNIQUE CLUSTERED(ID, Data) -- Кластерный индекс, обеспечивающий уникальность
);

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

Временные таблицы в игре

Табличные переменные не всегда оптимальный выбор из-за отсутствия статистики, что может негативно сказаться на оптимизации запросов. Более предпочтительно применять временные таблицы (#temp), которые поддерживают полнофункциональное создание индексов и предлагают все преимущества статистики.

CREATE TABLE #Temp (
    ID INT PRIMARY KEY,
    Data VARCHAR(100)
);

CREATE NONCLUSTERED INDEX IDX_Data ON #Temp (Data); -- Дополнительное индексирование с учетом статистики

Уникальные ограничения и вопрос о NULL

Работа с уникальными ограничениями может столкнуться с проблемами из-за специфики обработки SQL Server значений NULL. В этой системе каждое значение NULL считается уникальным, что противоречит стандарту SQL.

SQL Server 2014+: встроенное создание неуникальных индексов

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

Визуализация

Для наглядного представления процесса можно воспользоваться следующей аналогией:

Представьте себе библиотеку (📚) полную книг (📖), но без каталога...

Поиск книги о 'SQL' 🧐:
- Без индекса: [🔍📖🕒📖🕓📖🕔]
- С индексом: [🔍🗂️📖⚡]

Создание индекса для табличной переменной аналогично составлению каталога в библиотеке. Это существенно ускоряет поиск данных (книг)!

Продвинутые советы для опытных разработчиков

Динамический SQL для обхода проблем с именами индексов

Сложности с именами индексов в tempdb можно обойти с помощью использования динамического SQL:

EXEC sp_executesql N'CREATE INDEX IDX_Dynamic ON #TempDynamic (DataColumn)'; -- Создание индекса с помощью динамического запроса

Фильтрованные индексы

В SQL Server 2016 и более новых версиях можно создать фильтрованные индексы, которые охватывают определенный набор данных:

CREATE INDEX IDX_FilteredData ON #Temp (Data) WHERE Data IS NOT NULL; -- Фильтрованный индекс направлен на повышение производительности

Такой подход может существенно улучшить производительность в подходящих для этого контекстах.

Когда следует рассмотреть использование табличных переменных?

Табличные переменные стоит выбирать, если:

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

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

Полезные материалы