Difference between temprory table and table variable

Question

hi, What is the difference between temeprory table and table variable somewhere i read both are same can anyone help me out .any quick help is appreciable thanks

Akshay Patel · Answer

# [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 @VarTestTable TABLE ( [TestTableID] [int] NOT NULL, [FirstCol] [varchar](200) NULL, [SecondCol] [int] NULL )

Vikrant More · Answer

Sr.No. Temporary Table (#) Table Variable (@) 1] Temporary table can have index Table Variable doesn't have index 2] can use (nolock) can't Use (nolock) 3] Used in query,Sp Used in query,SP,Functions to know more about Temporary table and table variable use this link. http://www.c-sharpcorner.com/UploadFile/d2b5dd/temporary-tables-in-sql3/

Satyapriya Nayak · Answer

Hi Shakuntala, Please refer the below links http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table http://www.codeproject.com/Articles/18972/Temporary-Tables-vs-Table-Variables-and-Their-Effe Thanks

Difference between temprory table and table variable

Answers (3)