Steve Ngai (危 志 荣) KuaLa Lumpur, Malaysia

“SET NOCOUNT ON” vs “SET NOCOUNT OFF”

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

  • SET NOCOUNT ON – the count is not returned
  • SET NOCOUNT OFF – the count is returned
  • Why we need to set it ON or OFF? Answer is performance. If set it ON, it has a better performance.