Temporary tables SCOPE

Question

Temporary tables SCOPE

Carlo Romagnano

SSC-Insane

Points: 22599
More actions
September 5, 2017 at 12:12 am

#338913

Comments posted to this topic are about the item Temporary tables SCOPE

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72497 More actions · Answer 1

Nice question, thanks Carlo.
enjoyed the red herrings in the options...
the "DROP TABLE IF EXISTS #Table" is very handy, too

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

BillLudlow SSCertifiable Points: 6553 More actions · Answer 2

Drat! Read it totally wrong and wanted to select 3 answers, then picked the wrong 2.

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 3

Good question, thanks. Made me really think hard! Good stuff!

srienstr SSCrazy Points: 2435 More actions · Answer 4

Heh, I was so confident of my answer being "obviously" correct that I expected to find out that I was wrong about how EXECUTE() functions.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

Revenant SSC-Forever Points: 42467 More actions · Answer 5

BillLudlow - Tuesday, September 5, 2017 1:26 AM
Drat! Read it totally wrong and wanted to select 3 answers, then picked the wrong 2.

The same. Thanks, Carlo!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Lynn Pettis

SSC Guru

Points: 442467

September 5, 2017 at 4:02 pm

#1958231

Why do I feel that this was a very easy question?

Revenant SSC-Forever Points: 42467 More actions · Answer 7

Lynn Pettis - Tuesday, September 5, 2017 4:02 PM
Why do I feel that this was a very easy question?

Because you are who you are, my friend.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

Lynn Pettis - Tuesday, September 5, 2017 4:02 PM
Why do I feel that this was a very easy question?

Agreed. I was looking for the trick, but it was just the very important point about scope. It may sound scary, but I use the approach covered in the second batch frequently. It's very handy when I can't know the return column names when they're built dynamically.

Nice question, Carlo. Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisCarsonSQL Say Hey Kid Points: 663 More actions · Answer 9

I must be missing something because I tried to execute this SQL and all I received was error 156 incorrect syntax near the keyword 'if'

_____________________________

Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

ChrisCarsonSQL - Thursday, September 7, 2017 4:35 PM
I must be missing something because I tried to execute this SQL and all I received was error 156 incorrect syntax near the keyword 'if'

What version of SQL Server are using? IIRC, the syntax used in this question is for SQL Server 2016 and newer.

Carlo Romagnano SSC-Insane Points: 22599 More actions · Answer 11

ChrisCarsonSQL - Thursday, September 7, 2017 4:35 PM
I must be missing something because I tried to execute this SQL and all I received was error 156 incorrect syntax near the keyword 'if'

What sql server version are you using?
(try with sql server 2016)

Avi1 SSCrazy Points: 2323 More actions · Answer 12

ChrisCarsonSQL - Thursday, September 7, 2017 4:35 PM
I must be missing something because I tried to execute this SQL and all I received was error 156 incorrect syntax near the keyword 'if'

Only first line will not work in the prior versions, it will work only from 2016 version. But main code to related to temp table will work, you can drop the temp table the way you drop normal tables, or just close the window in SSMS

ChrisCarsonSQL Say Hey Kid Points: 663 More actions · Answer 13

Lynn Pettis - Thursday, September 7, 2017 4:39 PM
ChrisCarsonSQL - Thursday, September 7, 2017 4:35 PM
I must be missing something because I tried to execute this SQL and all I received was error 156 incorrect syntax near the keyword 'if'
What version of SQL Server are using? IIRC, the syntax used in this question is for SQL Server 2016 and newer.

That was the issue. Thank you!

_____________________________

Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...

Christian4145 SSC Rookie Points: 36 More actions · Answer 14

Just to add:

There is different behavior on SQL-Server 2012. I have to change the statement to (there is no 'if exists')

if object_id ('tempdb..#t') is not null drop table #t
execute ('create table #t (i int, z int)')
select * from #t
GO
-- second Batch
if object_id ('tempdb..#t') is not null drop table #t
create table #t (i int)
execute ('alter table #t add z int')
select * from #t

There is an error on SQL-Server 2012:

Msg 208, Level 16, State 0, Line 3
Invalid object name '#t'.

The resut will be i,z.

Learning buy doing.