Табличные переменные в Microsoft SQL Server

В Microsoft SQL Server есть особый тип данных TABLE, на основе которого мы можем создавать табличные переменные, для того чтобы использовать их в своих инструкциях и процедурах, и сегодня мы с Вами рассмотрим эти переменные, узнаем, как они объявляются и какие у этих переменных особенности.

Описание табличных переменных MS SQL Server

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

Переменные такого типа предназначены в качестве альтернативы временным таблицам. Если говорить о том, что лучше использовать табличные переменные или временные таблицы, то однозначного ответа нет, у табличных переменных есть и плюсы, и минусы. Например, лично мне нравиться использовать табличные переменные, потому что их удобно создавать (т.е. объявлять) и не нужно думать об их удалении или очищение в конце инструкции, так как они автоматически очищаются (как и обычные переменные). Но при этом табличные переменные лучше использовать только тогда, когда Вы собираетесь хранить в них небольшой объём данных, в противном случае рекомендуется использовать временные таблицы.

Заметка! Обзор Azure Data Studio. Что это за инструмент и для чего он нужен.

Преимущества табличных переменных в Microsoft SQL Server

• Табличные переменные ведут себя как локальные переменные. Они имеют точно определенную область применения;
• Табличные переменные автоматически очищаются в конце инструкции, где они были определены;
• При использовании табличных переменных в хранимых процедурах повторные компиляции происходят реже, чем при использовании временных таблиц;
• Транзакции с использованием переменных TABLE продолжаются только во время процесса обновления соответствующей переменной. За счет этого табличные переменные реже подвергаются блокировке и требуют меньше ресурсов для ведения журналов регистрации.

Недостатки табличных переменных в MS SQL Server

• Запросы, которые изменяют переменные TABLE, не создают параллельных планов выполнения запроса;
• Переменные TABLE не имеют статистики распределения и не запускают повторных компиляций, поэтому рекомендуется использовать их для небольшого количества строк;
• Табличные переменные нельзя изменить после их создания;
• Табличные переменные нельзя создавать путем инструкции SELECT INTO;
• Переменные TABLE не изменяются в случае откатов транзакций, так как имеют ограниченную область действия и не являются частью постоянных баз данных.

Примеры использования табличных переменных в Microsoft SQL Server

Сейчас давайте перейдем к практике, и для начала хотелось бы отметить, что в качестве сервера у меня выступает Microsoft SQL Server 2016 Express, другими словами все запросы ниже запускались на данной версии СУБД.

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

   
        CREATE TABLE TestTable(
                        ProductId INT IDENTITY(1,1) NOT NULL,
                        ProductName VARCHAR(50) NULL
                CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ProductId ASC)
        )
        GO
        INSERT INTO TestTable (ProductName)
                VALUES ('Компьютер'),
                           ('Монитор'),
                           ('Принтер')
        GO
        SELECT * FROM TestTable

С помощью инструкции CREATE TABLE я создал таблицу TestTable, затем для добавления данных в таблицу я использовал инструкцию INSERT совместно с конструктором табличных значений VALUES, затем с помощью SELECT сделал выборку из только что созданной таблицы.

Объявление табличной переменной и ее использование

В данном примере мы объявим табличную переменную, добавим в нее данных, и сделаем выборку из двух таблиц (табличной переменной и обычной таблицы) с объединением.

   
        --Объявление табличной переменной
        DECLARE @TableVar TABLE(ProductId INT NOT NULL,
                                                        Price MONEY NULL);
        --Добавление данных в табличную переменную
        INSERT INTO @TableVar (ProductId, Price)
                        VALUES (1, 500),
                                   (2, 300),
                                   (3, 200)
        --Использование табличной переменной с объединением данных              
        SELECT TTable.ProductId, TTable.ProductName, TVar.Price 
        FROM @TableVar TVar
        LEFT JOIN TestTable TTable ON TVar.ProductId = TTable.ProductId

Заметка! XACT_ABORT в T-SQL – что это такое и как использовать.

Создание табличной переменной с первичным ключом, ограничением UNIQUE и с некластеризованным индексом

В данном примере показано, как можно создавать первичный ключ, ограничения UNIQUE и некластеризованные индексы для табличных переменных. Возможность создания некластеризованного индекса появилась, начиная с версии Microsoft SQL Server 2014.

  
  --Объявление табличной переменной
  DECLARE @TableVar TABLE(ProductId INT NOT NULL PRIMARY KEY, --Первичный ключ
                                                  ProductName VARCHAR(50) NOT NULL,
                                                  Price MONEY NOT NULL,
                                                  UNIQUE (ProductName, Price), --Ограничение
                                                  INDEX IX_TableVar NONCLUSTERED (Price) --Некластеризованный индекс
                                                );
  --Добавление данных в табличную переменную
  INSERT INTO @TableVar (ProductId, ProductName, Price)
                  VALUES (1, 'Компьютер', 500),
                         (2, 'Монитор', 300),
                         (3, 'Принтер', 200);
  --Выборка данных
  SELECT ProductName
  FROM @TableVar
  WHERE Price > 200

На этом мой рассказ о табличных переменных закончен, если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server, надеюсь, материал был Вам полезен, пока!