September 25, 2013 at 4:29 am
Carlo Romagnano (9/25/2013)
Dscheypie (9/25/2013)
Carlos,sure, you may be right.
And the topic of this QotD is CTE and the usage of DML.
With the example for identifying duplicate records I wouldn't agree to CTEs being useless...
Jamsheer (9/25/2013)
create table #temp_test
(id int);
insert into #temp_test
values(1)
insert into #temp_test
values(1)
insert into #temp_test
values(2)
insert into #temp_test
values(2)
insert into #temp_test
values(3)
insert into #temp_test
values(5)
;
with ABC_CTE as
(
select ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS Num, ID
from #temp_test
)
delete from ABC_CTE where Num > 1
select * from #temp_test
drop table #temp_test
Try This.. It shows another power of CTE by deleting duplicate rows from a table.
CTE always is useless, here does the same:
create table #temp_test
(id int);
insert into #temp_test
values(1)
insert into #temp_test
values(1)
insert into #temp_test
values(2)
insert into #temp_test
values(2)
insert into #temp_test
values(3)
insert into #temp_test
values(5)
;
delete from ABC_NO_CTE
from (
select ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS Num, id
from #temp_test
)as ABC_NO_CTE where Num > 1
select * from #temp_test
drop table #temp_test
The use of CTE (Even though not considering Recursive) cannot be replaced by Derived tables always. Because simple CTE not only working as a derived table, but also it acts as a view. If anyone wants to use a recordset MULTIPLE TIMES in a single selection query, Derived table is not good(need to write the query multiple times which is used for getting the recordset). An example of CTE by using a Self join is given below
DECLARE @Tbl TABLE
(
EmpNo INT,
VDate DATE,
Voucher INT,
VAGE MONEY
)
INSERT INTO @Tbl VALUES(1, '01 MAR 13', 101, 1000)
INSERT INTO @Tbl VALUES(1, '02 MAR 13', 102, 1000)
INSERT INTO @Tbl VALUES(1, '01 APR 13', 103, 1100)
INSERT INTO @Tbl VALUES(1, '02 APR 13', 104, 1100)
INSERT INTO @Tbl VALUES(1, '01 MAY 13', 105, 1200)
INSERT INTO @Tbl VALUES(1, '02 MAY 13', 106, 1200)
;WITH CTE
AS
(
SELECT EmpNo, MONTH(VDate)AS Mnth, SUM(VAGE) TotVageOfMonth
from @Tbl
GROUP BY EmpNo, MONTH(VDate)
)
SELECT A.EmpNo, A.Mnth , A.TotVageOfMonth , B.TotVageOfMonth 'TotVageOfPreviousMonth'
FROM CTE A
LEFT JOIN CTE B
ON B.Mnth +1= A.Mnth
September 25, 2013 at 5:20 am
Carlo Romagnano (9/25/2013)
CTE is usefull only in recursive query for tally table:
Then presumably all language features that provide simplified expressions to replace complexity are useless, in your opinion? If a CTE is used more than once in a query, using subqueries instead requires a lot more typing, and produces something much more difficult to understand; just try writing something like
with cte1(x,y,z) as (<complexquery>),
cte2(u,v,w) as (<anothercomplexquery>),
cte3(r,s,t) as (yetanothercomplexquery)
select z,w,B.t+A.t as t
from cte1 left outer join cte2 on x=v
right outer join
cte3 A full outer join cte3 B
on A.r*B.s < A.t*B.t
on v between A.r and B.s or y between B.s and A.r;without CTEs and you'll realise that the simplify query syntax very much.
Until CTEs became available, since there was no such thing as a temporary view it was a general rule to write such things using 3 temp tables because writing it as a single query led to something almost impossible to understand. To me, that means that CTEs are inherently useful, and I believe that anyone else who has been involved in serious programming langauge design, or anyone who has ever been bitten by mind-boggling complexity visibly caused by absence of a simple language feature, will agree.
Tom
September 25, 2013 at 6:45 am
This was a much trickier question than I gave it credit for. Thanks! You taught me a few things. First, I really shouldn't assume the question says "what will this statement return" which was my bad, but more importantly that GO can work in place of a semi-colon for CTEs. Didn't know that.
September 25, 2013 at 6:50 am
John Mitchell-245523 (9/25/2013)
paul s-306273 (9/25/2013)
Can't see many people ever needing to use this feature.I've had developers gasp in wonder when I've shown them how to use code similar to Jamsheer's and thus avoid copying millions of rows into a new table with an identity column in order to remove duplicates.
John
+1
September 25, 2013 at 6:54 am
I've never even considered using a DELETE using a CTE. You made me think about something new, so thank you.
As for the semicolon before the WITH, that's not necessary. What is necessary is that the previous statement is properly terminated with a semicolon or the WITH statement is the first one in the batch.
A semicolon terminates the SQL statement.
September 25, 2013 at 6:59 am
Carlo Romagnano (9/25/2013)
Koen Verbeeck (9/25/2013)
Very interesting question. Never used a CTE with a DELETE statement before.+0.5
Never used a CTE at all.
This I find amazing... do you not use the Window Functions (ROW_NUMBER, RANK(), DENSE_RANK(), etc)?
I find myself using them all the time
September 25, 2013 at 7:01 am
paul s-306273 (9/25/2013)
...come on, maybe they were really impreseed, but I doubt they 'gasp in wonder'!
Mine did... 3 years ago when I showed them how to do some of these tricks with ROW_NUMBER(). More of a Wow! Than perhaps a Gasp! But close enough for government work. 😛
September 25, 2013 at 7:04 am
mtassin (9/25/2013)
Carlo Romagnano (9/25/2013)
Koen Verbeeck (9/25/2013)
Very interesting question. Never used a CTE with a DELETE statement before.+0.5
Never used a CTE at all.
This I find amazing... do you not use the Window Functions (ROW_NUMBER, RANK(), DENSE_RANK(), etc)?
I find myself using them all the time
Ditto. I was introduced to SQL less than a year ago from a developer perspective and a not-quite-DBA-in-training and even I have tons of uses for CTEs.
September 25, 2013 at 7:06 am
L' Eomot Inversé (9/25/2013)
Until CTEs became available, since there was no such thing as a temporary view it was a general rule to write such things using 3 temp tables because writing it as a single query led to something almost impossible to understand. To me, that means that CTEs are inherently useful, and I believe that anyone else who has been involved in serious programming langauge design, or anyone who has ever been bitten by mind-boggling complexity visibly caused by absence of a simple language feature, will agree.
+1 I certainly agree. Sometimes temp tables rock, but a lot of times I used them to simplify the query structure to keep me from getting confused. In the latter cases, I can use CTE's now, thankfully so.
September 25, 2013 at 7:36 am
L' Eomot Inversé (9/25/2013)
Carlo Romagnano (9/25/2013)
CTE is usefull only in recursive query for tally table:Then presumably all language features that provide simplified expressions to replace complexity are useless, in your opinion? If a CTE is used more than once in a query, using subqueries instead requires a lot more typing, and produces something much more difficult to understand; just try writing something like
with cte1(x,y,z) as (<complexquery>),cte2(u,v,w) as (<anothercomplexquery>),
cte3(r,s,t) as (yetanothercomplexquery)
select z,w,B.t+A.t as t
from cte1 left outer join cte2 on x=v
right outer join
cte3 A full outer join cte3 B
on A.r*B.s < A.t*B.t
on v between A.r and B.s or y between B.s and A.r;without CTEs and you'll realise that the simplify query syntax very much.
Until CTEs became available, since there was no such thing as a temporary view it was a general rule to write such things using 3 temp tables because writing it as a single query led to something almost impossible to understand. To me, that means that CTEs are inherently useful, and I believe that anyone else who has been involved in serious programming langauge design, or anyone who has ever been bitten by mind-boggling complexity visibly caused by absence of a simple language feature, will agree.
+1
This is why I usually wait to reply until I've read through the comments so far. Tom expressed this more elegantly and more succinctly than I would have even if I'd spent all morning on it. (thanks Tom)
September 25, 2013 at 7:41 am
Jamsheer (9/24/2013)
** CTE is a 'reference' to the source tables. i.e. Any changes to CTE will affect the Source table.
Actually, this is not quite correct. Yes, CTEs are nothing more than references to teh source, much as are unindexed views. But it isn't always possible to change them - the restrictions are much the same as for inserting, deleting or updating through views without triggers, so it can't even be done in many cases where it is absolutely unambiguous what must be done to the base tables to achieve the required operation. It's annoying that these failures are not detected at parse time, as it seems perfectly possible to do that; but in fact they are all deteced at run time. And even when it's perfectly easy to see what has to be done, SQL Server may decide it will refuse to do it.
For example
create table t1 (a int primary key, b int not null);
create table t2 (a int primary key, b int not null);
go
--insert some stuff into the tables
with cte3 as (select t1.a from t1 LEFT JOIN t2 on t1.a=t2.a)
/*an amazingly pointlesscte definition! */
delete cte3 where a=3;is accepted happily by the parser but gets ann error message if you run it, claiming that the delete affects more than one base table; as only one base table contributes to the CTE that's clearly false, but the query will fail anyway for that imaginary reason. Of course it isn't possible to compute precisely what is in principle updatable and what is not, but it's certainly trivial to detect some of the obvious cases including a lot of useful ones, as well as useless esoterica like the simple example above, and a decent RDBMS would make the effort to (a) reject things that easily be seen to be invalid at parse time instead of at run time and (b) invest some effort in analysis to enable it to do obviously doable operations before rejecting them despite the ease of doing them.
Tom
September 25, 2013 at 7:48 am
As is often the case, good question, somewhat lacking explanation.
September 25, 2013 at 12:42 pm
Mr. Kapsicum (9/24/2013)
kapil_kk (9/24/2013)
but what about ;(semi colon) of WITH ?
i guess, it should generate an error? Discuss
No it will not give as GO is there...
Remove the GO and try you will get the error
Got the point. Thanks
Also, CTEs do not begin with a semicolon (;). The WITH of a CTE requires that the previous statement must be terminated by a semicolon (;).
Remember that the semicolon is a statement terminator, not a statement begininator.
September 25, 2013 at 12:44 pm
Koen Verbeeck (9/25/2013)
Very interesting question. Never used a CTE with a DELETE statement before.
I use them quite often to verify data I want to update or delete prior to actually doing the update or delete. Really handy, especially when deleting duplicate data.
September 25, 2013 at 12:51 pm
Carlo Romagnano (9/25/2013)
CTE is useless and complicated!The same result is reachable in a standard way:
create table #a (i int)insert #a SELECT object_id FROM sys.objects
delete from t
from (SELECT * FROM #a WHERE i < 10) as t
... and you do not need semicolon.
With complicated CTE, the optimizer does not choose the best plan.
You are entitled to your opinion. I have found CTEs to be extremely useful and when used properly make code easier to develop and read. They have their place, just like derived tables in queries.
Viewing 15 posts - 31 through 45 (of 68 total)
You must be logged in to reply to this topic. Login to reply