Ms sql declare table: The Table Variable in SQL Server

Использование табличных переменных — видеоруководство по SQL Server

Из курса: Разработка баз данных Microsoft SQL Server 2016

Разблокируйте полный курс сегодня

Присоединяйтесь сегодня, чтобы получить доступ к более чем 21 300 курсам, которые преподают отраслевые эксперты, или приобретите этот курс отдельно.

Начать бесплатную пробную версию на 1 месяц

Использовать табличные переменные

“

— [Инструктор] Временные таблицы — хороший способ хранения непостоянных табличных данных во время одного пользовательского сеанса, но иногда даже они существуют намного дольше, чем требуется. Если вам нужна таблица только на короткое время, рассмотрите возможность использования вместо нее табличной переменной. Табличные переменные создаются, используются и уничтожаются за одну пакетную операцию. Их невозможно использовать для нескольких обращений к базе данных. Позвольте мне показать вам, как это выглядит. Во-первых, мы собираемся создать новую базу данных с именем TableVariables, которую мы будем использовать, а затем перейдем к этому оператору DECLARE. Я собираюсь создать таблицу для хранения информации об авторе в виде табличной переменной. Это будет немного отличаться от стандартного оператора CREATE table. Вместо создания объекта мы объявляем переменную. Как и при создании временной таблицы, мы добавим специальный символ в начало имени, чтобы обозначить, что это будет табличная переменная. Где временные таблицы используют хэш или фунт…

Содержание

• • Добро пожаловать

    42 с

  • Использование файлов упражнений

    40 с

  • Что следует знать

    37 с

  • Установить образец базы данных

    3 м 15 с

• • Дизайн таблицы в SQL Server

    4 м 31 с

  • Типы данных

    3 м 20 с

  • Создание и изменение таблиц

    7 м 28 с

  • Реализовать схемы таблиц

    6 м 21 с

  • Вычислить столбцы таблицы

    6 м 40 с

• • Разделение больших таблиц данных

    9 м 3 с

  • Создание временных таблиц

    4 м 38 с

  • Использовать табличные переменные

    4 м 41 с

  • Понимание сортировки столбцов

    7 м 9 с

  • Основы сжатия данных

    3 м 57 с

  • Сжать таблицы данных

    3 м 40 с

  • Вернитесь назад во времени с временными таблицами

    5 м 4 с

• • Что такое просмотры?

    2м 18с

  • Создать представление данных

    7 мин 19 с

  • Повышение производительности просмотра

    4 мин 53 с

• • Понимание целостности данных

    2 м 4 с

  • Реализовать целостность домена

    2 м 45 с

  • Обеспечение ссылочной целостности

    2 м 26 с

  • Использовать триггеры DML для проверки данных

    4 м 49 с

• • Определение ролей и разрешений пользователей

    3 м 56 с

  • Добавить пользователя в роль с помощью T-SQL

    4 мин 49 с

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

    5 м 21 с

  • Реализовать схему безопасности

    4 м 3 с

• • Сохранение пространственных данных

    6 м 12 с

  • Добавить данные GUID в таблицу

    4 мин 3 с

  • Реализовать тип данных XML

    4 м 50 с

  • Сохранение данных BLOB в таблице

    3 м 19 с

  • Реализовать доступ к FILESTREAM

    6 м 48 с

  • Неявные и явные преобразования данных

    6 м 22 с

• • Введение в хранимые процедуры

    2 м 55 с

  • Написать хранимую процедуру с помощью T-SQL

    4 м 37 с

  • Добавить параметры к хранимой процедуре

    4 м 35 с

  • Перекомпилировать хранимую процедуру

    2 м 13 с

• • Зачем использовать таблицы в памяти?

    2 м 1 с

  • Создать оптимизированную для памяти таблицу

    6 мин 11 с

  • Написать собственную скомпилированную хранимую процедуру

    5 м 17 с

• • Основы настольного указателя

    3 м

  • Кластерные индексы

    2 м 18 с

  • Некластеризованные индексы

    2 м 19 с

  • Одноколоночные и составные индексы

    5 мин 24 с

  • Разработка пространственных и XML-индексов

    2 м 45 с

• • Улучшить запросы с покрывающими индексами

    5 м 6 с

  • Получить статистику индекса

    4 м 11 с

  • Отфильтрованные индексы

    3 м 43 с

  • Создать индексы columnstore

    2 м

• • Знакомство с разреженными столбцами

    2 м 6 с

  • Реализовать разреженные колонны

    3 м 24 с

• • Индексы дефрагментации

    4 м 16 с

  • Анализ планов выполнения и настройка запросов

    4 м 27 с

  • Использование советника по настройке SQL

    3 м 34 с

• • Обзор функций SQL Server

    1 мин 37 с

  • Возвращаемые значения со скалярными функциями

    6 м 2 с

  • Создать табличную функцию

    3 м 1 с

• • Использовать динамические представления управления

    1 мин 43 с

  • Запись расширенных сеансов событий

    5 м 38 с

  • Поиск узких мест с помощью Activity Monitor

    2 м 20 с

• • Следующие шаги

    46 с

3 Важные факты для разработчиков о табличной переменной SQL Server

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

Во-первых, давайте разберемся, как использовать табличную переменную. Это очень просто, как показано ниже:

 DECLARE @Employees TABLE

(

Идентификатор сотрудника INT,

Имя VARCHAR(30),

Фамилия VARCHAR(30),

Пол CHAR(1)

) 

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

Область действия и время жизни транзакции

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

 УСТАНОВИТЬ NOCOUNT ON

