September 25, 2013 at 2:07 am
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
September 25, 2013 at 2:16 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
...come on, maybe they were really impreseed, but I doubt they 'gasp in wonder'!
September 25, 2013 at 2:22 am
paul s-306273 (9/25/2013)
...come on, maybe they were really impreseed, but I doubt they 'gasp in wonder'!
Well, it was on the phone, so maybe I just heard what I wanted to hear!
John
September 25, 2013 at 2:39 am
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.
September 25, 2013 at 2:44 am
Nice tick putting 5 after 3. Almost got me. Nice question.
September 25, 2013 at 2:46 am
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.
________________________________________________________
If you set out to do something, something else must be done first.
September 25, 2013 at 2:51 am
Was just curious about the behaviour of CTEs with JOINed tables: I expected same behaviour as of views.
IF OBJECT_ID('temp_test_FK') IS NOT NULL
DROP TABLE temp_test_FK;
IF OBJECT_ID('temp_test') IS NOT NULL
DROP TABLE temp_test;
GO
CREATE TABLE temp_test (id INT NOT NULL);
CREATE TABLE temp_test_FK (id INT NOT NULL, content VARCHAR(100));
GO
ALTER TABLE temp_test
ADD CONSTRAINT PK_temp_test
PRIMARY KEY (ID);
ALTER TABLE temp_test_FK
ADD CONSTRAINT FK_temp_test_FK
FOREIGN KEY (ID)
REFERENCEStemp_test (ID);
GO
INSERT INTO temp_testVALUES(1)
INSERT INTO temp_testVALUES(2)
INSERT INTO temp_testVALUES(3)
INSERT INTO temp_testVALUES(5)
GO
INSERT INTO temp_test_FKVALUES(1, '1. record')
INSERT INTO temp_test_FKVALUES(2, '2. record')
INSERT INTO temp_test_FKVALUES(3, '3. record')
INSERT INTO temp_test_FKVALUES(5, '5. record')
GO
WITH ABC_CTE AS
(SELECT t.ID ID, tfk.ID ID_FK, tfk.content FROM temp_test t INNER JOIN temp_test_FK tfk ON t.id = tfk.id)
DELETE FROM ABC_CTE WHERE id = 1;
WITH ABC_CTE AS
(SELECT t.ID ID, tfk.ID ID_FK, tfk.content FROM temp_test t INNER JOIN temp_test_FK tfk ON t.id = tfk.id)
DELETE FROM ABC_CTE WHERE ID_FK = 1;
--for each CTE
--Msg 4405, Level 16, State 1, Line 1
--View or function 'ABC_CTE' is not updatable because the modification affects multiple base tables.
Not too surprising, I think.
________________________________________________________
If you set out to do something, something else must be done first.
September 25, 2013 at 2:58 am
Carlo Romagnano (9/25/2013)
CTE is useless and complicated!
Clearly not useless, even if your personal preference is not to use them. And no more or less complicated, in my opinion, than your example.
The same result is reachable in a standard way:
CTEs are part of the ANSI 99 standard.
... and you do not need semicolon.
Semicolons will become mandatory for all statements in a future version, so we may as well get used to using them.
With complicated CTE, the optimizer does not choose the best plan.
Do you have any references to support that, please?
John
September 25, 2013 at 3:00 am
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
September 25, 2013 at 3:15 am
John Mitchell-245523 (9/25/2013)
Carlo Romagnano (9/25/2013)
CTE is useless and complicated!Clearly not useless, even if your personal preference is not to use them. And no more or less complicated, in my opinion, than your example.
The same result is reachable in a standard way:
CTEs are part of the ANSI 99 standard.
... and you do not need semicolon.
Semicolons will become mandatory for all statements in a future version, so we may as well get used to using them.
With complicated CTE, the optimizer does not choose the best plan.
Do you have any references to support that, please?
John
CTE is usefull only in recursive query for tally table:
with tally (Num)AS
(
select 0 AS Num
UNION ALL
select 1+ tally.Num AS Num
from tally
)
select top(20000) * from tally
OPTION (MAXRECURSION 20000);
September 25, 2013 at 3:26 am
How about Recursive CTE? Here is a simple example. Try this too..
DECLARE @FromDate DATE = '01 APR 13'
DECLARE @ToDate DATE = '31 MAR 14'
; WITH CTE
AS
(
SELECT @FromDate Dates
UNION ALL
SELECT DATEADD(D,1,CTE.Dates)
FROM CTE
WHERE CTE.Dates < @ToDate
)
SELECT * FROM CTE
OPTION (MAXRECURSION 365)
September 25, 2013 at 4:24 am
Carlo Romagnano (9/25/2013)
With complicated CTE, the optimizer does not choose the best plan.
I have sometimes found that as well.
I have also occasionally found the opposite, where using a CTE has resulted in a better plan, but in general I try to avoid complex CTEs.
September 25, 2013 at 4:26 am
Carlo Romagnano (9/25/2013)
... and you do not need semicolon.
But at some point in the future you will need a semi-colon for everything. Best practice IMO is to use them for everything now, so you are ready.
September 25, 2013 at 4:28 am
good interesting question
as far as CTE is concern they do come handy ...
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
Viewing 15 posts - 16 through 30 (of 68 total)
You must be logged in to reply to this topic. Login to reply