CTE

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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

  • paul s-306273

    SSChampion

    Points: 10615

    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'!

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 21983

    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.

  • ksatpute123

    Hall of Fame

    Points: 3325

    Nice tick putting 5 after 3. Almost got me. Nice question.

  • Dscheypie

    SSCommitted

    Points: 1545

    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.

  • This was removed by the editor as SPAM

  • Dscheypie

    SSCommitted

    Points: 1545

    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)

    REFERENCES temp_test (ID);

    GO

    INSERT INTO temp_test VALUES(1)

    INSERT INTO temp_test VALUES(2)

    INSERT INTO temp_test VALUES(3)

    INSERT INTO temp_test VALUES(5)

    GO

    INSERT INTO temp_test_FK VALUES(1, '1. record')

    INSERT INTO temp_test_FK VALUES(2, '2. record')

    INSERT INTO temp_test_FK VALUES(3, '3. record')

    INSERT INTO temp_test_FK VALUES(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.

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 21983

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 21983

    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);

  • Jamsheer

    Ten Centuries

    Points: 1136

    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)

  • Toreador

    SSChampion

    Points: 11257

    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.

  • Toreador

    SSChampion

    Points: 11257

    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.

  • twin.devil

    SSC-Insane

    Points: 22208

    good interesting question

    as far as CTE is concern they do come handy ...

Viewing 15 posts - 16 through 30 (of 69 total)

You must be logged in to reply to this topic. Login to reply