CTE

  • Samith C

    Mr or Mrs. 500

    Points: 519

    Comments posted to this topic are about the item CTE

    [font="Verdana"] There is no Wrong time to do a Right thing 🙂 [/font]

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    but what about ;(semi colon) of WITH ?

    i guess, it should generate an error? :discuss:

  • Samith C

    Mr or Mrs. 500

    Points: 519

    Mr. Kapsicum (9/24/2013)


    but what about ;(semi colon) of WITH ?

    i guess, it should generate an error? :discuss:

    nope ;

    try it out 🙂

    Which semi colon your saying ?

    [font="Verdana"] There is no Wrong time to do a Right thing 🙂 [/font]

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    Samith C (9/24/2013)


    Mr. Kapsicum (9/24/2013)


    but what about ;(semi colon) of WITH ?

    i guess, it should generate an error? :discuss:

    nope ;

    try it out 🙂

    Which semi colon your saying ?

    This:

    ;with cte as ()

    And tried ur code ssms, it worked,

    and explanation from your side will be highly appreciated. Thanks 🙂

  • kapil_kk

    SSC-Insane

    Points: 21316

    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

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    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

  • Jamsheer

    Ten Centuries

    Points: 1136

    Good and easy question. Helped to have some thoughts about CTE and stated some points.

    ** CTE is a 'reference' to the source tables. i.e. Any changes to CTE will affect the Source table.

    ** The statements before the starting of CTE should be terminated(Before 'with', need to use 'GO' or ';')

    Thanks Samith

  • kapil_kk

    SSC-Insane

    Points: 21316

    Jamsheer (9/24/2013)


    Good and easy question. Helped to have some thoughts about CTE and stated some points.

    ** CTE is a 'reference' to the source tables. i.e. Any changes to CTE will affect the Source table.

    ** The statements before the starting of CTE should be terminated(Before 'with', need to use 'GO' or ';')

    Thanks Samith

    +1:-P

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Koen Verbeeck

    SSC Guru

    Points: 258928

    Very interesting question. Never used a CTE with a DELETE statement before.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Carlo Romagnano

    SSC-Insane

    Points: 21748

    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.

  • psingla

    Hall of Fame

    Points: 3840

    Mr. Kapsicum (9/24/2013)


    but what about ;(semi colon) of WITH ?

    i guess, it should generate an error? :discuss:

    It worked because "with ABC_CTE as" is the first statement in the batch.Go separates two batches of statements.

    If "with ABC_CTE as" is not the first statement in the batch then you need to include semi-colon (;).

    There are many other statements in tsql that works the same way like

    will work

    set statistics io on

    go

    sp_who2

    will not work

    set statistics io on

    sp_who2

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • sqlnaive

    SSCoach

    Points: 17435

    Koen Verbeeck (9/25/2013)


    Very interesting question. Never used a CTE with a DELETE statement before.

    +1. Same here. Never used with Delete so never saw the CTE from that view. 🙂

    Overall good question.

  • Jamsheer

    Ten Centuries

    Points: 1136

    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.

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    Good question, thanks. I'd have liked to have seen in the explanation why the third and fourth options were wrong, something like this:

    Option three is not correct because the CTE definition is the first statement in the batch. Option four is wrong because a temp table lasts for the duration of the session (or until dropped) - if a table variable had been used instead (which lasts for the duration of the batch only), this would have been the correct answer.

    John

  • paul s-306273

    SSChampion

    Points: 10555

    Can't see many people ever needing to use this feature.

Viewing 15 posts - 1 through 15 (of 69 total)

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