Table variable Vs Temporary tables

Table variable can be passed as a parameter to functions and stored procedures while the same cannot be done with Temporary tables.Temporary tables are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine. Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. It is created in the memory database but may be pushed out to tempdb. Use Table variable, if you have less than 1000 rows otherwise go for Temporary tables. 1.Table variable (@table) is created in the memory. Whereas, a Temporary table (#temp) is created in the tempdb database. However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb. 2. Table variables cannot be involved in transactions, logging or locking. This makes @table faster then #temp. So table variable is faster then temporary table. 3. Temporary tables are allowed CREATE INDEXes whereas, Table variables aren’t allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint. 4. Table variable can be passed as a parameter to functions and stored procedures while the same cannot be done with Temporary tables. 5. Temporary tables are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine. 6. Temporary table allows Schema modifications unlike Table variables. There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server. Local Temporary Table Scope If a local temporary table created in a stored procedure, it is dropped automatically when the stored procedure is finished. This means that this local temporary table can be referenced only by nested stored procedures. The local temporary table cannot be referenced by the stored procedure or application that called the stored procedure that created the local temporary table. Global Temporary Tables A global temporary table is created using CREATE TABLE statement with the table name prefixed with a double number sign (##table_name). In SQL Server, global temporary tables are visible to all sessions (connections). So if you create a global temporary table in one session, you can start using it in other sessions.