T-SQL GO statement

Question

T-SQL GO statement

Viewing 15 posts - 16 through 30 (of 46 total)

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

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

Nils Gustav Stråbø (11/23/2010)
It's also worth mentioning that GO is not a T-SQL statement. It is a command only recognized by SSMS, sqlcmd and osql, so it can't be used in any T-SQL code that is not executed in any of the three applications mentioned. In other words, SQL Server does not know what GO is.

Hmm. I seem to recall that I have used GO in Execute SQL Tasks in SSIS without a problem. (Or does that fall in the 3 categories you mentioned, because I only know the first one :blush:)

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

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 2

I think that this functionality of adding a count to the GO command is only available in SQL 2005 and up.

Thanks...Chris

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 3

da-zero (11/23/2010)
Nils Gustav Stråbø (11/23/2010)
It's also worth mentioning that GO is not a T-SQL statement. It is a command only recognized by SSMS, sqlcmd and osql, so it can't be used in any T-SQL code that is not executed in any of the three applications mentioned. In other words, SQL Server does not know what GO is.
Hmm. I seem to recall that I have used GO in Execute SQL Tasks in SSIS without a problem. (Or does that fall in the 3 categories you mentioned, because I only know the first one :blush:)

I just tested, and SSIS actually honors the GO command. Nice to know. Thanks 🙂

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

Nice question, fairly basic. I agree that the # trickery is a bit unnecessary.

Minor bitching about the title and explanation:

1) GO is not a T-SQL statement. It's a batch seperator that most clients recognise and honor. But if you write your own C# client and have it send "GO" to SQL Server, you'll get a syntax error message, as SQL Server does not recognise GO as a valid keyword.

2) A very minor distinction - because GO is processed by the client, GO 100 will not simply execute the batch 100 times; it will send it 100 times. The result will be the same, but you get more network traffic and more parse and compile time. If you want to save on those resources, write a single batch with the logic to execute the statement 100 times.

DECLARE @i int = 1;

WHILE @i <= 100

BEGIN;

INSERT (...);

SET @i += 1;

END;

DougieCow (11/23/2010)
(...)
when I did actually try to run the code it didn't seem to like the "100"... I got...
(1 row(s) affected)
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '100'.
The code is identical... I'm running this on a SQL Server 2000 box, but I don't think that matters...

The server does not matter, the client does. You need at least the SSMS version that ships with SQL Server 2005. It should work against any version server, as far as I know (but I never tried this).

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

sknox SSChampion Points: 12388 More actions · Answer 5

da-zero (11/23/2010)
I like the point the question tried to make, namely using the GO statement as an instrument to loop, but I do not like the trickery with the # name. At first, I did not even notice the GO 100 statement, as I was focusing on the table name. So I'm confused what this question actually tried to do: teach us about GO n (mission accomplished), or about the various options for table names or was it just to trick people into choosing the wrong answer?

Agreed. That's why, for me, this question falls into the "good, but with incomplete explanation" category: I don't mind a question with 2 or more points to make, but you've got to make those points clear in your explanation. Add the explanation that # is an acceptable table name, and a resource explaining why, and this would be a good question.

cengland0 SSCertifiable Points: 6102 More actions · Answer 6

Excellent question. Finally one without any trick and one where I actually learned something. I can actually find a use for this feature.

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

Hugo Kornelis
The server does not matter, the client does. You need at least the SSMS version that ships with SQL Server 2005. It should work against any version server, as far as I know (but I never tried this).

Using SQL 2000 - Query Anayser

Fails with error message "Incorrect syntax near '100'."

Using SSM 2005 to connect to same SQL 2000 server

Executes and returns correct answer.

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]

Surii Default port Points: 1497 More actions · Answer 8

Surii

Default port

Points: 1497

November 23, 2010 at 7:38 am

#1254460

Awesome!!

Cliff Jones SSChampion Points: 10547 More actions · Answer 9

Interesting question, thanks. I had no idea you could do this.

Ken SSCoach Points: 17191 More actions · Answer 10

A nice question. I walked through the problem and got the right answer but the part with the temp table name # concerned me so I had to run it anyway. Interesting that # is a valid table name.

sjimmo SSChampion Points: 11139 More actions · Answer 11

Mike, good question. I guessed at the answer and learned something new.

Thanks

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

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

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.

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]

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

bitbucket-25253 (11/23/2010)
Hugo Kornelis
The server does not matter, the client does. You need at least the SSMS version that ships with SQL Server 2005. It should work against any version server, as far as I know (but I never tried this).
Using SQL 2000 - Query Anayser
Fails with error message "Incorrect syntax near '100'."
Using SSM 2005 to connect to same SQL 2000 server
Executes and returns correct answer.

Thanks for running the test and confirming what I expected, Ron.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

gitmo Old Hand Points: 379 More actions · Answer 15

This could be handy. It'll freak some folks out, too. :w00t: