My question here is what is the difference between CTE
and View
in SQL. I mean in which case I should use the CTE
and which case the View
. I know that both are some kind of virtual tables but I can't differentiate their use.
I found a similar question here but it's about performance.
Update 1:
For example: I have a database filled with trades(tbl_trade
). I need to select from 3.5 millions records only the trades that was opened the current month until current time and then manipulate the data(with different queries on the virtual table - this looks like View). The problem here is that I want a SUM
of 3-4 columns and then on I need to SUM
some columns and create a virtual column with the result(looks like CTE).
Eg: 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. Rerunning the queries for SUM
is not an option since I already have the result.
Thanks in advance!
Views can be indexed but CTE can't. So this is one important point.
CTE work excellent on
tree hierarchy
i.e. recursiveAlso, consider views when dealing with complex queries. Views being a physical object on database (but does not store data physically) and can be used on multiple queries, thus provide flexibility and centralized approach. CTE, on the other hand are temporary and will be created when they are used; that's why they are called as
inline view
.Update
According to your updated question, views will be the right choice. Dealing with 3.5 million rows in CTE will create extra overhead on TempDb which will eventually slow down SQL Server performance. Remember, CTE is a disposable view hence no statistics are stored and you can't create Indexes too. It is just like a sub query.