Thursday, 6 November 2014

SQL - CTE vs TEMP tables


Temp Tables in SQL Server:

  1. Are real materialized tables that exists in tempdb
  2. can be indexed
  3. can have constraints
  4. Persists for the life of the current connection
  5. Can be referenced by the queries or sub procedures
  6. Have dedicated statistics generated by sql engine
  7. A table created on disk
  8. Use temp tables for longer or larger queries data.
  9. Table name looks like #Tablename
CTE - Common Table Expressions:
  1. CTE is an expression that can be thought of as a temporary result set which is defined   within the execution of a single SQL statement. A CTE is similar to derived table in that it is not stored as an object and lasts only for the duration of the query.
  2. Using CTE improves the readability and makes maintenance of complex queries easy
  3. The query can be divided in to separate, simple, logical buildings blocks which an be then used to build more complex CTE's until final result set is generated
  4. CTE can be defined in functions, SP's, Triggers and views
  5. After CTE is defined, it can be used as a table or a view and can select,Insert,Update or Delete
  6. No additional statistics stored in sql server engine
  7. No indexes
  8. Cannot have constraints
  9. Are essentially disposible views
  10. Persist only until the next query is run
  11. Can be recursive
  12. CTE's and Tbale Variables can be use for small dataset
  13. Can be substituted for a view
  14. Can reference itself multiple times

1 comment:



  1. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
    Msbi online training Hyderabad
    Msbi online training India
    Msbi online course

    ReplyDelete