Временные таблицы sql @tmp vs #tmp
В чем разница между двумя типами временных таблиц @tmp vs #tmp в SQL 2005? и их другие типы, о которых я не знаю?
Спасибо
5 ответов
#tmp является временной таблицей и в основном действует как настоящая таблица. Может иметь индексы, может иметь статистику, участвовать в транзакциях, оптимизатор вырабатывает правильные оценки строк
@tmp переменная таблицы Без индексов, без статистики, без учета транзакций, оптимизатор всегда предполагает ровно 1 строку
В противном случае они оба ограничены (немного различаются) в памяти / кеше, но контекст - это tempdb, но если он будет слишком большим, он попадет на диск и т.д.
Редактировать:
О ключах на табличных переменных. Они не имеют значения. Там нет статистики и предполагается один ряд. Это изменит сканирование таблицы на сканирование кластерного индекса, которое будет таким же. Проверьте любой план запроса и предполагаемые строки.
Кроме того, просто прочитайте это. Прочитайте, какая разница между временной таблицей и табличной переменной
Первым делом я поместил первичный ключ в табличную переменную @ComputersToProcess. Это превратило сканирование таблицы в сканирование кластерного индекса, но ничего не сделало для производительности.
См. http://support.microsoft.com/kb/305977:
Табличные переменные имеют следующие преимущества перед временными таблицами:
- Как упоминалось в статье "Таблицы" электронной документации по SQL Server, табличные переменные, такие как локальные переменные, имеют четко определенную область действия, в конце которой они автоматически очищаются.
- Табличные переменные приводят к меньшему количеству перекомпиляций хранимой процедуры по сравнению с временными таблицами.
- Транзакции с табличными переменными выполняются только на время обновления табличной переменной. Поэтому для табличных переменных требуется меньше ресурсов блокировки и ведения журнала. Поскольку переменные таблицы имеют ограниченную область действия и не являются частью постоянной базы данных, откаты транзакций не влияют на них.
Вот некоторые недостатки по сравнению с временными таблицами:
- Некластеризованные индексы нельзя создавать для табличных переменных, кроме системных индексов, которые создаются для ограничения PRIMARY или UNIQUE. Это может повлиять на производительность запроса по сравнению с временной таблицей с некластеризованными индексами.
- Табличные переменные не поддерживают статистику, как временные таблицы. Статистика не может быть создана для табличных переменных с помощью автоматического создания или с помощью оператора CREATE STATISTICS. Следовательно, для сложных запросов к большим таблицам отсутствие статистики может помешать оптимизатору определить лучший план для запроса, что повлияет на производительность этого запроса.
- Определение таблицы нельзя изменить после первоначального оператора DECLARE.
- Переменные таблиц нельзя использовать в операторе INSERT EXEC или SELECT INTO.
- Ограничения CHECK, значения DEFAULT и вычисляемые столбцы в объявлении типа таблицы не могут вызывать пользовательские функции.
- Нельзя использовать инструкцию EXEC или хранимую процедуру sp_executesql для запуска динамического запроса SQL Server, который ссылается на переменную таблицы, если переменная таблицы была создана вне оператора EXEC или хранимой процедуры sp_executesql. Поскольку на табличные переменные можно ссылаться только в их локальной области видимости, оператор EXEC и хранимая процедура sp_executesql будут находиться за пределами области видимости табличной переменной. Однако вы можете создать табличную переменную и выполнить всю обработку внутри оператора EXEC или хранимой процедуры sp_executesql, поскольку тогда локальная область действия табличных переменных находится в операторе EXEC или хранимой процедуре sp_executesql.
Являются ли табличные переменные структурами только для памяти, которые гарантируют лучшую производительность по сравнению с временными или постоянными таблицами, потому что они хранятся в базе данных, которая находится на физическом диске?
- Табличная переменная не является структурой только для памяти. Поскольку табличная переменная может содержать больше данных, чем может поместиться в памяти, она должна иметь место на диске для хранения данных. Табличные переменные создаются в базе данных tempdb аналогично временным таблицам. Если память доступна, переменные таблицы и временные таблицы создаются и обрабатываются в памяти (кэш данных).
Для практического сравнения производительности, см. Также:
Просто добавляю на существующие ответы. Там на самом деле 3 типа временных таблиц. В дополнение к другим ответам вы можете создавать глобальные временные таблицы, например ##globalTempTable
Они видимы для всех подключений к серверу sql и используются редко, однако полезно отметить, что они действительно существуют.
Вот хорошее прочтение о разнице между стандартными и глобальными временными таблицами http://www.codeproject.com/KB/database/TempTable.aspx
@tmp относится к переменной таблицы типов, хранящейся в памяти, тогда как #tmp относится к таблице в TEMP база данных.
Существует еще один тип "временной таблицы", если вы используете CTE, это похоже на создание временной таблицы.