April 9, 2009 at 7:07 am
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.
April 9, 2009 at 7:11 am
"GO" is a batch separator. It simply tells Management Studio to run these things separately, instead of as a single script. You can change to something else if you want to, in the preferences.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 8:26 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 10, 2009 at 12:00 am
Hi Jack and GSquared ,
Thanks 4 ur time. But still I m facng the problem without GO.
Is it something like the first statement and second statement clashes, because the first statement creating the identity column and the second one is using than?
I thing all the SQL statements in a batch is running sequentially. i.e. first it will execute the first statement. After finishing that, it will start to execute the second one. But I think this is not happening here.
April 10, 2009 at 7:57 am
Have you posted the entire set of SQL Statements you are trying to run? I don't see any reference to the column "num" in anything you posted other than creating it.
Second, the script you posted throws an error because you never use the CTE after defining it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 10, 2009 at 10:13 am
arup_kc (4/10/2009)
Thanks 4 ur time. But still I m facng the problem without GO.
Why can't you use GO?
Is it something like the first statement and second statement clashes, because the first statement creating the identity column and the second one is using than?
That's a possibilty. That is one of the reasons that SQL code sometimes has to be broken into separate batches. For Scripts, GO is the way to do that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 11, 2009 at 2:53 am
In sql server 'Go' will apart all statment in a single execution into multiple batches. The batch till first Go statement will execute first, and till second Go statement will execute second etc...
o/w in execution of multiple statements in a single run will manage all the execution on multiple statment internally... you hv to check execution plan..
Though, pls provide comeplete code or some more detail.
"Don't limit your challenges, challenge your limits"
April 11, 2009 at 10:17 am
Hi
GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.
CHeck out the below link
http://msdn.microsoft.com/en-us/library/ms188037.aspx
April 11, 2009 at 1:47 pm
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.
I guess a better question would be... why are you adding an IDENTITY column to what's an obvious temp table? It would be better to identify the real task you're trying so solve instead of trying to force what may be the incorrect tool to solve it.
With that in mind, what are you doing that requires the addition of an IDENTITY column often enough to write a script or stored procedure to do it?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2009 at 10:44 pm
GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.
CHeck out the below link
I also hv checked the above link. Though, I have a little confusion.
The point is, if we are not using 'GO' statement, then how SS will execute the bunch of statement?
eg.:
Statement-1
Statement-2
Here, will Statement-1 execute first, and once it is executed completely then only execution of statement-2 will start?
OR
Let say, statement-1 is very big query to execute; but as it is stated first, will executed first; but statement-2 will also execute concurrently in thread or something like that, to utilize the server time fully?
"Don't limit your challenges, challenge your limits"
April 13, 2009 at 6:54 am
kruti (4/12/2009)
the point is, if we are not using 'GO' statement, then how SS will execute the bunch of statement?eg.:
Statement-1
Statement-2
Here, will Statement-1 execute first, and once it is executed completely then only execution of statement-2 will start?
OR
Let say, statement-1 is very big query to execute; but as it is stated first, will executed first; but statement-2 will also execute concurrently in thread or something like that, to utilize the server time fully?
SQL Server uses the first method you mentioned and not the second. Statements within a batch do not overlap each other.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 13, 2009 at 7:19 am
Ok. so pointer is again come to first question of this post.
Why it is giving error in his execution?
"Don't limit your challenges, challenge your limits"
April 13, 2009 at 8:54 am
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.:)
Abhijit - http://abhijitmore.wordpress.com
April 13, 2009 at 9:00 am
kruti (4/13/2009)
Ok. so pointer is again come to first question of this post.Why it is giving error in his execution?
It's hard to say because as I asked in both of my earlier posts, is this all the code? In my first post I included code that does all that the OP is attempting to do and had it work with and without GO. In my second post I also explain that defining a CTE without using it after, as the OP has done throws an error, so I again ask for the complete code.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2009 at 11:33 am
Yes Jack,
I am also feeling something missing in code!
@arup_kc
can you flash some more lights on this topic? Pls provide some more details..
I m really very eager to know abt it...
"Don't limit your challenges, challenge your limits"
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply