SQL - CTE vs VIEW

Question

My question here is what is the difference between CTE and View in SQL . I mean, in this case I have to use CTE , and in the case of View . I know that both are virtual tables, but I cannot distinguish between their use.

I found a similar question here , but it's about performance.

Update 1:

For example: I have a database filled with deals ( tbl_trade ). I need to select from the 3.5 million records only those transactions that were open in the current month before the current time, and then manipulate the data (with various queries on the virtual table - it looks like View). The problem here is that I want a SUM of 3-4 columns, and then I need to SUM several columns and create a virtual column with the result (looks like a CTE).

For example: tbl_trade has columns: profit , bonus and expenses . I need SUM(profit) , SUM(bonus) , SUM(expenses) and a new column total , which will be equal to SUM(profit) + SUM(bonus) + SUM(expenses) .

PS. Restarting requests for SUM not an option, as I already have a result.

Thanks in advance!

Answer 1

Views can be indexed, but CTE cannot. So this is one important point.

CTE works fine on tree hierarchy i.e. recursive

Also consider the presentation when working with complex queries. Views are a physical object in the database (but do not physically store data) and can be used for multiple queries, thus providing flexibility and a centralized approach. CTEs, on the other hand, are temporary and will be created when they are used; why they are called inline view .

Update

According to your updated question, opinions would be the right choice. Working with 3.5 million rows in CTE will create additional overhead for TempDb, which will ultimately slow down SQL Server performance. Remember that CTE is a one-time view, so statistics are not saved, and you cannot create indexes either. This is like a helper request.

Answer 2

Both will be interpreted in exactly the same way by the Plan Optimizer. This is a completely different matter.

The view may be used at its discretion. It can encapsulate complex operators in a simpler query.

If CTE is mainly used to write cleaner code with less redundancy in procedures / views, for example. You can also use CTE for recursive queries, which is a very large and powerful feature!

Hope this helps clarify the situation.

Answer 3

One reason for choosing a CTE: If you are performing hierarchical queries, use a CTE. CTE can be called recursively. Views cannot be called recursively.