SQL Server Performance Forum – Threads Archive

Setting NOCOUNT off

Hi, I’ve just read the thread on paging records in stored procedures and noticed that on the 4guysfromrolla link that they SET NOCOUNT ON at the end of the procedure, myself I have never really done this although I do SET NOCOUNT OFF for queries in my stored procs, is there any benefit from doing this in the procedure (setting NOCOUNT ON) if you’ve set it on, performance or otherwise? Thanks
Shaun World Domination Through Superior Software

setting nocount off will prevent the rowcount being returned in the result set of the stored procedure, some people say this has a performance benefit. Personally I think the performance boost is almost negligble, and therefore tend not to do this… some connectivity technologies rely on it for exposing certain object properties, and so if you disable it for some sp’s, the developers (or users of the sp) need to be aware of that.

I do SET NOCOUNT ON. I think that all those almost negligble could sum up to something less negligble. The SET NOCOUNT OFF at the end of the procedure is not really important, though generally it is a maybe it is a good practice to change back the settings. What is missing in that stored procedure and is more important is DROP TABLE #TempItems
Even though local temproary table are being dropped when store procedure is completed, it is a good practice to drop them explitly. Another thing regurding that paging example. It is not neccessary to count again the number of rows left
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
) You can easily calculate it using @@ROWCOUNT. Add
SELECT @total_rows = @@ROWCOUNT
right after the insert into the temp table (it must be the first line after), and
SELECT @total_rows = @total_rows – @RecsPerPage * @Page will give you the number of rows left. Bambola.

Hi, I ran some tests with SET NOCOUNT ON and OFF on my local machine (see script below) and what took 15,490 milliseconds with NOCOUNT OFF, took only 1,810 milliseconds with NOCOUNT ON. I expect this difference to be far larger if it is over a network, but I can’t test that at home.
Bambola:
A local temporary table is not dropped automatically when a stored procedure is completed, it is only dropped when the connection that created the temporary table is terminated. Table variables will be automatically destroyed when the stored procedure or batch they were created in finishes (just like ordinary variables). In other words, the scope of a local temporary table is the connection, the scope of a table variable is the batch.
USE Northwind
GO
DECLARE @i INT
DECLARE @start DATETIME
DECLARE @nocount_off INT
DECLARE @nocount_on INT SET NOCOUNT OFF
SET @i = 0
SET @start = GETDATE()
WHILE @i < 10000
BEGIN
DELETE FROM orders WHERE 1=0
SET @i = @i + 1
END
SET @nocount_off = DATEDIFF(ms, @start, GETDATE()) SET NOCOUNT ON
SET @i = 0
SET @start = GETDATE()
WHILE @i < 10000
BEGIN
DELETE FROM orders WHERE 1=0
SET @i = @i + 1
END
SET @nocount_on = DATEDIFF(ms, @start, GETDATE()) SELECT @nocount_off, @nocount_on

Jacco – I totally agree with you about setting NOCOUNT ON, but not about temporary tables. They are dropped once the stored procedure is completed. Check this script and see for yourself [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <pre> – the stored procedure that creates the temp table CREATE PROCEDURE create_temp_table AS CREATE TABLE #temptable(id int) SELECT a.[name] as create_temp_table FROM tempdb..sysobjects a WHERE [name] LIKE ‘%temptable%' – drop table #temptable GO – calls the sproc that created the temp table. – if temp table was not dropped, you should see it here. CREATE PROCEDURE test_temptable_scope AS exec create_temp_table SELECT a.[name] test_temptable_scope FROM tempdb..sysobjects a WHERE [name] LIKE ‘%temptable%' GO SET NOCOUNT ON – run this to test scope EXEC test_temptable_scope GO – clean up DROP PROCEDURE create_temp_table GO DROP PROCEDURE test_temptable_scope GO </pre> As you can see, #temptable was dropped. If however, you will create a temp table in a stored procedure, and call from within the stored procedure another stored that reference the tamp table you have created, you will be able to see it. Check this: <pre> CREATE PROCEDURE test_temptable_scope_2 AS SELECT a.[name] as test_temptable_scope_2 FROM tempdb..sysobjects a WHERE [name] LIKE ‘%temptable2%' GO CREATE PROCEDURE create_temp_table_2 AS CREATE TABLE #temptable2(id int) SELECT a.[name] as test_temptable_scope_2 FROM tempdb..sysobjects a WHERE [name] LIKE ‘%temptable2%' EXEC test_temptable_scope_2 DROP TABLE #temptable2 GO SET NOCOUNT ON – tun this to test the scope EXEC create_temp_table_2 GO – clean up DROP PROCEDURE create_temp_table_2 GO DROP PROCEDURE test_temptable_scope_2 GO </pre> Bambola.

You are right Bambola. I was confused with temporary tables that are created outside stored procedures, through a batch in QA or something similar, they exist for the duration of the connection unless dropped earlier. I find this somewhat inconsistent behaviour though. A stored procedure is supposed to similar to a batch in scope, but it isn’t.

]]>