Let’s try this out. First, open your SQL Query Analyzer. Then, run these three lines of code. (I assume your SQL Server 2000 has default Pubs database).
Use Pubs
SET NOCOUNT ON
SELECT au_lname FROM authors
Then, change from SET NOCOUNT ON to SET NOCOUNT OFF and run them.
Use Pubs
SET NOCOUNT OFF
SELECT au_lname FROM authors
Spot the difference?
Okay, if you didn’t, the hint is at the left bottom tab. Click Messages tab. Got it? The difference is
Why we need to set it ON or OFF? Answer is performance. If set it ON, it has a better performance.
One reply on ““SET NOCOUNT ON” vs “SET NOCOUNT OFF””
at the end of sproc, do i really need to say SET NOCOUNT OFF …. i see some people doing this.. I set nocount on …. why do i need to turn it back off?