Jump Start: SET NOCOUNT ON

The SET NOCOUNT ON statement can help you optimize your T-SQL stored procedures and batches by telling SQL Server and SQL Server Express not to return the count of rows affected by each T-SQL operation. Disabling the row count might not seem important, but when you have a large number of client connections or limited system resources, as you might if you're using SQL Server Express, SET NOCOUNT ON can reduce network utilization and improve your application’s performance.

To use SET NOCOUNT ON, simply add the statement to the top of your stored procedures, as in the following listing:

CREATE PROCEDURE sp_UseSETNOCOUNTONASBEGINSET NOCOUNT ON;select @@servernameENDGO

The code in this listing first creates a stored procedure named sp_UseSETNOCOUNTON. The first executable statement in the stored-procedure code (between the BEGIN and END statements) is SET NOCOUNT ON. Just to give the stored procedure something to do, I then coded a SELECT statement with the @@servername system variable to return the server name. The SET NOCOUNT ON statement remains in effect until the stored procedure or batch containing it ends or until the SET NOCOUNT OFF statement is executed.