Сравнение временных таблиц и таблиц переменных.

Существует много путаницы о том, что такое таблицы переменные(Table Variables) и чем они не являются. А так же как они соотносятся с временными таблицами(Temporary Tables). Я хочу внести свою лепту в объяснения различий между двумя этими типами таблиц. И в развенчивании нескольких мифах об этих объектах.

Таблицы переменные

Таблицы переменные впервые появились в SQL Server 2000. Так что такое в действительности таблицы переменные? Microsoft определяет это в BOL как переменная типа таблица. Это определение включает в себя определения колонок, имен, типов данных и ограничений. Возможно использовать только следующие типы ограничений: PRIMARY KEY, UNIQUE, NULL, и CHECK. А FOREIGN KEY недоступен для использования. Таблица определяется с помощью инструкции DECLARE.

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

Таблица переменная может быть использована, в пределах своей области, как и любая другая таблице. В частности, он может быть использован в виде таблицы или табличного выражения везде, где это допустимо. Почти во всех операциях SELECT, а также UPDATE, INSERT, DELETE. Но они не могут быть использованы в выражении SELECT INTO [select_list] table_variable, и в SQL Server 2000, они не могут быть использованы в выражении INSERT INTO table_variable EXEC stored_procedure.

Что вы не можете делать с этими таблицами?

• Хотя переменная table и является переменной, ей не может быть присвоена другая таблица переменная.
• Ограничение CHECK, значения по умолчанию и вычисляемые столбцы в определении таблиц не могут ссылаться на UDF.
• Вы не можете создавать именованные ограничения.
• Вы не можете использовать инструкцию TRUNCATEс такими таблицами.
• Вы не можете вставлять явно определенные значения в столбцы с идентификаторами. То есть не поддерживается SET IDENTITY_INSERT ON.

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

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

Так что такое временная таблица? Просматривая BOL мы можем обнаружить, что временная таблица, создается точно так же как и обычные за исключением

• Имя такой таблице ограничено 116 символами. Это делается для того, что бы ядро базы могло идентифицировать одинаковые временные таблицы, созданные в разных сессиях во одно и тоже время. Для внутренней идентификации ядро добавляет цифровой суффикс к имени таблицы.
• Локальные временные таблицы, имеющие в имени знак #, видимы только для текущего соединения, в точке создания таблицы и внутри вложенных хранимых процедур.
• Такие таблицы будут удалены в одном из тех случаев.
• Явное удаление с помощью инструкции DROP TABLE.
• Если таблица является локальной временной таблицей определенной в хранимой процедуре, то по факту завершения выполнения процедуры.
• Все остальные таблицы будут удалены по факту закрытия сессии.
• Глобальные временные таблицы, имеющие в имени двойной знак ##, видимы для всех сессий. Вы должны всегда проверять существование такой таблицы перед тем как попытаться создать ее, иначе вы получите ошибку дублирования объектов.
• Глобальные временные таблицы удаляются, когда закрывается сессия, в которой они были созданы, а все оставшиеся сессии прекращают ссылаться на таблицу.

• Эта привязка работает на уровне инструкций.
• Временная таблица не может быть секционирован.
• FOREIGN KEY не может быть применен к временным таблицам.
• Столбцы временной таблицы не могут быть определены с помощью UDDT, не определенных в temdb, вы должны использовать нативные типы данных (UDDTs определенные в вашей БД и в tempdb). Так как при старте экземпляра сервера база tempdb пересоздается, вы должны использовать специальные хранимые процедуры для создания этих типов. Альтернативным вариантом может быть изменение базы model.
• Колонки с типом xml не могут быть определены с использованием xml collection, пока коллекция не будет добавлена в tempdb

Временная таблица может быть создана с помощью выражения CREATE TABLE. Или выборкой в таблицу с помощью инструкции SELECT [select_list] INTO #table, вы так же можете использовать предложение INSERT INTO #table EXEC stored_procedure при работе с временными таблицами.

Временные таблицы могут иметь именованные ограничения. Однако, если 2 пользователя выполняют одну и туже процедуру одновременно, при выполнении второй возникнет ошибка There is already an object named [objectname] in the database. В таком случае вы должны будете создавать неименованные ограничения, а ядро бд само даст им уникальные идентификаторы.

Мифы

А теперь самое интересно: самые распространенные мифы о временных таблицах и таблицах переменных.

Мифы № 1 и 2: таблицы переменные находятся только в памяти, а временные таблицы располагаются на диске.

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

Миф № 3: Таблицы переменные не могут иметь индексы.

Это так же ошибочное утверждение. Почему оно возникло? А все дело в том, что если таблица переменная создана, то она не может участвовать ни в каких DDL выражениях. Например в CREATE INDEX. Однако вы можете создать индексы ассоциированные с PRIMARY KEY или UNIQUE, если эти ограничения будут определены как часть определения таблицы. Например

declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

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

Еще немного фактов.

• SQL Server не может строить статистику для таблиц переменных, в отличии от временных таблиц. Это значит что план выполнения для таблицы переменной всегда будет подразумевать, что в таблице всего одна запись. Очевидно, что это обозначает, что сервере не сможет всегда выбирать оптимальный план.
• Как уже упоминалось ранее, вы не можете выполнять любые DDL операции с таблицами переменными. Например, вам необходимо заполнить таблицу, а затем добавить индекс или столбец, но все это можно сделать с временной таблицей.
• Невозможно вставить данные в таблицу переменную с помощью команды SELECT INTO, однако так можно поступить с временной таблицей.
• В SQL Server 2008, вы можете передать табличную переменную в процедуру, если вы определили UDTT и этот тип является параметром процедуры. Но в более ранних версиях SQL Server'a это сделать невозможно.
• Область применения: таблицы переменные видны только в текущем пакете инструкций, и не видны в вызываемых процедурах. Локальные временные таблицы видны в текущей сессии после создания, в том числе вложенных процедур, однако они не будут видны в родительских процедурах. Глобальные временные таблицы видны все сеансы до закрытия сессии в которой таблица была объявлена и прекращения ссылок из других сессий.
• Collation: таблицы переменные используют collation текущей базы данных. Временные таблицы использовать collation база tempdb. Если они не совместимы, то вам ghbltncz указать collation для использования в любом запросе.
• Если вы хотите использовать таблицы переменные в динамическом SQL коде, то вы должны определить таблицу в этом же коде. В то время как временная таблица должна быть просто определена заранее.

Что я должен использовать?

Microsoft рекомендует использовать таблицы переменные, если количество строк, которые вы собираетесь вставлять в таблицу очень мало. Большинство "интернет гуру" скажет вам, что количество записей должно быть меньше 100, так как это то количество, при котором отсутствие статистики начинают снижать производительность запроса - но они также скажут вам протестировать вашу задачу используя оба типа таблиц. Если вы можете использовать индекс полученный при определении PRIMARY KEY или UNIQUE на таблице переменной, то вы можете получить прекрасную производительность с таблицей содержащей десятки тысяч строк. До тех пор пока вы не используете ее с инструкцией join. Когда вы начнете ее джойнить с другой таблицей, отсутствие статистики приведет к большим проблемам с производительностью. Так же если вам понадобится что-то изменить в определении таблицы по ходу пьесы, то сделать это можно будет только с временной таблицей.

Оба типа таблиц служат для достижения похожих целей, поэтому нет какого-то конкретного ответа на вопрос какие таблицы лучше? Каждый случай должен рассматриваться отдельно и исходя из требований и результатов тестов можно будет определить, что нужно использовать в данной ситуации