Why do we need variable tables and when should I use them?

The only minus of temp tables, I think, is that they need to be checked and dropped if they are before creation.

In my opinion, this is a problem only with @@nestlevel equal to 0, and even then not always (for example, if we open a connection, create a # table, do something and close the connection, then there is no need for verification). Inside the module (trigger or procedure), if you need to create #table , you can not check and not drop it (it can be harmful), even if #table with the same name was already created on the external level.

Ie, for example, there is a procedure

 create procedure SomeProc as begin set nocount on; create table #table (id int); insert into #table values (1), (2); select * from #table; end GO 

You can create #table and call SomeProc

 create table #table (value float); insert into #table values (100.0), (200.0); exec SomeProc; -- #table внутри SomeProc - это другая #table select * from #table; drop table #table; GO 

while #table , created outside SomeProc , and #table , created inside SomeProc are different and do not interfere with each other. If inside SomeProc before SomeProc create table #table put the existence test to #table and drop table #table , then the table created at the external level will be destroyed, and the code following the procedure will break.

So, for what purposes they may be needed when there are tempo tables

When choosing between @table and #table , it seems to me, one should proceed from the properties and features of these two types of tables. @ -Tables have quite a lot of limitations compared to # -tables, a lot in common, and there are features specific to @ -tables.

In detail this question is covered here and here . Below is a summary of the information on these two links (incomplete).

Storage: Contrary to the conventional notion that the @ -tables are stored in RAM, and # -tables are in tempdb , there really is no difference between them. Both those and others are stored in tempdb , and, also, those and others are equally buffered.

Scope: # tables are available at the level at which they are created and on nested ones ( @@nestlevel more than the current one). @ -tables are available only within the batch or module in which they are declared (however, typed @ -tables to nested levels can be passed as a parameter).

Life cycle: @ -tables are created implicitly at the very beginning of the batch in which they are declared, before all other commands are executed (and if it is a module, then before the module starts to execute), regardless of whether the declare instruction is reached during execution , or not. Here such code, for example, quite works:

 if 1 != 0 declare @table1 table (id int); else declare @table2 table (name varchar(20)); select * from @table1; select * from @table2; GO 

@ -tables are destroyed after exiting the corresponding batch or module in which they are declared.

# tables are created when the corresponding create table statement is encountered; are destroyed when the corresponding drop table instruction is encountered, either when the nesting level is reduced (exit from the module or exec instruction), or the connection is closed (if the # table was created at the zero nesting level).

Use in functions and procedures: # -tables cannot be created in custom scalar and multi-line table functions, @ -tables can be. Typed @ -tables can be passed to functions and procedures by parameter. # tables cannot be passed to functions (although you can access them indirectly through a synonym). In procedures, # tables created externally are available.

Сollation: When creating @ -tables, columns of string types (if they are present) inherit collation from the current database. When creating # tables, columns of string types inherit collation from tempdb , or from the current database, if that one is contained .

Indices / Keys: Before SqlServer 2014, only unique keys could be set on @ -tables

 declare @table table ( id int primary key, uid uniqueidentifier unique ) 

In SqlServer 2014 with the addition of inline-syntax for creating indexes, it became possible to add non-unique indexes

 declare @table table ( value varchar(20), index ix_1 (value) ) 

In SqlServer 2016 it is possible to add unique and filtered indexes

 declare @table table ( id int, value varchar(20), index ix_1 unique (value), index ix_2 (id) where value is NULL ) 

Indexes with include , as well as special types of indexes on @ -tables are currently not supported.

Compilation of queries: The query optimizer in many cases assumes that the @ -table contains a single row. The @ -tables do not support column statistics.

Transactions: Changing the contents of the @ -tables always occurs in a system transaction, so they are insensitive to rollback .

 declare @table table (value int); create table #table (value int); begin tran; insert into @table values (1); insert into #table values (2); rollback; select * from @table; select * from #table; drop table #table; 

Parallelism: Requests that change the contents of @table (insert / update / delete) cannot use parallelism. Requests that read from @table (select) may have a parallel plan.

Table hints: Table hints can not be used on @ -tables, such a restriction is not imposed on # -tables .

Other: @ -table cannot be created with select * into @table from ... Truncate truncate table , alter table , create index not available for @ -tables.