31

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!

Community
  • 1
  • 1
BlackM
  • 3,927
  • 8
  • 39
  • 69
  • 3
    A view is an object in the database. A CTE only exists for the duration of a single query. They seem very different. Can you provide an example of choosing between them? – Gordon Linoff Jun 18 '15 at 14:48
  • 2
    How much data you are dealing with? CTE extensively use TempDb and if your TempDb is not properly configured or your CTE is dealing with large number of rows then it is not a best bet. Choose View when dealing with large amount of data and CTE with small. You can choose CTE also when you need recursion. That's all ! – Anuj Tripathi Jun 18 '15 at 14:50
  • Hi and thanks for your replies. see the updated questions – BlackM Jun 18 '15 at 14:58
  • @BlackM check my updated answer. – Anuj Tripathi Jun 18 '15 at 15:05

5 Answers5

51

Views can be indexed but CTE can't. So this is one important point.

CTE work excellent on tree hierarchyi.e. recursive

Also, 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.

Anuj Tripathi
  • 2,251
  • 14
  • 18
  • 2
    "CTE doesn't work pretty well with large amount of data compared to views" That is not true. There is no difference between a CTE and view unless the view is indexed. – Mikael Eriksson Jun 18 '15 at 16:06
  • But can I add `column1+column2 as column3` and present it in the query? – BlackM Jun 18 '15 at 17:50
  • @MikaelEriksson True but on my testing on large amount of data [recursive] I found high tempdb usage, thus low performance. However, I still believe your comment is correct and I am modifying my answer. – Anuj Tripathi Jun 18 '15 at 19:16
  • @BlackM You can add computed column in views and also you can create CTE inside a view too. – Anuj Tripathi Jun 18 '15 at 19:18
15

Both will be interpreted exactly the same by the Plan Optimizer. It's just a different thing.

A view can be used on its own. It can encapsulate complex statements to a more simple query.

Where a CTE is mostly used to write cleaner code with lesser redundancy in procedures/views for example. You can use a CTE for recursive queries too, which is a very great and powerful feature!

I hope this helps to clarify things.

Sepster
  • 4,800
  • 20
  • 38
Ionic
  • 3,884
  • 1
  • 12
  • 33
12

One of the reasons to choose CTE: If you are doing hierarchical querying, use CTEs. CTEs can be called recursively. Views cannot be called recursively.

zedfoxus
  • 35,121
  • 5
  • 64
  • 63
3

CTE exists in memory only while the query is running. After the query is run, the CTE is discarded; it cannot be used for the next SQL query unless we define it again. Still, the same CTE might be referenced several times in the main query and any subqueries.

A view is a stored SQL query that is executed each time you reference it in another query. Note that a view doesn’t store the output of a particular query – it stores the query itself.

The key thing to remember about SQL views is that, in contrast to a CTE, a view is a physical object in a database and is stored on a disk. However, views store the query only, not the data returned by the query. The data is computed each time you reference the view in your query.

MILASHU BELAY
  • 31
  • 1
1

Just to add one more consideration: permissions.

  • To create and use a view requires CREATE permissions, which you may or may not have been granted.
  • If you have been granted permissions, you may find yourself in naming conflicts with others who have similar permissions and want to create or alter a view with the same name.
  • The simplest way to resolve naming conflicts is to create additional schema (name spaces)

The DB administrator may not be sympathetic to the above, so you may not be able to create views in the first place.

In this case, you can regard a CTE as a throwaway view which requires no more privileges that you already have to run a SELECT query.

I often use a CTE to create something which is self-contained and doesn’t require additional maintenance, especially for sample code.

Manngo
  • 14,066
  • 10
  • 88
  • 110