Semantics of sql NOCOUNT

Question

This is a minor question regarding the usage and semantics of the NOCOUNT statement. I've seen it used a couple different ways and I want to know what is actually required or not.

I've seen it listed on MSDN with the trailing semicolon and GO statement like such:

SET NOCOUNT ON;
GO

and I've seen in without the trailing semicolon:

SET NOCOUNT ON
GO

and I've seen it without the GO statement

SET NOCOUNT ON

I realize that the GO simply signals the end of a batch, but should this be called in order for the NOCOUNT to take effect?

And what is the point of the semicolon?

Your question really has nothing to do with `SET NOCOUNT ON`: you're actually asking when to use a semi-colon and when to use `GO`. Both questions have been answered already: [here](http://stackoverflow.com/questions/710683/when-should-i-use-semicolons-in-sql-server) and [here](http://stackoverflow.com/questions/2668529/t-sql-go-statement). And of course you can read the documentation too: [here](http://msdn.microsoft.com/en-us/library/ms177563.aspx) and [here](http://msdn.microsoft.com/en-us/library/ms175972.aspx). — Pondlife, Mar 15 '13 at 18:53

score 0 · Accepted Answer · answered Mar 15 '13 at 17:16

0

A semicolon ends the current SQL statement.

To the best of my knowledge, it isn't needed after SET NOCOUNT ON.

You should not need 'GO' to have NOCOUNT take effect, though I'm less certain of that.

answered Mar 15 '13 at 17:16

Jeff

2,835
3
41
69

3

Regarding whether or not it is is needed after `SET NOCOUNT ON` this depends on what follows. e.g. `SET NOCOUNT ON; WITH T(C) AS (SELECT 1) SELECT * FROM T;`. Not using the semicolon as a statement terminator is deprecated. – Martin Smith Mar 15 '13 at 17:19

djangojazz · Answer 2 · 2013-03-15T19:08:46.313

A ';' is a memory terminator I always though and a GO statement is a batch terminator.

So if you do DDL creation such as creating a proc, view, function, or other object you can do a bunch like:

Create proc blah as .... GO

create proc blah2 as .... GO

And then you can have a single nice creation script. If you did not have the GO's it would break as it would say something like: "Create (thing) must be the first statement in a creation ...." This means SQL thought you were doing a single operation for both. 'GO' says: "NEW SCOPE, NEW OBJECT". So it gets around that. If you look at the creation scripts for pubs and Northwind ( the old MS test databases) I believe they all are using batch terminators for a single '*.sql' file. It makes a bunch of creation possible in a single file.

A ; will just terminate the memory up to a statement. Most of the time it will be fine to omit them but a big place some of you SQL experts will know you cannot get away from this is..... CTE's!

Yes a CTE will yell at you right away because it begins with a 'with' but you can also use (nolock) hints with 'with' so it needs to differentiate between the two transactions and THUS you should use a ';'.

EG:

Select * from table -- standard SQL no biggie

Or

Select * from table 
Select * from table2  -- these are fine stacked and will run

But...

Select * from table
with a as (select * from table2) select * from a

will break immediately because it did not know that 'with' s context was changed to a new statement. Proper SQL if you are being meticulous should be like:

Set NoCount ON;  -- No thank you engine I don't need to see counts
Set Transaction Level Isolation Level Read Uncommitted;  -- Set me to dirty reads as default

Select 
    * 
from table
;

Select 
  * 
from table2
;

SQL's Engine see this as:

Set NoRead ON;-- No thank you engine I don't need to see counts\nSet Transaction Level Isolation Level Read Uncommitted;\n-- Set me to dirty reads as default\n\nSelect\n*\nfrom table\n;\n\nSelect\n*\nfrom table2\n;

So it needs a little help from the person telling it where the white space TERMINATES. Or else it is not human and does not know where one statement stopped and another one began.

Whatever you do if you were writing it for others and under well defined guidelines I was always told to do the ';' terminator to make it official ending sequence.

A GO is a batch terminator but you can change contexts with it, which make it useful for switching databases like:

Use Database1 GO

Select * from TableOnDatabase1;

Use Database2 GO

Select * from TableOnDatabase2;

Also to save space I did a single line but really you should be doing your main sql syntaxes on a separate line and also sub syntax like:

Select 
    ColumnA
,   ColumnB
,   count(ColumnC) as cnt
From table
Where thing happens
Group by 
    ColumnA
,   ColumnB
Having Count(ColumnC) > 1
Order by ColumnA

EDIT for common real world example:

set nocount on;

declare @Table table ( ints int);
declare @CursorInt int = 1;

while @CursorInt <= 100 
begin 
  insert into @Table values (@CursorInt)

  set @CursorInt += 1
End

-- wait a second engine you did not tell me what happened in the 'Messages' section?!
-- aw come on I want to see each transaction!

Set nocount off;

while @CursorInt <= 200 
begin 
  insert into @Table values (@CursorInt)

  set @CursorInt += 1
End

-- okay that is annoying I did not have to see 100: "(1 row(s) affected)"

You can turn on and off 'nocount' with memory terminators as much as you want in the scope of a procedure. I do it all the time when I want to see some inserts and ignore others in my procs. And in some if I want to pass them out I then set an output variable or a simple select of a final rowcount for a return.

Semantics of sql NOCOUNT

2 Answers2