Post reply

T-SQL Set NOCOUNT On and Off

  • November 15, 2007 at 10:13 am

    #377074

    Before Selecting Rows from database tables, all of my T-SQL procedures execute Set NOCOUNT ON. I was wondering when I leave the stored procedure should I add a Set NOCOUNT OFF or is this done automatically when a T-SQL script exists?

    Thanks

  • November 15, 2007 at 10:52 am

    #751597

    the SET NOCOUNT ON command is to affect all of the subsequent commands in the batch you are running....

    if you were to set it off at the end of a stored procedure, and then the procedure exits, just as you expect, it would have no effect, so there's no need to do it from within the procedure. so you are right there is no need to set it to off when you are finished.

    similarly, if you were to create a temp table in a procedure, since it exists just for the duration that the code executes, there is no need to exlicitly drop a #tmp table prior to the procedures completion.

    Someone did a million exection example, where the same code was executed in a proc; one version just exiting and another explicitly dropping the temp table...the one that dropped the temp table was slightly slower, so there's a bit of overhead when you run a command that eventually has no effect...minor, but yeah a slight impact.

    hope that helps!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 16, 2007 at 6:47 am

    #751812

    Thanks very much for the clear and well thought out response.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply