четверг, 10 марта 2011 г.

T-SQL: когда индекс на table variable очень нужен

В мире T-SQL живут и сотрудничают две очень похожие и в тоже время очень разные вещи: табличные переменные (table variables) и временные таблицы (temporary tables). На форумах часто можно встретить темы вида «Что мне лучше использовать и в чем разница?». Но про это я сейчас не расскажу ;)

Тема поста про то как ускорить сканирование табличной переменной, а точнее совсем от него избавиться. Предположим что у вас есть временная таблица (да, начнем с неё). Что делать если она большая и во время выборки по условию происходит сканирование таблицы, которое занимает приличное время. Конечно вы создадите необходимый индекс, сравните время затрачиваемое на создание индекса и на сканы, и решите оставить индекс и быстро сканировать или же убрать индекс и быстро заполнять таблицу.

А что делать если такое необходимо сделать с табличной переменной? Все кто пытался хоть раз это сделать знают, что на табличной переменной можно создать тока primary key и нельзя создать индекс. Но если очень хочется, то индекс создать можно :)

Если вы попытаетесь создать индекс напрямую, движок БД недвусмысленно ответит вам, что нельзя на табличных переменных создавать индексы. Давайте подумает что еще у нас ведет себя как индекс, выглядит как индекс, но не индекс? Конечно же это unique constraint. В MS SQL этот тип constraint реализуется как индекс (да и в большинстве других РСУБД тоже). Так что же нам мешает создать unique constraint на таб
личной переменной и наслаждаться жизнью? Абсолютно ничего не мешает:

 







Поле Id включено в unique constraint потому что комбинация полей может повторяться. Уникальность должна быть уникальной.
Смотрим план запроса:






Index Seek — что можно еще желать?

2 комментария:

  1. 1. Для произвольного поля (не уникального) можно делать так:
    DECLARE @Table TABLE (
    Field1 VARCHAR(50),
    idRow INT PRIMARY KEY NOT NULL IDENTITY(1, 1),
    UNIQUE(Field1, idRow))
    2. В табличных функциях может как ускорить, так и затормозить (особенно, если результат(с этим индексом) будет джойниться с таблицей, в которой уже есть индекс).

    ОтветитьУдалить
  2. Супер! Спасибо огромное!

    ОтветитьУдалить