Use of GO

Question

Use of GO

Viewing 12 posts - 16 through 26 (of 26 total)

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

Abhijit More (4/13/2009)
Vijaya,
I must appreciate for explanation you hvae provided. As you said, (*** The current batch of statements is composed of all statements entered since the last GO....***) I have one confusion on this one.
See if you execute the statement "CREATE DATABASE GO" it creates the new database with name "Go" in this scenario your explanation fails.
but if i execute the below statement it gives me the error.
CREATE DATABASE
GO...
Glad to hear more explanation on this.:)

GO is only a batch separater if it starts in column 1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 2

Hi everyone,

Sorry for late replying. The code I have published is very official, hence cant publish the actual code.

But my actual question is, are the SQL statements running parallel or executed serially?

I hope the answer of this is the answer of actual question.

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 3

GO is only a batch separater if it starts in column 1.

Thanks Jeff...I learnt something new again.

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 4

Arup,

You can always mask your code in such a way that the proprietary information is not revealed. It would be helpful especially to newbies like myself to learn practical solutions to real world problems such as the ones people like you post here.

Regards

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 5

kruti (4/13/2009)
Ok. so pointer is again come to first question of this post.
Why it is giving error in his execution?

As has been mentioned by Mr Corbett, I don't think GO is the cause behind Arup's code not working. The problem is due to somethign else (he has not posted the whole code for instance).

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Martin Wills Ten Centuries Points: 1288 More actions · Answer 6

Returning to the original question.

I suspect that there's a level of error-checking performed when a batch of commands are submitted and parsed by SQL Server. This happens before any of the commands are executed. (This will be what happens in SSMS when you click the green tick icon.)

So at this stage, the num column doesn't exist and therefore the errors are reported. It doesn't matter that by the time the command is executed, the num column will exist. (Maybe it's an SQL Server bug - discuss!)

But when you add the GO statement, you now have 2 batches. The ALTER TABLE comand is in one batch. So that is parsed and executed before the second batch containg the WITH ... SELECT command is itself parsed.

And now the num column does exist. So all is well.

starunit SSCommitted Points: 1817 More actions · Answer 7

arup_kc (4/9/2009)
Why are we using GO statement? Generally SQL statements are executed sequentially, then why r we using GO?
Here is a situatrion where without GO, its throwing an error.
ALTER TABLE Temp ADD num int identity(1,1);
go
WITH Dublicates_CTE(consultant_id, num)
AS
(
SELECT consultant_id, Min(r_num) num
FROM GROUP BY consultant_id
HAVING Count(*) > 1
)
Here, if we r not using GO, its throwing an error. The error is:
Msg 207, Level 16, State 1, Line 6
Invalid column name 'num'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'num'.
Kindly give some hint.
Thanks in advance.

Hello Arup_kc,

I didn't see an explicit answer to your question about the error you are receiving, so I'll chime in:

Please refer to the 7th bullet in remarks on the following page:

http://msdn.microsoft.com/en-us/library/ms175972(SQL.90).aspx

which states

When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

Your use of GO preceding the CTE 'WITH' statement made your CTE the first statement in a new batch, so you didn't need the semicolon.

The code sample provided by Jack Corbett included the semicolon, but I didn't see that he explicitly mentioned it.

As for the use of 'Go' with yor Query analyzer: Sometimes you need to separate statements. Try this:

Write a create procedure statement, and following with GRANT EXECUTE statements but don't put a GO between them. Then look at the definition for the SP: the GRANT Statements will be in there as part of the SP.

CREATE PROCEDURE myTestThis AS

SELECT 1

GRANT EXECUTE ON myTestThis TO GUEST

GO

SELECT Object_Definition(object_id('myTestThis'), 1)

Hope this helps,

Mark

Mark
Just a cog in the wheel.

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 8

Hi everybody,

Thanks to all specially MArk and DMW. I have got my answer.

Thanks again.

starunit SSCommitted Points: 1817 More actions · Answer 9

arup_kc (4/28/2009)
Hi everybody,
Thanks to all specially MArk and DMW. I have got my answer.
Thanks again.

You are welcome Hope it worked out for you,

Mark

Mark
Just a cog in the wheel.

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 10

Hi arup_kc,

please conclude your solution in brief.

"Don't limit your challenges, challenge your limits"

upperbognor Right there with Babe Points: 787 More actions · Answer 11

Jack Corbett (4/9/2009)
Have you posted all the code you are running? When I run this (I have to create the table first since I don't have it):
IF OBJECT_ID('temp') IS NOT NULL
BEGIN
DROP TABLE temp
END
CREATE TABLE temp
(
consultant_id INT,
r_num INT
)
Go
ALTER TABLE Temp ADD num int identity(1,1);
go
;WITH Dublicates_CTE(consultant_id, num)
AS
(
SELECT
consultant_id,
Min(r_num) num
FROM
temp
GROUP BY
consultant_id
HAVING
Count(*) > 1
)
SELECT * FROM Dublicates_CTE
It runs fine with and without the GO's.

arup_kc (4/9/2009)
Why are we using GO statement? Generally SQL statements are executed sequentially, then why r we using GO?
Here is a situatrion where without GO, its throwing an error.
ALTER TABLE Temp ADD num int identity(1,1);
go
WITH Dublicates_CTE(consultant_id, num)
AS
(
SELECT consultant_id, Min(r_num) num
FROM GROUP BY consultant_id
HAVING Count(*) > 1
)

Jack, I think I noticed why your code works and his doesn't. You have a ; in front of yours where you declare your CTE, where his code does not. So the CTE is not the first line in the batch for his but in yours it is.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 12

Back again to the original question, you can avoid using GO by using this:

EXEC sp_executesql N'ALTER TABLE Temp ADD num int identity(1,1)'

One situation where you would not want to use GO is if you have declared variables, because when you hit the GO they are gone.