В чем разница объявления таблицы через @ и через # в ms sql?

Question

[e1s]

В скрипте встретилось вот такое объявление переменной

declare @tPayment table (typeId int)
	insert	@tPayment (typeId)

Как я понимаю создается временная таблица, но в чем разница по сравнению с объявлением временной таблицы через #? Никак не могу понять, есть какие-нибудь тонкости при таком объявлении?

Answer 1

[Иван Филатов]

когда создаете через # то таблица создается в БД - TempDb
и она не совсем временная - т.е. она физически есть. там можно добавить всякие объекты типо индексов, связи с другими временными таблицами. и т.д. и насколько я помню - для текущего соединения можно к ней обращаться из других скриптов. а есть еще ## - там создается глобальная таблица, доступна из других соединений. вроде такая есть возможность.

а когда через @ - то это просто переменная, временная, которая обнулится когда закончится выполнятся скрипт.
там только данные можно хранить. простая вещь.

Comments

[e1s]

Спасибо, т.е. если через #, то я могу обратиться к этой таблицы из другого скрипта? Влияют ли учетные записи к доступу к TempDb?

[Иван Филатов]

e1s: дополнил ответ

[Иван Филатов]

e1s: вообще проектируйте БД так, чтобы не связывыватся с кучей временных # таблиц. запутаетесь кто создает их, кто пишет, а кто читает. Создавайте физическую таблицу если она такая нужная.

Answer 2

[Артём Каретников]

e1s: через ## - тогда будет видна другим. Однако использование временных таблиц такого рода часто говорит о том, что ее просто надо сделать постоянной. Надо понимать, что создание и удаление таблиц - ресурсоемкое дело и лучше бы их не использовать, кроме тех мест, где это реально необходимо.
Зачастую временную таблицу можно заменить запросом типа select myfield from (select id_table from mytable) либо используя cte - последний способ весьма хорош, если подобная "временная таблица" будет использоваться в процедуре часто.

Не стоит так же забывать про индексы на временные таблицы.

Answer 3

[Sergey Syrovatchenko]

# - локальная временная таблица и видима только в текущем соединении
## - глобальная временная таблица и видна во всех соединения
@ - табличная переменная видна в только в текущем батче

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

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