Temp Table:

Temporary tables, are very similar to the permanent tables. Permanent tables get created in the database you specify, and remain in the database permanently, until you delete (drop) them. On the other hand, temporary tables get created in the TempDB and are automatically deleted, when they are no longer used. These tables are automatically destroyed at the termination of the procedure or session that created them. SQL Server provides the concept of temporary table which helps the developers in a great way. These tables can be created at runtime and can do all kinds of operations that one normal table can do. In SQL Server all temporary tables are present in tempdbdatabase.

Types of Temporary Tables:

SQL Server contain two types of Temporary tables:

  1. Local Temporary Tables
  2. Global Temporary Tables

    Local Temporary Tables:

    Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables start with(“#”) hash sign.Local temporary tables are visible only in the current session, and can be shared between nested stored procedure calls.

    Example:

    1. Create local temp table.  
    2. CREATE TABLE# TEMP_SAVE  
    3.     (  
    4.         Emp_IdINT,  
    5.         Emp_NameVARCHAR(30),  
    6.         Project_NameVARCHAR(30)  
    7.     )  
    8.   
    9. Insert data into temp table.  
    10.   
    11. INSERT INTO # TEMP_SAVE  
    12. SELECT e.Emp_Id, e.EmployeeName, p.Project_NameFROMdbo.Employee e  
    13. INNERJOIN  
    14. dbo.Project p  
    15. ON  
    16. e.Emp_Id = p.Project_Id  


        Global Temporary Tables:

        Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table, then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of Global temp tables start with (“##”) double hash sign. Global temporary tables are visible to other sessions and are destroyed, when the last connection referencing the table is closed. 


Example:

Create a Global temp table
  1. Create Table## TEmp  
  2.     (  
  3.         IIDintidentity(1, 1),  
  4.         Namenvarchar(50),  
  5.         Salaryint  
  6.     )  

When to Use Temporary Tables

  1. We required to hold data from further query.
  2. When we have a complex Join operation.
  3. In some cases it may be a replacement of cursors.


Table Variable:

Table variables are somewhat like temp tables, but table variables are not SQL Server objects like temp tables.  That is, you do not create a table variable with a create statement. 

A table variable is also created in TempDB. The scope of a table variable is the batch, stored procedure, or statement block in which it is declared. 

This acts like a variable and exists for a particular batch of query execution.
It gets dropped once it comes out of the batch.

This is also created in the tempdb database but not the memory.

This also allows you to create a primary key, identity at the time of Table variable declaration but not non-clustered index. They can be passed as parameters between procedures. Table variables are best suited for working with small row sets.

GO
 DECLARE @TProduct TABLE
 (
 SNo INT IDENTITY(1,1),
 ProductID INT,
 Qty INT
 ) 
 --Insert data to Table variable @Product 
 INSERT INTO @TProduct(ProductID,Qty)
 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 
 --Select data
 Select * from @TProduct
 
 --Next batch
 GO
 Select * from @TProduct --gives error in next batch


CTE:

The CTE is an abbreviation of “Common Table Expression.” CTE was introduced in SQL Server. It works as a temporary result set that is defined within the execution scope of a single select, insert, update, delete statements. CTE is typically the result of complex sub queries. Similar to temporary tables CTE doesn’t store as an object; the scope is limited to the current query. CTE improves readability and ease in maintenance of complex queries and sub-queries.

Example:

  1. WITH CTE(Emp_ID, Emp_Name, Project_Name)  
  2. AS  
  3.     (  
  4.         SELECT e.Emp_Id, e.EmployeeName, p.Project_NameFROMdbo.Employee e INNERJOIN dbo.Project p ON e.Emp_Id = p.Project_Id  
  5.     )  
  6. SELECT * FROM CTE  

When to use CTE

  • Use for recursive query.
  • CTE is easy to implement compared to complex queries which involves several sub-queries.

Derived Table:

Derived tables are the tables which are created on the fly with the help of the Select statement. Derived table expression appears in the FROM clause of a query. In derived table server create and populate the table in the memory, we can directly use it and we also don’t require to drop the table. But scope of derived table is limited to the outer Select query who created it. Derived table can’t be used further outside the scope of outer select query.

Example:

  1. SELECT * FROM  
  2. (SELECT e.Emp_Id,e.EmployeeName,p.Project_Name FROM dbo.Employee e  
  3. INNERJOIN  
  4. dbo.Project p  
  5. ON  
  6. e.Emp_Id=p.Project_Id)Tab  
  7. WHERE Tab.Emp_Id%2=1  

When to Use:

  • When you are required to perform some complex query and result of inner query work as table for outer query.

Subquery:

A subquery is a SELECT statement that is nested within another statement. Subquery is also known as nested query. Result of subquery is used in the main query as a condition to further restrict the data to be retrieved. The subquery used in select, update, delete and insert command.

Example:

  1. SELECT * FROM dbo.Emp e  
  2. WHERE e.Id IN(SELECT e2.Emp_Id fromdbo.Employee e2);  

When to use:

  • When you require that inner query or condition must be fulfilled before generating any result.
  • Use subquery when the result that you want requires more than one query and each subquery provides a subset of the table involved in the query.
  • If the query requires a NOT EXISTS condition, then you must use a subquery because NOT EXISTS operator only work with subquery.

Note

  1. Temp Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, an index like normal tables.

  2. CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than the Tempdb database. You cannot create an index on CTE.

  3. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of a batch. This is also created in the tempdb database but not the memory.