DECLARE @Employee TABLE (Id INT)

CREATE TABLE #Employee (ID INT)



НАЧАТЬ ТРАН



ВСТАВЬТЕ # Сотрудник

ЗНАЧЕНИЯ(1),(2)



ВСТАВЬТЕ @Employee

ЗНАЧЕНИЯ(1),(2)



/*Оба имеют 2 строки*/

ВЫБЕРИТЕ Id AS '#Table_ID' ОТ #Employee

ВЫБЕРИТЕ Id AS '@Table_ID' ИЗ @Employee



ОТКАТ



ПЕЧАТЬ 'ПОСЛЕ ОТКАТА'

ПЕЧАТЬ '================'

/*Теперь только табличная переменная имеет строки*/

ВЫБЕРИТЕ Id AS '#Table_ID' ОТ #Employee

ВЫБЕРИТЕ Id AS '@Table_ID' ИЗ @Employee

УДАЛИТЬ ТАБЛИЦУ # Сотрудник 

Вот результат:

Табличные переменные доступны только в пределах пакета или области, в которой они объявлены.

 УСТАНОВИТЬ NOCOUNT ON
DECLARE @Employee TABLE (Id INT)
CREATE TABLE #Employee (ID INT)

ВСТАВЬТЕ # Сотрудник
ЗНАЧЕНИЯ(1),(2)

ВСТАВЬТЕ @Employee
ЗНАЧЕНИЯ(1),(2)

/*Оба имеют 2 строки*/
ВЫБЕРИТЕ Id AS '#Table_ID' ОТ #Employee
ВЫБЕРИТЕ Id AS '@Table_ID' ИЗ @Employee
ИДТИ

-- GO является пакетным разделителем по умолчанию в SSMS. 

ВЫБЕРИТЕ Id AS '#Table_ID' ОТ #Employee
ВЫБЕРИТЕ Id AS '@Table_ID' FROM @Employee - это не удастся, так как он находится в новой партии
 

Приведенный выше пример приведет к появлению следующего сообщения об ошибке:

 Сообщение 1087, уровень 15, состояние 2, строка 19.

Необходимо объявить табличную переменную "@Employee" 

Статистика и индексы

Табличные переменные не допускают ALTER к определению после их объявления. А до SQL Server 2014 они не позволяли создавать некластеризованные индексы.

Мы также должны иметь в виду, что оценка количества строк может отличаться от фактических данных в таблице.

Вот скрипт, который вы можете запустить, чтобы увидеть эффект.

 ИСПОЛЬЗОВАТЬ временную базу данных

ИДТИ

ЕСЛИ OBJECT_ID('PermanentEmployeeTable') НЕ NULL

УДАЛИТЬ ТАБЛИЦУ

ИДТИ

CREATE TABLE PermanentEmployeeTable (EmployeeID INT)

ИДТИ

СОЗДАТЬ ИНДЕКС idx_PermanentEmployeeTable ON PermanentEmployeeTable (EmployeeID)

ИДТИ

--вставить 100000 строк в таблицу PermanentEmployeeTable

УСТАНОВИТЬ NOCOUNT ON

НАЧАТЬ СДЕЛКУ

ОБЪЯВИТЬ @loop INT

УСТАНОВИТЬ @цикл = 0

ПОКА @цикл < 100000

НАЧИНАТЬ

ВСТАВИТЬ В таблицу PermanentEmployeeTable

ЦЕННОСТИ (@loop)

УСТАНОВИТЬ @цикл = @цикл + 1

КОНЕЦ

СОВЕРШИТЬ ТРАНЗАКЦИЮ

ИДТИ

--обновить статистику с полным сканированием

ОБНОВЛЕНИЕ СТАТИСТИКИ PermanentEmployeeTable С ПОЛНЫМ СКАНИРОВАНИЕМ



УСТАНОВИТЬ NOCOUNT ON

DECLARE @EmployeeTableVariable TABLE (EmployeeID INT)

НАЧАТЬ СДЕЛКУ

ОБЪЯВИТЬ @loop INT

УСТАНОВИТЬ @цикл = 0

ПОКА @цикл < 100000

НАЧИНАТЬ

ВСТАВЬТЕ В @EmployeeTableVariable

ЦЕННОСТИ (@loop)

УСТАНОВИТЬ @цикл = @цикл + 1

КОНЕЦ

СОВЕРШИТЬ ТРАНЗАКЦИЮ

ВКЛЮЧИТЬ ПРОФИЛЬ СТАТИСТИКИ

ВЫБИРАТЬ *

ОТ @EmployeeTableVariable tv

ВНУТРЕННЕЕ СОЕДИНЕНИЕ PermanentEmployeeTable t ON tv. EmployeeID = t.EmployeeID

ИДТИ

ВЫКЛЮЧИТЬ ПРОФИЛЬ СТАТИСТИКИ 

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

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

 ВЫБЕРИТЕ *

ОТ @EmployeeTableVariable tv

ВНУТРЕННЕЕ СОЕДИНЕНИЕ PermanentEmployeeTable t ON tv.EmployeeID = t.EmployeeID ОПЦИЯ (ПЕРЕКОМПИЛИРОВАНИЕ)
 

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

Это еще один миф о табличных переменных: если табличные переменные имеют меньшие наборы данных, то они будут только резидентными. Эта информация неверна, и вот быстрое доказательство.

 DECLARE @TableVariable TABLE (Column1 INT)
ВСТАВИТЬ В @TableVariable VALUES(1),(2)
ВЫБЕРИТЕ sys.