August 10, 2008 at 10:25 am
hi all,
today one of my friend told me CTE is called as inline view. then I asked what is the different between VIEW and CTE. my friend told me in VIEW recursive query is not possible but it's possible in CTE..
I told its also possible in VIEW...but I am not tried not yet...????
Can anybody tell me different between VIEW and CTE..and how CTE is better alternative of VIEW...or you can say benefits of CTE in stead of VIEW....and What circumstances I can go for a CTE rather than VIEW....
Thanks in advance
Milu.
August 10, 2008 at 11:25 am
A CTE is in essence a temporary view. It's a named query that only exists for a single query after its defined. It simplifies writing queries with complex subqueries that are repeatedly used or referenced.
A CTE can contain a reference to itself, whereas a view can't. That's the basis of the recursive mechanisms for CTEs.
Does that help?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2008 at 4:49 pm
A CTE also doesn't permanently exist. It's only in the batch code itself. A view exists as a separate object and can be called by anyone. Granted, a stored procedure/UDF can contain a UDF and do something similar, however it cannot take the place of a table, as a view can.
August 10, 2008 at 5:26 pm
A CTE is nothing more than a "derived table" just like a subquery in a FROM clause would be. The big differences between itself and a "derived table" is that a CTE can call itself for recursion and it can be referenced multiple times in the query that immediately follows.
A view can also be called many times from a single query, but a view cannot call itself.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 11, 2008 at 10:24 am
hi Gail
==========
I got your point but I need one example of this{VIEW and CTE both i need to compare as per you, so i need both script.....}, I will be clear otherwise i am not clear...
A CTE can contain a reference to itself, whereas a view can't. That's the basis of the recursive mechanisms for CTEs.
hi Jones
===========
I agree, but below point makes me confuse?? Clear me with a small example...
a stored procedure/UDF can contain a UDF and do something similar, I mean to say if a procedure is calling a UDF, it can call many times...but Is it possible CTE, I mean is it not restricted to scope, can we use it out side scope.....
hi Moden
===========
Yes true....1st point ....But below point i am not believing , make me believe you....
A CTE is nothing more than a "derived table" just like a subquery in a FROM clause would be.
but here i can note a point to you...i am using derive table in from clause and in CTE am constructing a new structure which is going create a execution plan in my result....Is this happening in Derive Table.....
for Moden...my 2nd point..
===================
The big differences between itself and a "derived table" is that a CTE can call itself for recursion and it can be referenced multiple times in the query that immediately follows.
you said it can be recursive...you mean to say..if i am not wrong....lets examine it..what i understood from your post...
with TempCTE(CustID,CustName)
as
(select * from Customer where CustomerID in (select CustID from TempCTE))
Select * from TempCTE
is this possible....????
Clear me please you all....Gail, Jones, Moden...
am looking for your reply to all.....
luv's
Milu.:)
August 11, 2008 at 10:41 am
Of course a View itself can contain a CTE also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 11, 2008 at 10:43 am
rbarryyoung, Not Clear to me, Give a Example....Please....
luv's
Milu.:)
August 11, 2008 at 10:44 am
Milu (8/11/2008)
with abc(a,b)as
(select * from temp where tid in (select tid from abc))
is this possible....????
Not like that, no. A recursive CTE has to follow a format that has a few restrictions (see Books OnLine).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 11, 2008 at 10:46 am
A recursive CTE has to follow a format that has a few restrictions (see Books OnLine).
Please give a example..............
(see Books OnLine). ..........I am unable to know the concept by reading.....can you give me a script which will shows me what you said now....???/
luv's
Milu.:)
August 11, 2008 at 10:50 am
You can pretty much take any CTE example out of BOL (Books On-Line) and just put a "CREATE VIEW..." in front of it.
Here is a recursive example:
Create View vwRecursiveAW AS
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 11, 2008 at 10:53 am
your code...
Create View vwRecursiveAW AS
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN vwRecursiveAW d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
My code:
Create View vwRecursiveAW AS
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN AdventureWorks.HumanResources.Employee d
ON e.ManagerID = d.EmployeeID
--SELECT ManagerID, EmployeeID, EmployeeLevel
FROM vwRecursiveAW;
Tell me the difference...???/
luv's
Milu.:)
August 11, 2008 at 11:07 am
I need the advantage rather than small small use..of CTE....
and I need the script wise compare....I mean to say where VIEW fails that can be possible by CTE..
I need practical example..I am not sure..but i can assure you what you achieve by CTE, I can do by using VIEW.....
luv's
Milu.:)
August 11, 2008 at 11:11 am
Milu (8/11/2008)
your code...Create View vwRecursiveAW AS
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN vwRecursiveAW d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
My code:
Create View vwRecursiveAW ASSELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN AdventureWorks.HumanResources.Employee d
ON e.ManagerID = d.EmployeeID
--SELECT ManagerID, EmployeeID, EmployeeLevel
FROM vwRecursiveAW;
Tell me the difference...???/
luv's
Milu.:)
Neither one is correct. The first one attempts to recurse on the view, but it has to recurse on the CTE. Should be:
Create View vwRecursiveAW AS
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
(Change in bold.)
A view can't reference itself. A CTE can.
As for Jeff's statement about a CTE and a derived table being essentially the same thing, if you disagree, you may not actually understand what a "derived table" is.
A derived table is a query inside another query, that is queried from in the outer query. Here's an example:
select main.Col1, main.Col2, derived.Col3
from Main
inner join
(select Col3, Col4
from Secondary) Derived
on main.Col1 = derived.Col4
The query that selects Col3 and Col4 from table Secondary, is a "derived table". Usually, they contain complex joins and where clauses that can't be easily done in the outer query.
To write the same thing as a CTE:
;with CTE as
(select Col3, Col4
from Secondary)
select main.Col1, main.Col2, cte.Col3
from Main
inner join CTE
on main.Col1 = cte.Col4
Basically, it's the same thing, but written slightly differently.
The main advantage to CTEs over traditional derived tables is that you can reference a CTE more than once, including inside itself (with special rules), or in later CTEs. Derived tables cannot do those things.
A view is a database object all by itself. It's a pre-built select statement that's stored in the database and can be used over and over by other database objects, like procs, UDFs, or even other views.
A CTE isn't like that. It only operates on the query immediately following it (or following the last CTE in that particular query, if you have more than one CTE in a query). It doesn't get stored as a separate thing in the database.
For example, with a view called "viewTest", you can do something like this:
update viewTest
set col1 = 1
where col2 = 3
update viewTest
set col2 = 4
where col1 = 1
update viewTest
set col3 = 5
where col4 = 2
You can reference it in multiple commands and queryies.
On the other hand, a CTE doesn't work that way. Try this:
;with CTE as
(select col1, col2, col3, col4
from Table1)
update CTE
set col1 = 1
where col2 = 3
update CTE
set col2 = 4
where col1 = 1
You'll get an error from that, because the CTE only works for the first one, and the second one doesn't know what to update.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 11, 2008 at 11:24 am
Milu (8/11/2008)
your code...Create View vwRecursiveAW AS
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN vwRecursiveAW d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
OK, first of all this is NOT My code. This code does NOT work.
This is my code:
Create View vwRecursiveAW AS
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
This code WORKS.
My code:
Create View vwRecursiveAW ASSELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN AdventureWorks.HumanResources.Employee d
ON e.ManagerID = d.EmployeeID
--SELECT ManagerID, EmployeeID, EmployeeLevel
FROM vwRecursiveAW;
Tell me the difference...???/
Well, the big difference is that my code works and yours does not.
Here is what mine outputs:
ManagerID EmployeeID EmployeeLevel
----------- ----------- -------------
NULL 109 0
109 6 1
109 12 1
109 42 1
109 140 1
109 148 1
109 273 1
273 268 2
273 284 2
273 288 2
288 290 3
284 285 3
...
Yours will not even compile because there is no EmployeeLevel column in the original table that you are referencing. This column is made by the CTE and then re-Referenced in the recursive part of the CTE.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 11, 2008 at 11:24 am
GSquared, ...
rbarryyoung code was correct, when i copied it got changed.....Sorry for that....:)
As you told..
You'll get an error from that, because the CTE only works for the first one, and the second one doesn't know what to update.
as you told it working with a scope...just like a variable ..rite???
do you think it is faster rather than looping ...???
Now I got little bit....I will happy if you post a view and a CTE and compare them ..so it would be better..what rbarryyoung exactly correct....I wanted to know more that's why I asked to rbarryyoung...
Thanks to rbarryyoung..for his clear explanation.....happy to see Sql Guru's Explanations.....
I LOVE SQL SERVER CENTRAL FORUM......
Still I need reply from Gail.., Jones..., Moden....regarding this.....:P
luv's
Milu.:)
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply