Difference between temprory table and table variable

# [Temporary Tables]

[Table Variable]

This can be accessed within the declared SP.

The scope of this table will not exists beyond the SP.

This table will be dropped automatically on end of each session.

But it will always be better to drop the table physically using the code

These temp tables can be created with the same name in multiple windows.

Different users can create diff temptable with the same name.

The tables created will be having a unique id for each session.

The table name will be appended with the number allocated for that session.

It will always be better to create indexes on the temp tables and use that within the SPs.

any procedure with a temporary table cannot be pre-compiled

Table variables are just like scalar variables which possess structure of a table and can hold records for intermediate results of different quires. These are the best alternative for temporary tables as there is no need to worry about demolition of table variables after use.

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).

An execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

If we are creating a temp table using the name #tmpRoles, then we can't fetch directly using the name.

we have to write a query like below

SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like '%tmpRoles%'

CREATE TABLE#Temp_TestTable

([TestTableID] [int] NOT NULL,

 [FirstCol] [varchar](200) NULL,

 [SecondCol] [int] NULL

)

GO

-- DROP TABLE #Temp_TestTable

--(Drop temporary table when not required)     

GO

DECLARE@VarTestTableTABLE

(

[TestTableID] [int] NOT   NULL,

[FirstCol] [varchar](200) NULL,

[SecondCol] [int] NULL

)