Knowledge: Differences between temporary table and table variable.

Temporary Tables vs Table variables.

Temporary Tables	Temporary Table Variables
1) Following is the syntax for creating a temporary table. CREATE TABLE #temp_table (id INT, city VARCHAR(100))	1)Following is the syntax for creating a table variable. DECLARE @temp_table TABLE (id,INT, city VARCHAR(100))
2) All DML & DDL operations works on temporary table like on normal tables. Example - All DDL operations like ALTER, CREATE & DROP operations will work with temporary tables.	2)Table variables doesn't support DDL operations like ALTER, CREATE & DROP operations. It will support DML operations only. Basically once the table variable is created we can't drop or alter the structure of the table.
3) Temporary tables are created in tempdb database.	3) Table variables are also created in tempdb database.
4) Temporary tables support explicit transaction defined by the user.	4) Table variables doesn't support explicit transactions defined by the user.
5) Temporary tables are not allowed in user defined functions.	5) Table variables are always allwoed in user defined functions.
6) Indexes can be created on temporary tables if required.	6) Index creation on temporary variables is not supported.
7) Temporary tables can be created using single # which are referred as local temporary tables and double ## which are referred as global temporary tables.	7) Table variables are created using 'DECLARE' keyword and of type 'TABLE'.
8) The scope of local temporary tables is within the session in which it is created and will be dropped automatically once the session ends or it can be dropped explicitly. The scope of global temporary table is whith the session it created and also can be accessible by any other sessions. These tables can be dropped explicitly by any session if it is not actively being used or it will get dropped auotmatically when no session is using it.	8) The scope of temporary variable is witin the stored procesure it is declared and will be dropped automatically when the procedure execution completes. It can't be dropped explicitly.