What is SET NOCOUNT in SQL Server

You must have seen the statement SET NOCOUNT ON in most of the stored procedure. When I saw this message first time I was thinking why this has been used in almost all stored procedure what will happen if I set SET NOCOUNT OFF in place of ON. Here in this article I thought I will share you the advantage of using SET NOCOUNT ON in SQL Stored Procedure and how this will improve the performance of your query.

The main advantage of using SET NOCOUNT ON in your stored procedure or TSQL statement is, this statement automatically suppress the message which shows how many rows has been affected by the query. Which means, this will reduce the overhead of the server and improves the performance when you execute query in a table which has got millions of data.

What exactly is happening inside the server is when you execute a stored procedure with SET NOCOUNT ON  it prevents the sending of DONE_IN_PROC messages to the client for each statement in your stored procedure. Imagine you have a loop or several statement has to be executed!!! So using SET NOCOUNT ON will definitely boost your query performance and adding the line SET NOCOUNT ON is one of the main DBA tasks when they find that query or procedure is not performing well.

If you wanted to know the message of affected rows then you can add SET NOCOUNT OFF in your query. This will show the number of rows affected by executing the query.

Irrespective of using SET NOCOUNT ON or SET NOCOUNT ON  SQL server internally updates @@ROWCOUNT function. So querying @@ROWCOUNT function will give you the affected rows details at any time.

Now let’s see how it shows in SQL query analyzer,

Below is my stored procedure, you can notice that I have commented the line SET NOCOUNT ON. 

CREATE PROCEDURE [dbo].[sp_GetCustomerList]

AS

BEGIN

    --SET NOCOUNT ON;

    SELECT * from Customer

END

We will execute this procedure and see the result.

Now we will un comment the line SET NOCOUNT ON and see the output,

CREATE PROCEDURE [dbo].[sp_GetCustomerList]

AS

BEGIN

    SET NOCOUNT ON;

    SELECT * from Customer

END

Hope you are now clear about the usage of SET NOCOUNT ON.

How to use NuGet Package Manager for Visual Studio 2013

Technorati Tags: How to use NuGet Package,How to install AjaxControlToolKit using NuGet Package Manager,Install AjaxControlToolKit for VisualStudio 2013

Have you ever heard about NuGet Package? Do you know how to use NuGet Package manager and how to install AJAXControlToolkit using NuGet Package Manager. Here is the answer for all your doubts related to NuGet Package manager for Visual Studio.

Pre-Requisite: System with Visual Studio 2013 and Good Internet Connection

First we will download NuGet Package manager for Visual Studio 2013. You may use the this link :NuGet Package Manager for Visual Studio 2013 for downloading the package.

Once you install the package open Visual Studio 2013 and open the Package Manager Console.

Refer below image to know how to open NuGet Package Manager Console.

Once you click on the Package Manager Console you will find a consol window link below

Here is the place you execute the command install AjaxControlToolkit. As I mentioned earlier you should have good internet connection to download the AjaxControlToolKit otherwise it will throw error.

The command you execute to install AjaxControlToolkit is :PM> Install-Package AjaxControlToolkit

Once you execute the command Install-Package AjaxControlToolkit it may take few seconds and you will below updates,

Finally when the installation completes you will find below updates,

There are many inbuilt commands available in NuGet you can find the help by running the command get-help NuGet and explore all possible options.

How to edit .rdlc file to add new column

I have a requirement to add new column in existing report. Unfortunately rdlc file was given by a third party vendor and we don’t have the complete project and dataset to edit the file.

As you are aware .rdlc is just an XML file, once you open that in notepad or any editor you can see what and all contents are visible in reports. But if you start editing the .rdlc file manually it will be a never ending process.

Here we are going to edit .rdlc file by using Visual Studio. Of course you require Visual Studio Business Intelligence to edit .rdlc file, also I am expecting you to have an access to the database to add new field in your stored procedure or SQL query which is executed from .rdlc to populate the data in reports.

Now we will go to step by step procedure.

1. Convert .rdlc to .rdl first—> This is a very simple technique. You just need to change the extension from .rdlc to .rdl. This will automatically convert the file.

2. Open Visual Studio business intelligence and select new—> Project—> Report project.

3. Right Click solution and select Add—>Existing Items—> Locate your renamed .rdl file.

 

4. Double Click .rdl file to open it in design mode.

5. Select the last field or the field near to the new one you are going to add. In this example I am adding the new column as the last one.

6. Right Click on the selected field and select Insert Column. You can opt either left or right depends on your choice.

7. Provide required name for the new fields. Here I am giving the name as Test.

8. Now we will add new field in the DataSet. Click on View menu and select report Data to show the dataset.

9. Right Click Dataset and select DataSet Properties.

6. Click on Add button to add new field in the DataSet.

7. And the last steps is to modify your Stored procedure/SQL query to add the new field you added in the reports.

8. Now it’s the time to convert .rdl to .rdlc. and it is exactly same as the firs step, just change the extension from .rdl to .rdlc.

Just copy the .rdlc file in your project folder where it has been placed earlier and access it from your application…That’s it!!!

If you have any doubt or confusion in above steps you can always contact me using my mail ID or write comment below.