T-SQL GO statement

Question

T-SQL GO statement

Viewing 15 posts - 31 through 45 (of 46 total)

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

Ken SSCoach Points: 17191 More actions · Answer 1

How often are global temp tables actually used? I recall reading about them when I started using SQL Server but I can't remember ever seeing one used in production. I would guess that ETL processing might be a place to use them?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Kenneth Wymore (11/23/2010)
How often are global temp tables actually used? I recall reading about them when I started using SQL Server but I can't remember ever seeing one used in production. I would guess that ETL processing might be a place to use them?

I have indeed used them in SSIS. (but actually because a senior someone told me I'd better use global instead of local temp tables in SSIS. But I think there's no real reason to prefer local above global.)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Hugo Kornelis (11/23/2010)
bitbucket-25253 (11/23/2010)
Want some more ...
CREATE the temptable as ##
Run the insert code ... do NOT close that instance of SSMS.
Perform the SELECT * FROM ## statement .. right it returns the correct number of rows
Open a second instance of SSMS
Then perform ONLY the SELECT * FROM ## statement in this second instance of SSMS
And yes, you do get the same number of rows returned, Intriguing indeed.
Why intriguing? Table names starting with ## are considered to be global temporary tables. They're available to all sessions, and are only discarded when no sessions use them anymore.

Sorry meant the word "intriguing" as a lame, ok a very lame joke.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

rVadim Hall of Fame Points: 3969 More actions · Answer 4

My SQL Server 2005 BOL dated April 2006 doesn't mention the [count] argument for the GO command. I guess it was added later, in one of the service packs perhabs.

--Vadim R.

vk-kirov SSCertifiable Points: 7686 More actions · Answer 5

Kenneth Wymore (11/23/2010)
How often are global temp tables actually used? I recall reading about them when I started using SQL Server but I can't remember ever seeing one used in production.

I've never seen GTTs in production code, too. However, I often use them when I test my T-SQL code.

For example, there is a long-running stored procedure which returns a dataset, and I'm doing an optimized version of that procedure. I want to run a test to ensure that, given specified parameters, both procedures return the same dataset.

I open two SSMS windows, in one of them I run the following script:

CREATE TABLE ##result1 (...);

INSERT ##result1

EXEC dbo.Old_Procedure @param1 = value1, ...

In the other window, I run the following script:

CREATE TABLE ##result2 (...);

INSERT ##result2

EXEC dbo.New_Procedure @param1 = value1, ...

Both procedures execute in parallel, and I can compare the datasets without making junk permanent tables.

Srikanth-348936 Newbie Points: 3 More actions · Answer 6

A very nice and tricky question. Thanks to the author of this question. GO with the loop is news to me.

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 7

You don't even need to use the word 'GO'. You can set the batch separator to be some string other than 'GO' in the SSMS options. Inveterate prankster and SQL guru in his own right, Rob Farley once showed me a really quite awful but just as funny practical joke you can play on your colleague who is in the habit of leaving his or her workstation without locking the screen. Apparently, and I'm not in any way advocating this, you'll cause some pretty disconcerting and inexplicable error messages if you change GO to oh let's say... SELECT. Your friend may spend hours trying to find the source of the problem.

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

TomThomson SSC Guru Points: 104773 More actions · Answer 8

It's a good question, but now that MS have clearly stated that # (on its own) will not be supported as a temporary table name in a future version of SQL server http://msdn.microsoft.com/en-us/library/ms143729.aspx it's perhaps a bad idea to use it in a question which people will be using for learning.

Tom

Ken SSCoach Points: 17191 More actions · Answer 9

GPO (11/24/2010)
You don't even need to use the word 'GO'. You can set the batch separator to be some string other than 'GO' in the SSMS options. Inveterate prankster and SQL guru in his own right, Rob Farley once showed me a really quite awful but just as funny practical joke you can play on your colleague who is in the habit of leaving his or her workstation without locking the screen. Apparently, and I'm not in any way advocating this, you'll cause some pretty disconcerting and inexplicable error messages if you change GO to oh let's say... SELECT. Your friend may spend hours trying to find the source of the problem.

Well now that's just terrible! Funny, but terrible. I hope no one I work with reads this and gets any ideas!

Ken SSCoach Points: 17191 More actions · Answer 10

Tom.Thomson (11/24/2010)
It's a good question, but now that MS have clearly stated that # (on its own) will not be supported as a temporary table name in a future version of SQL server http://msdn.microsoft.com/en-us/library/ms143729.aspx it's perhaps a bad idea to use it in a question which people will be using for learning.

Good to know Tom. I wouldn't use a table name with just # anyway as I think it is a bad habit and could be confusing. I'm glad Microsoft is eliminating this from being a future issue.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 12

Excellent question. i thought # is not valid table name but googling suggest that it's valid.

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 13

Great thanks. didn't know that. learnt something new.

UMG Developer SSChampion Points: 13482 More actions · Answer 14

UMG Developer

SSChampion

Points: 13482

December 3, 2010 at 1:24 pm

#1258627

Thanks for the question!

supremepss SSC Enthusiast Points: 115 More actions · Answer 15

hi...

yes u r correct sir/madam, me too get the same results when i am executing the below statement

insert into #

select 'asdf'

go 100

but after that , u said to drop the table in the queryy editor means (drop table #), then the answer is # is an invalid table name knw......... So how we can get 101