What is the difference between declaring a table with @ and with # in ms sql?

Question

Transact SQL

In the script, there was such a declaration of a variable

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

As I understand it, a temporary table is created, but what is the difference compared to declaring a temporary table through #? I just can not understand, is there any subtleties with such an announcement?

Answer 1

when you create through # then the table is created in the database - TempDb
and it is not quite temporary - i.e. she is physically there. there it is possible to add any objects like indexes, communications with other temporary tables. etc. and as far as I remember - for the current connection, you can access it from other scripts. and there is also ## - a global table is created there, accessible from other connections. there seems to be a possibility.
and when through @ - then it's just a variable, temporary, which will be reset to zero when the script ends.
only data can be stored there. simple thing.

Answer 2

e1s : through ## - then it will be visible to others. However, the use of temporary tables of this kind often suggests that it simply needs to be made permanent. You need to understand that creating and deleting tables is a resource-intensive business and it would be better not to use them, except for those places where it is really necessary.
Often, a temporary table can be replaced with a query like select myfield from (select id_table from mytable) or using cte - the latter method is very good if such a "temporary table" will be used frequently in the procedure.
Do not also forget about indexes on temporary tables.

Answer 3

# - local temporary table and visible only in the current connection
## - global temporary table and visible in all connections
@ - table variable visible only in the current batch
Whoever says it, but on the physical, these are all tables that are created and stored in tempdb . In terms of the difference between them... it really is. First, there are no statistics on table variables and the expected number of rows is always 1. With a large number of rows, the optimizer may eventually generate less than optimal plans. In addition, table variables prevent the optimizer from using a parallel execution plan. They are minimally logged and do not support transactions.
Temporary tables have the same properties as regular tables. Actually, this is the main difference. If you dig deeper, you can still remember a lot of interesting things.