Different between CTE, Temp Table, and Variable Table

• Akshay Shah

  Mr or Mrs. 500

  Points: 501

  More actions

  July 7, 2010 at 10:23 pm

  #237310

  Hi,

  I am new to SQL Server 2005, I came across this Temporary Table type CTE, Temp Table, and Variable Table.

  Can any one explain in detail or provide any URL for difference between CTE, #table_name, ##table_name, and Variable Table and when to use this thing 🙂

  I gone through the Google but did not got the proper information.

  Thanks in Advance

  Akshay

• malleswarareddy_m

  SSCertifiable

  Points: 5847

  More actions

  July 7, 2010 at 10:36 pm

  #1189991

  CTE is new feature in SQL server in 2005 which uses the result set as a table and you can join the result set with some more table.CTe is use with select.

  where as table varable first you need to declare a table variable with columns and datatypes and after that you will perform insertion, delete into table variable.

  #temp table will used to store entire result set in table.you can create the temp table as like temp table or it will automatically create using select into statement.it will stay until connection disconnected.

  you will find more info in this site.use the search and type CTE or temp tbale

  you will get good articles in this site.

  Malleswarareddy
  I.T.Analyst
  MCITP(70-451)

• ColdCoffee

  SSC-Dedicated

  Points: 39971

  More actions

  July 7, 2010 at 10:40 pm

  #1189994

  Books Online - THe free help that comes along with SQL Server 2005 installation is better than Google search..

  CTE = Common Table Expression

  These are just select queries ; they dont create a physical space for them in tempDB.

  Syntax :

  ; -- This semicolon is required when ur CTE is going to be part of another query.

  WITH <CTENAME> as

  ( your select query on YOURTABLE )

  SELECT * FROM <CTENAME>

  -- The above query is equivalent to

  SELECT SUbquery.* FROM

  ( your select query on YOURTABLE ) AS Subquery

  Temp Table :

  These are virtual-physical (i know this is an oxymoron :-D) tables that get created much like a normal table. The thing that makes it different from normal tables is TEMP tables are temporary (:-D) ; their scope is within the session they are created.

  I ll give u a sample on what i said above ; do this

  1. Open SSMS (SQL Server Management Studio)

  2. Open 2 "New Query"

  3. Run the following code on query1 window

  CREATE TABLE #Temp ( A int, B int)

  INSERT INTO #Temp

  SELECT 1 , 2 UNION ALL

  SELECT 3 , 4

  SELECT * FROM #Temp

  4. Run the following code on query2 window

  SELECT * FROM #Temp ( A int, B int)

  What do u see ? query 1 and 2 both refer the same table, but only query1 produces result; so the temp table is accessible only in it's scope.

  You can create index, create constraints, et al ; basically u can do anything to a temp table what u can do to a normal table.

  Table Variables

  They are very similar to the temp table but with certain limitations from the temp tables.

  I actually lost the link which dealt with Temo Table and Table Variables ; once i find it, i will put it here..

  Hope this helps you!