Nocount On/Off

[Ielamrani]

Can anyone explain the meaning of SET NOCOUNT ON and SET NOCOUNT OFF in the following code:

USE pubs
SET NOCOUNT ON
GO
DECLARE @pub_id char(4), @hire_date datetime
SET @pub_id = '0877'
SET @hire_date = '1/01/93'
-- Here is the SELECT statement syntax to assign values to two local
-- variables.
-- SELECT @pub_id = '0877', @hire_date = '1/01/93'
SET NOCOUNT OFF
SELECT fname, lname
FROM employee
WHERE pub_id = @pub_id and hire_date >= @hire_date

Thanks in advance

Ismail

 

[SQLDenis]

that is just to surpres output messages for example
(30000 row(s) affected)

It will also speed up your code

I would make SET NOCOUNT ON the first statement and SET NOCOUNT OFF the last statement in the whole code and not put SET NOCOUNT OFF in the middle of the code

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Ielamrani]

Thank you very much

 

[SQLBill]

SET NOCOUNT ON is great tool for when you do INSERTs, UPDATEs, and DELETEs. This lets you know how many rows actually were affected. Let's say you do an UPDATE and you expect it to change 10 rows of data. But you have SET NOCOUNT OFF and you get (20 rows affected). OOOOPPPSS. Something went wrong and you can check for the 10 rows that were updated incorrectly. With SET NOCOUNT ON, you would not know that 10 extra rows were updated.

For a SELECT, the count of rows returned isn't usually important.

-SQLBill

Posting advice: FAQ481-4875

 

[Ielamrani]

Well explained. Thank you very much
Ismail