SET NOCOUNT Usage

SET NOCOUNT ON, in SQLSERVER, is used to stop the message that shows the count of the number of rows affected by the SQL statement written in the stored procedure or directly SQL Statement. You can view this message in the Management Studio in the Message tab of the result pan. 

When NOCOUNT is ON - the number of affected rows will not be returned 

 When NOCOUNT is OFF - the number of affected rows will be returned   

Triggers also need it, as its useful, If we don't turned on NOCOUNT, their performance can be drastically affected . Take, for example, INSERT triggers that are fired repeatedly, especially when using INSERT INTO statements for massive insert operations. In such cases where the trigger is fired over and over again during the course of the statement, the trigger will issue DONE_IN_PROC messages for each INSERT action, which can slow things down drastically. 

This slow down is especially pronounced if the trigger is being fired as the result of a scheduled SQL Server Agent job. SQL Server Agent automatically imposes a delay after each DONE_IN_PROC signal to avoid server congestion. If you try running the same set of commands through the Query Analyzer, it will execute much faster since no such delays are imposed. If you run such a query through Query Analyzer and see multiple "n rows affected" statements, there's a good chance the query is iterating repeatedly and re-firing the trigger many more times than it really needs to. To turn off DONE_IN_PROC messages, use the SET NOCOUNT ON command at the start of a trigger statement. 

 Microsoft actually encourages the use of SET NOCOUNT ON in Stored Procedures. 

 There is another thing @@ROWCOUNT, which is relevant to this. It is used to get the number of rows affected. Note that either the SET NONCOUNT is ON or OFF, @@ROWCOUNT is always updated with the number of rows affected.

 I hope this post help everyone in understanding the usage of SET NOCOUNT. Thank You.

Multiple Active Result Sets

SQL Server 2005 has so many new features , One of those is Multiple Active Result Sets or MARS. Multiple Active Result Sets is a new SQL Server 2005 feature that, putting it simply, allows the user to run more than one SQL batch on an open connection at the same time.

Pre-SQL 2005 era

In SQL Server's prior to 2005, you could only run one batch per connection. This means simply that you could only do this:

private void MARS_Off()
{
    SqlConnection conn = new SqlConnection("Server=serverName;
        Database=adventureworks;Trusted_Connection=yes;");

    string sql1 = "SELECT * FROM [Person].[Address]";
    string sql2 = "SELECT * FROM [Production].[TransactionHistory]";

    SqlCommand cmd1 = new SqlCommand(sql1, conn);
    SqlCommand cmd2 = new SqlCommand(sql2, conn);
    cmd1.CommandTimeout = 500;
    cmd2.CommandTimeout = 500;
    conn.Open();
    SqlDataReader dr1 = cmd1.ExecuteReader();
    // do stuff with dr1 data
    conn.Close();

    conn.Open();
    SqlDataReader dr2 = cmd2.ExecuteReader();
    // do stuff with dr2 data
    conn.Close();
}

And the accompanying profiler trace:

This example shows that you could use the same connection with the second SqlDataReader only when you finished using the connection with first one. The connection must be closed and reopened as it is shown with Audit Login and Audit Logout events. Opening and closing a connection is an expensive operation so this can hurt performance, even if your connection is stored in the connection pool.

If you for instance wanted to do some processing of the data in your data reader and updating the processed data back to the database you had to use another connection object which again hurts performance. There was no way to use the same opened connection easily for more than one batch at the time. There are of course server side cursors but they have drawbacks like performance and ability to operate only on a single select statement at the time.

SQL 2005 era

SQL Server 2005 team recognized the above mentioned drawback and introduced MARS. So now it is possible to use a single opened connection for more than one batch. A simple way of demonstrating MARS in action is with this code:

private void MARS_On()
{
    SqlConnection conn = new SqlConnection("Server= serverName;Database=adventureworks;
        Trusted_Connection=yes;MultipleActiveResultSets=true;");
    
    string sql1 = "SELECT * FROM [Person].[Address]";
    string sql2 = "SELECT * FROM [Production].[TransactionHistory]";

    SqlCommand cmd1 = new SqlCommand(sql1, conn);
    SqlCommand cmd2 = new SqlCommand(sql2, conn);
    cmd1.CommandTimeout = 500;
    cmd2.CommandTimeout = 500;
    conn.Open();
    SqlDataReader dr1 = cmd1.ExecuteReader();
    SqlDataReader dr2 = cmd2.ExecuteReader();
    conn.Close(); 
}

And the accompanying profiler trace:

MARS is disabled by default on the Connection object. You have to enable it with the addition of MultipleActiveResultSets=true in your connection string.