CTE

  • 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

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

  • 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

  • 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.

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

  • 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

  • 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.

  • 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

  • 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

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

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

  • 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.

  • 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.

  • 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