Basic points to be considered when starting with SQL Server – – Part 3

Hello friends, in our last few articles we were discussing about data types, benefits of correct selection. Today, we will discuss about “SET NOCOUNT”.

SET NOCOUNT will stop the message that provides the rows count that a SQL statement affects.

Syntax for this:-

SET NOCOUNT [ON/OFF]
If we will set it to “ON” it will not display the message.
If we will set it to “OFF” it will show that message for each statement.

Now the question that come into my mind is,
What is the difference between ON/OFF value of NOCOUNT other than message display and what are the benefits?

The main benefit of setting it “ON” is the performance enhancement. It increases performance by removing the count message. It will not be noticeable if you have only one statement. But if we have a large set of statements then it will give performance boost.

How exactly it works?
After execution of each statement in stored procedure DONE_IN_PROC is send to client. So when we set it to “ON” it prevents that message. So to have good performance set it to “ON”.
Guys thanks for reading my articles, please keep in touch for more……

<<Previous Post                                                                                          Next Post>>
Written By: Ankit Arora, Sr. Software Engineer, Mindfire Solutions

Advertisements

Posted on February 4, 2014, in SQL and tagged , , , , . Bookmark the permalink. 2 Comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: