set nocount scope

Posted on 2004-10-24
Last Modified: 2011-09-20
I'm having trouble understanding the scope of a command like SET NOCOUNT ON. I know I want to use this in stored procedures, but do I put this command in the sp's definition or outside the CREATE PROCEDURE command? Does running the command only last for one transaction?
Question by:elmoredaniel
    LVL 15

    Accepted Solution


    yes you are right you put it inside the definition, so for example you might have

    CREATE PROCEDURE get_Table1_p



    SELECT * FROM Table1

    As for whether or not it only lasts for one transaction I am pretty sure that it is specific to the sp that it is included in.

    I know that I use it in every sp I write as it does helps to reduce network traffic by not returning the number of rows affected line when it is not needed.


    LVL 6

    Assisted Solution

    It is a connection level setting, so it lasts while you have that connection to the server.  You could put it in either place I guess.  There is a connection default that you can set all connections that connect to your sql server.  Right click on your server in Enterprise Manager click properties and it is in there.  (It might be set on the database if is not in there but I am pretty sure it is on the server)  If you switch it for the connection I am not to sure what else it effects.  We have always just set it in the procedures.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now