3

A SELECT query, which is an intermediate result for another query, as known, can be either written by a CTE, view or a nested SELECT query (SELECT * FROM (SELECT ... ) a).

Now I want to build a query use one of the three methods as the intermediate result. Should my query logic remains same, is there any difference on performance when using one of the three methods to build my query? (During my build, no advanced and specific topic on either way is used, such as recursive CTE, indexed view, USING ...MERGE ... etc., just fundamental syntax but complexity might be high.)

a4194304
  • 366
  • 2
  • 13
  • 2
    I believe CTEs will just be inlined and run, and then would behave the same as the query without a CTE. A materialized view could perform faster. – Tim Biegeleisen Oct 12 '18 at 03:48
  • Views are something entirely different. CTE and sub queries are essentially the same except when the CTE is recursive. – Salman A Oct 12 '18 at 05:27
  • Write whatever is most straightforward for you to read. Test it. Does it perform adequately compared to your performance goals? Great, move on. If not, try variants. But assuming your code is correctly written in each variant, overall performance is likely to be similar or identical (ignoring materialized/indexed views). Don't try to learn "rules" of the form "X is faster then Y". Even when such rules may exist, the sheer number of accompanying caveats and exceptions (and the sheer number of rules) makes them far less than generally applicable. – Damien_The_Unbeliever Oct 12 '18 at 07:52
  • Possible duplicate of [SQL - CTE vs VIEW](https://stackoverflow.com/questions/30918633/sql-cte-vs-view) – xdtTransform Aug 20 '19 at 11:42

2 Answers2

4

CTE is just syntax so in theory it is just a subquery. you may not get any performance difference while using CTE and Subquery.

I think the biggest benefit for using CTEs is readability. It makes it much easier to see what queries are being used as subqueries, and then it's easy to join them into a query, much like a view.

Now about CTE and View Hope you can read this and see what is suitable for you.

CTE vs View Performance in SQL Server

SQL - CTE vs VIEW

Its also depends how you designed you query and script structure.

Janmejay Kumar
  • 309
  • 2
  • 7
1

The answer is NO.

CTE, View and Subquery depends on your Script Structure. Much better to learn Techniques for improving the performance of SQL queries.

Take a look at this link

Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30