$$SWYNK$$
SQL Server Tip – SET NOCOUNT ON
This one short clause has been known to speed up database operations for years. I have no idea why it works. If you check the variable @@ROWCOUNT after executing your command, you will get the number of rows impacted by the last SQL Statement.
However, the NOCOUNT property is by default turned off. What this means is that when you execute one or more SQL commands, the count of the number of rows impacted by each command is sent to the standard output. For SQLCMD statements, the count is sent to the console. For SQL Server Management Studio (SSMS) the count is send to the message tab.
Somehow this adds measurable overhead. Especially in stored procedures with multiple statements, one of the first lines I include is SET NOCOUNT ON. Notice that you are turning on a negative behavior. It is a little backward to me…a carryover from SYBASE.
This is also a great tip when you create triggers. Even though the output of the count values is not sent to any useful destination, triggers benefit by having counts disabled. This is especially true of highly active triggers. This also explains why you may perform a data modification command on a table yet see more than one count in the message output. If a trigger performs one or more statements as a result of an SQL insert, update or delete, you will see the counts from the trigger code as well.
SET NOCOUNT ON is restricted in scope to the current process. So, each connection to the database engine manages this value independently.
To restore the counting behavior execute the command SET NOCOUNT OFF..
So, unless you really care about the count for rows impacted by your insert, update, delete, select, or set statements, along with many other kinds of commands you can execute, you may find it helpful to SET NOCOUNT ON.
I hope this little tip long known to the SQL Server community is helpful. These are the kinds of simple tips for those new to SQL Server, even if they are seasoned professionals in other data engines. If you have other tips like this you would like to share, feel free to drop a note to btaylor@sswug.org. MySQL, DB2, Oracle, Posgress, SQL Server…the engine is not as important as the concept.
Cheers,
Ben
Featured Article(s)
A WHERE Clause mandatory for UPDATE & DELETE
Learn how you can build triggers that prevent an update or delete statement from being run without a WHERE clause in this article.
Featured White Paper(s)
Key Management in the Multi-Platform Environment
Written by Townsend Security
This White Paper discusses the challenges … (read more)
Featured Script
admin db – force users out of a database
Before you can restore a database or put it in single user mode you have to get the existing users out of that database. This… (read more)