none
What is difference between CTE(Common Table Expression) and Variable Tables?

    Question

  • hello

    What is the main difference between CTE(Common Table Expression) and Variable Tables?

    the CTE results are stored in memory? or disk?

    Where any better to use?(for example when we have a lot of data)

    thanks

    Sunday, November 17, 2013 5:58 AM

Answers

  • What is the main difference:

    • Table variable is like Temporary Table. It stores on disk.
    • CTE is like Derived Table or even like a sub-query. So it doesn't store on disk.

    Where any better to use:

    • If you want to work with small number of records (about 100-200), and you force to do your job in a Temporary Table, it's better to use Table variable.
    • If you deal with large numbers of data, sometimes it's better to use CTE and sometimes it's better to use Temporary Tables. But there is no worth to use Table variable.

    Final tip:

    • There is no performance difference between CTE, derived table or sub-query!

    sqldevelop.wordpress.com


    Sunday, November 17, 2013 8:22 AM

All replies

  • the CTE results are stored in memory? or disk?

    Hello,

    No. A CTE is just a query like a common SELECT statement or a view; nothing is stored here, neither in memory nor on disk. A table variable is similar to a scalar variable.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Sunday, November 17, 2013 6:28 AM
  • A CTE is like a derived table and has a advantage over derived tables as we can define CTE once and use it multiple times in the immediate statement, where as derived table has to redefined each time we use them , even in a single statement. A recursive CTE helps to evaluate hierarchical data sets. A table variable acts like a physical table ( with some limitations) with in scope of a single session.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Sunday, November 17, 2013 7:50 AM
  • What is the main difference:

    • Table variable is like Temporary Table. It stores on disk.
    • CTE is like Derived Table or even like a sub-query. So it doesn't store on disk.

    Where any better to use:

    • If you want to work with small number of records (about 100-200), and you force to do your job in a Temporary Table, it's better to use Table variable.
    • If you deal with large numbers of data, sometimes it's better to use CTE and sometimes it's better to use Temporary Tables. But there is no worth to use Table variable.

    Final tip:

    • There is no performance difference between CTE, derived table or sub-query!

    sqldevelop.wordpress.com


    Sunday, November 17, 2013 8:22 AM
  • Thanks to all engineers for your helpful posts
    Monday, November 18, 2013 8:23 AM