SQL

*************************************     Differences            *************************
-- Differences    CTE, Temp Tables, Derived tables and Table Variable

/*

A) What is CTE? Advantages
   Common table expressions are temporary result sets which is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE OR CREATE VIEW statement. It’s like a derived table which lasts only as long as the session of the query.
 
   Advantages of CTE: 
   1) Can be used to create a recursive query.
   2) Can be substituted for a view
   3) Allow grouping by a column which might be derived from a scalar subset
   4) Can reference itself multiple times
   Scope of an CTE in SQL Server 2005
   The rule of thumb is that the scope is until where next ; would be. A semi-colon terminates any statement but is optional unfortunately.


   -- CTE
WITH t (customerid,lastorderdate) AS
(SELECT customerid,max(orderdate) FROM sales.SalesOrderHeader
 WHERE customerid=27604
 GROUP BY customerid)
SELECT * from sales.salesorderheader soh
INNER JOIN t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
GO

--Temp table
CREATE TABLE #temptable (customerid [int] NOT NULL PRIMARY KEY,lastorderdate [datetime] NULL);
INSERT INTO #temptable
SELECT customerid,max(orderdate) lastorderdate FROM sales.SalesOrderHeader
 WHERE customerid=27604
 GROUP BY customerid;
SELECT * from sales.salesorderheader soh
INNER JOIN #temptable t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
drop table #temptable
GO

--Table variable
DECLARE @tablevariable TABLE (customerid [int] NOT NULL PRIMARY KEY,lastorderdate [datetime] NULL);
INSERT INTO @tablevariable
SELECT customerid,max(orderdate) lastorderdate FROM sales.SalesOrderHeader
 WHERE customerid=27604
 GROUP BY customerid;
SELECT * from sales.salesorderheader soh
INNER JOIN @tablevariable t ON soh.customerid=t.customerid AND soh.orderdate=t.lastorderdate
GO

Fast 1        2              3
CTE ---> Temp table ---> Table variable


*/

******************
/*
Question -03 When to Use SQL Temp Tables vs. Table Variables
#Local SQL temp tables:- tempdb database. A local SQL Server temp table is only visible to the current session.
Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables.
These are automatically deleted when the session that created the tables has been closed.
The local temporary table name is stared with a single hash ("#") sign.

##Global_Table_Name :-   you want the result set visible to all other sessions.  No need to setup permissions.
Global temp tables are available to all SQL Server sessions or connections (means all the user).
These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server
connections have been closed. The global temporary table name is stared with double hash ("##") sign.

Table Variable :-  They reside in the tempdb database much like local SQL Server temp tables.
  Also like local SQL temp tables, table variables are accessible only within the session that created them.
  However,  unlike SQL temp tables the table variable is only accessible within the current batch.

  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.

Notes :--

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.