Difference between temp table and CTE

                                            Difference between temp table and CTE

 CTE:                 

• CTE is  un-materialized/ non-indexable (cannot create indexes on CTE)
• CTE is logical/disposable View
• CTE persists only till the very next query
• CTE cannot have constraints
• CTE is mostly used for recursion, as CTE can call itself
• CTE resists in memory

 Temp Table:

• Temp table gets stored in temp table
• Temp table persists till the current connection ends
• Temp table can be referred in sub procedure
• Temp table can have constraints,indexes and primary defined
• Indexes can be implemented in Temp Table
• Data can be updated in Temp Table
• Temp Tables are stored in disk

Similar Posts:   Difference between CTE and View  ,  CTE(Common Table Expressions) in SQL ,                                      T- SQL Interview Questions and Answer

Difference between CTE and View - SQL

             Difference between CTE and View

               A CTE is a temporary/logical View, it is not store physically. It is a named query, the result for which is only available to the very next query after the CTE is defined. CTE is defined using WITH clause.

                  A View is a physical object that is present in the database. View is as good as a Table but it doesn't store data physically as compared to a table, only the data schema is stored in View. View,when referred, pulls data by executing the query that is associated with it.

                 The biggest difference between a CTE and View, is that, View or derived table cannot call itself, whereas CTE can call itself and hence support recursion.

                In many databases, views have options, for instance to index them.

Also See:  Having vs Where clause ,  Difference between temp table and CTE , CTE(Common Table Expressions) in SQL  , Difference between DELETE and TRUNCATE command

CTE in SQL

                    CTE(Common Table Expressions) in SQL

CTE( Common Table Expressions) was first introduced with SQL 2005. It is the best replacement for Views.

There are times when we might require to work on small data sets that is a part of large database. For example, we might have a huge sized table that contains data for all transactions that a company has made, but we require current month's data only and apply some grouping or filters on that particular data of current month. Typically views are used to do the task. But, views sometimes over occupy our resources as views are stored at system level. CTE is the best substitute for Views. It is also helpful when we don't have access to create object in a database.


Common Table Expressions or CTE are more readable form of derived table. CTE can be declared once and can be used in multiple queries. CTE can be recursive and non-recursive (we will cover this topic in next post)

Common Table Expression Syntax
A Common Table Expression contains three core parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

EXAMPLE:

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
   SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
   FROM Products p
      INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
   WHERE p.UnitPrice > 10.0
)

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

                            The CTE is part of the subsequent statement only.The subsequent statement can be a single SELECT/INSERT/UPDATE/DELETE, or a compound (with UNION, INTERSECT etc). So if we modify our above example and include a query between CTE creation query(WITH) and the query that refer the CTE.(like below)

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
   SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
   FROM Products p
      INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
   WHERE p.UnitPrice > 10.0
)

SELECT * FROM Products 

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

Here, we will see that when the query that refers to CTE(ProductAndCategoryNamesOverTenDollars) is executed we will get an error "Invalid object name 'ProductAndCategoryNamesOverTenDollars'" 

Also See:   Difference between CTE and View  ,   Difference between temp table and CTE