Table Variables - Serial plan upon data modification statements and OPTION(RECOMPILE)

Here is something to be aware of:
When you have data modification statements

(like, INSERT,UPDATE,DELETE) against a table variable,
The SQL Server will not use a parallel plan, it will use a serial plan.
This can affect the performance, so keep it in mind.

Although, there is a workaround:
INSERT INTO @Table
EXEC('SELECT .. FROM ...')

Additionally,
Until SQL Server 2019 which has "Table variable deferred compilation" feature,
the estimated number of rows of table variables was always 1.
Why? When a query/SP/batch is compiled and optimized, the table variable is empty.

If you wanted to have proper estimations then you need to use 

OPTION(RECOMPILE) hint or use trace flag 2453, 

so the optimize will use real row count.

Note, OPTION(RECOMPILE) hint will not always work when

you join the table variable to a table and have 1 to many relationship. 

See https://www.sqlservercentral.com/blogs/should-i-use-optionrecompile for more details.

Also, the situation is different when you have some filters on your table variable.
Because there are no statistics on the table variables, 

SQL Server will use the following rules for the estimation of rows:

- When there is equal filter the estimates will be 10 % out of total rows,
- When there are inequality filters, like <, >, <> the estimates will be 30 % out of total rows
- When there is an equal filter on a bit field the estimates will be 50 % out of total rows

Source articles:
https://www.brentozar.com/archive/2017/02/using-trace-flag-2453-improve-table-variable-performance/
https://support.microsoft.com/en-us/help/2952444/fix-poor-performance-when-you-use-table-variables-in-sql-server-2012-o

A table variable is not a memory-only structure

Yep, the word 'Variable' is misleading.

According to Microsoft article:

"A table variable is not a memory-only structure.

Because a table variable might hold more data than

can fit in memory, it has to have a place on disk to store data.

Table variables are created in the TempDB database similar

to temporary tables. If memory is available, both table variables

and temporary tables are created and processed while

in memory (data cache). "

We can easily check this by checking the 'sysobjects'

table in the TempDB:

DECLARE @TestTable TABLE(BookID INT)

SELECT TOP 1 * 

FROM tempdb.sys.sysobjects

WHERE [name] LIKE '#%'

AND [name] NOT LIKE '%[_]%'

ORDER BY crdate DESC

Thanks for reading.

What are the major differences between Temporary Table and Variable Table

First of all, there is no universal rule for when and where to use
temp table or variable table. But using table variables for small
to medium volumes of data considered to be a rule of thumb.
One important thing - the table variable is NOT necessarily
memory resident. Under memory pressure, the pages
belonging to a table variable can be pushed out to tempdb.
Temp tables always stored in TempDB.

OK,lets see the main differences:

1) Table variables are out of scope of the transaction mechanism,
in other words the ROLLBACK is not going to work in case you
changed some data in variable table and rollback the changes.
Also table variables minimally logged and don't participate
in locking. This gives them some performance advantage due
to the fact that they don't require the overhead of locking
and logging.

2) A Stored Procedure containing a temp table will be
almost always recompiled. While s SP with table variable can
be statically compiled in advance.If you have a long SP that take
a while to compile you should consider whether to use the
temp tables.

3)Table variable can have Primary Key and Unique Constraints
defined. And the constraints can not be created in separate DDL
statements,only in table definition.table variable cannot have
regular indexes.The temp table can have all types of indexes.

4)Table variables are variables,this means that table variable
cannot be referenced by dynamic queries (you cannot 'see'
them in EXEC(string) statement) and in inner (sub) stored
procedures. One more limitation: table variables cannot be
used in an insert/exec (in SQL 2000) and 'Select into'
statements. Meaning you cannot insert SP`s result set into
table variable by using insert/exec method (in SQL 2000)
or use Select Into method:
insert into @table
exec sp_xxxxx
or
Select *
into @table
from SomeTable

*** The Insert/exec method can be used with table variables
in SQL 2005 and above.

5)Table variables don't have column statistics,temp tables
do have. In fact, the estimated row number in execution
plan for table variable is always 1. Without statistics,SQL Server
might choose a poor processing plan for a query that contains
a table variable.

6)Parallel query execution plans are not generated for queries
that modify table variables.They generated only for Select
statements that involve table variable(s).

7)In case of table variable the collations for the string
columns inherited from the collation of the current user
database,and in case of temp tables it will take the collation
of TempDB.So in case your database collation is different
then server collation,you should pay attention to COLLATION
issues when using temp tables.

8)Temp tables cannot be used in UDF(user defined function).

9)A table variable can use registered UDTs, user defined data
types, and xml collections in user current database,because
table variable inherits the user current database settings.
A temp table cannot use the types created in the user
database if the same types do not exist in the TempDB,
because it inherits the settings of TempDB.

I hope this will help you decide when and where to use
temp tables or table variables.