locked
difference between cte and temp table RRS feed

  • Question

  • User-1722422105 posted

    Hi all

    i am litttle confusing about when we need To use 

     cte and temp table in sql server

     give me with real time example

    thanks and regards

    siddu

    Monday, July 30, 2018 3:47 AM

Answers

  • User-369506445 posted

    hi

    1. Temp Tables are physically created in the <g class="gr_ gr_25 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="25" data-gr-id="25">Tempdb</g> database. These tables act as the normal table and also can have constraints, <g class="gr_ gr_26 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="26" data-gr-id="26"><g class="gr_ gr_43 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="43" data-gr-id="43">index</g> like</g> normal tables. It is divided into two Local temp tables and Global Temp Table, Local Temp table <g class="gr_ gr_44 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="44" data-gr-id="44">are</g> only available to the SQL Server session or connection (means single user) that created the tables. 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<g class="gr_ gr_47 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep gr-progress" id="47" data-gr-id="47">.cases</g> where you need transaction rollback support.

    2. CTE - Common table Expression is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of <g class="gr_ gr_28 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="28" data-gr-id="28">statement</g>. This is created in memory rather than Tempdb database. You cannot create <g class="gr_ gr_24 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="24" data-gr-id="24">any</g> index on CTE.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 30, 2018 4:04 AM
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 30, 2018 10:26 PM
  • User347430248 posted

    Hi sidu,

    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.

    Temp Table:

    Temporary tables are 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. 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

    You can try to refer links below to get detailed information.

    CTE Vs Temp Table Vs Table Variable

    Differences Among CTE, Derived Table, Temp Table, Sub Query And Temp Variable

    CTE’s vs Temp tables – an Opinion

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 31, 2018 7:14 AM

All replies

  • User-369506445 posted

    hi

    1. Temp Tables are physically created in the <g class="gr_ gr_25 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="25" data-gr-id="25">Tempdb</g> database. These tables act as the normal table and also can have constraints, <g class="gr_ gr_26 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="26" data-gr-id="26"><g class="gr_ gr_43 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="43" data-gr-id="43">index</g> like</g> normal tables. It is divided into two Local temp tables and Global Temp Table, Local Temp table <g class="gr_ gr_44 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="44" data-gr-id="44">are</g> only available to the SQL Server session or connection (means single user) that created the tables. 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<g class="gr_ gr_47 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep gr-progress" id="47" data-gr-id="47">.cases</g> where you need transaction rollback support.

    2. CTE - Common table Expression is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of <g class="gr_ gr_28 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="28" data-gr-id="28">statement</g>. This is created in memory rather than Tempdb database. You cannot create <g class="gr_ gr_24 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="24" data-gr-id="24">any</g> index on CTE.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 30, 2018 4:04 AM
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 30, 2018 10:26 PM
  • User347430248 posted

    Hi sidu,

    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.

    Temp Table:

    Temporary tables are 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. 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

    You can try to refer links below to get detailed information.

    CTE Vs Temp Table Vs Table Variable

    Differences Among CTE, Derived Table, Temp Table, Sub Query And Temp Variable

    CTE’s vs Temp tables – an Opinion

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 31, 2018 7:14 AM