What are the main differences between these entities?

  • temporary table
  • generic table expression
  • cursor

    1 answer 1

    Temporary tables are of two types. Tables variables (@Table), temporary tables (#table), there are still tables of the form ## table, differ from #table by the scope.

    In this regard, you can give a brief description:

    • Variable tables are stored in RAM (if there is enough). Available in code block, i.e. it can be reused in different requests. has a local scope, just like any other local variable.

    • Temporary tables. They are stored in tempdb, have a wider scope, namely throughout the call stack. Those. if procedure A created table #A, then called procedure B, which created table #B, then both A has access to #B (after calling B) and B has access to #A. You can also create indexes, triggers, and so on temporary tables, in contrast to variable tables. Tables ## Table have a global scope. If someone created the ## Table table, all sessions see it, but it exists until at least one session that accessed this table is alive.

    • STE. Here the area of ​​validity is only within one request! Those. reuse the result is impossible. Moreover, if you access the CTE several times within the same apros, it will be calculated the same number of times ! There are undocumented ways to make the optimizer remember CTE in RAM for reuse, but that's another story :)

    • Cursors. In general, this is a bit of another opera ... Cursors allow you to process data line by line and are not intended for storage. Inside the cursor, you can call the execution of procedures, which cannot be done in the request.

    I will add my subjective opinion when you need to use it.

    • Tables are variables. When you need to use a small amount of data. For example, the intermediate result of a complex query is to write a variable into a table, thus breaking up a complex query into two simple ones.

    • Temporary tables. When there is a lot of information and / or it needs to be transferred to another place of execution. These tables are no different from ordinary tables, except that you don’t need to worry about cleansing and deleting them.

    • CTE - when it is impossible to use temporary tables (ie, such places that oblige us to use only one SQL query), for example, inside the body of a table function.

    • Cursors - when you can not do in other ways. For example, when for each row of a temporary result, you need to run the execution of the stored procedure. In MS SQL, cursors are usually slower than queries. So there is a possibility - it is better to avoid them.