What does it mean for these MS SQL Statements?

Posted on 2005-05-10
Last Modified: 2008-03-03

I got the following SQL statements from 2 differnt examples:

USE pubs
DROP TABLE #coffeetabletitles
SET NOCOUNT ON   <----------------
SELECT * INTO #coffeetabletitles
FROM titles
WHERE price < $20
SET NOCOUNT OFF  <-----------------
FROM tempdb..sysobjects
WHERE name LIKE '#c%'

From the above example, I don't know what does it mean with (1) SET NOCOUNT ON and (2) SET NOCOUNT OFF
And these statements are there for what?

// Another example
USE pubs
      WHERE table_name = 'newtitles')
      DROP TABLE newtitles
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
USE pubs
SELECT * INTO newtitles
FROM titles
WHERE price > $25 OR price < $20
SELECT name FROM sysobjects WHERE name LIKE 'new%'
USE master                                                                       <----------------------------
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

Why we have to use the statement: USE master before th statment of "EXEC ... , 'false'\
Why we don't have it before th first EXEC statement

Thanks a lot for any help,


Question by:rfr1tz
    LVL 95

    Assisted Solution

    by:Lee W, MVP

    For the latter question, you're switching databases and using a different stored procedure.
    LVL 9

    Assisted Solution

    Hi rfr1tz,

     Set NoCount On / Off sets Sql to return rows affected

    LVL 11

    Accepted Solution

    From the SQL Server Books online:
    When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

    Basically it stops you having to see messages like (26554 row(s) affected) in the messages window of Query Analyser

    The second statement USE Master changes the database you are using from pubs (the first line of this procedure) to master.
    It is useful if you have a query that references multiple databases and you need to move between them in the execution of your script. You can achieve the same effect by using the database.owner.object sysntax when refering to objects
    LVL 8

    Assisted Solution

    Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

    When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

    Its indicating that you are using another database - which has the data you looking for


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    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.

    761 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

    12 Experts available now in Live!

    Get 1:1 Help Now