CTE

Question

Post reply

CTE

Samith C

Mr or Mrs. 500

Points: 519
More actions
September 24, 2013 at 8:47 pm

#278373

Comments posted to this topic are about the item CTE
[font="Verdana"] There is no Wrong time to do a Right thing 🙂 [/font]

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

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

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

but what about ;(semi colon) of WITH ?

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

Samith C Mr or Mrs. 500 Points: 519 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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: 259105 More actions · Answer 8

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: 22663 More actions · Answer 9

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 More actions · Answer 10

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: 17437 More actions · Answer 11

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 More actions · Answer 12

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: 148809 More actions · Answer 13

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: 10730 More actions · Answer 14

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