Asked  7 Months ago    Answers:  5   Viewed   27 times

Inspired by this question where there are differing views on SET NOCOUNT...

Should we use SET NOCOUNT ON for SQL Server? If not, why not?

What it does Edit 6, on 22 Jul 2011

It suppresses the "xx rows affected" message after any DML. This is a resultset and when sent, the client must process it. It's tiny, but measurable (see answers below)

For triggers etc, the client will receive multiple "xx rows affected" and this causes all manner of errors for some ORMs, MS Access, JPA etc (see edits below)

Background:

General accepted best practice (I thought until this question) is to use SET NOCOUNT ON in triggers and stored procedures in SQL Server. We use it everywhere and a quick google shows plenty of SQL Server MVPs agreeing too.

MSDN says this can break a .net SQLDataAdapter.

Now, this means to me that the SQLDataAdapter is limited to utterly simply CRUD processing because it expects the "n rows affected" message to match. So, I can't use:

  • IF EXISTS to avoid duplicates (no rows affected message) Note: use with caution
  • WHERE NOT EXISTS (less rows then expected
  • Filter out trivial updates (eg no data actually changes)
  • Do any table access before (such as logging)
  • Hide complexity or denormlisation
  • etc

In the question marc_s (who knows his SQL stuff) says do not use it. This differs to what I think (and I regard myself as somewhat competent at SQL too).

It's possible I'm missing something (feel free to point out the obvious), but what do you folks out there think?

Note: it's been years since I saw this error because I don't use SQLDataAdapter nowadays.

Edits after comments and questions:

Edit: More thoughts...

We have multiple clients: one may use a C# SQLDataAdaptor, another may use nHibernate from Java. These can be affected in different ways with SET NOCOUNT ON.

If you regard stored procs as methods, then it's bad form (anti-pattern) to assume some internal processing works a certain way for your own purposes.

Edit 2: a trigger breaking nHibernate question, where SET NOCOUNT ON can not be set

(and no, it's not a duplicate of this)

Edit 3: Yet more info, thanks to my MVP colleague

  • KB 240882, issue causing disconnects on SQL 2000 and earlier
  • Demo of performance gain

Edit 4: 13 May 2011

Breaks Linq 2 SQL too when not specified?

Edit 5: 14 Jun 2011

Breaks JPA, stored proc with table variables: Does JPA 2.0 support SQL Server table variables?

Edit 6: 15 Aug 2011

The SSMS "Edit rows" data grid requires SET NOCOUNT ON: Update trigger with GROUP BY

Edit 7: 07 Mar 2013

More in depth details from @RemusRusanu:
Does SET NOCOUNT ON really make that much of a performance difference

 Answers

40

Ok now I've done my research, here is the deal:

In TDS protocol, SET NOCOUNT ON only saves 9-bytes per query while the text "SET NOCOUNT ON" itself is a whopping 14 bytes. I used to think that 123 row(s) affected was returned from server in plain text in a separate network packet but that's not the case. It's in fact a small structure called DONE_IN_PROC embedded in the response. It's not a separate network packet so no roundtrips are wasted.

I think you can stick to default counting behavior almost always without worrying about the performance. There are some cases though, where calculating the number of rows beforehand would impact the performance, such as a forward-only cursor. In that case NOCOUNT might be a necessity. Other than that, there is absolutely no need to follow "use NOCOUNT wherever possible" motto.

Here is a very detailed analysis about insignificance of SET NOCOUNT setting: http://daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount/

Tuesday, June 1, 2021
 
capsid
answered 7 Months ago
76

You want the OUTPUT clause

UPDATE Items SET Clicks = Clicks + 1
OUTPUT INSERTED.Name
WHERE Id = @Id
Sunday, June 13, 2021
 
danjah
answered 6 Months ago
14

Informational messages (like the rows affected count info) are reported in ADO.Net through the SqlConnection.InfoMessage event. Add a delegate to the event and will be invoked whenever the server transmits an informational message (ie. any error message with severity bellow 10).

there is no way to associate informational messages like afffected count info with the source. You're going to have to do it based on knowledge of the logic and understand that the first message refers to the first update, the second message to the second update etc.

Relying on affected rows count in the client is generaly a bad practice. The many issues ORM layers like NHibernate and ADO.Net datasets have when SET NOCOUNT ON is turned on just shows how problematic this practice is.

Wednesday, September 1, 2021
 
ShadowZzz
answered 3 Months ago
13

Use @@RowCount. It's explicit and transparent, it is entirely controlled by your code rather than a built-in behaviour.

The NOCOUNT option can be manually set to default to ON (Optons>Query Execution>SQL Server>Advanced). If you set it this way but then declare SET NOCOUNT OFF in your stored procedure then that local setting takes precedence.

Thursday, October 7, 2021
 
DeanB_Develop
answered 2 Months ago
85

If your SqlConnection was already open, then SqlDataAdapter should use it as is (i.e. without closing/opening it).

One possibility as to why your stored procs cannot see the temp table, is that ADO.NET executed your first SqlCommand (used to create the temp table), with a sp_executesql call. That would mean that the temp table gets created within the scope of the stored proc sp_executesql, and would not be visible to subsequent commands, even though you are using the same connection. To check, you could run a Sql Profiler trace - if you see sp_executesql being used for your first SqlCommand, then you'll have a problem.

This comment at: Sql Server temporary table disappears may be relevant:

I honestly think it has to do with the way the SqlCommand text is structured. If it's a simple select into, with no parameters, then it may be run as a simple select statement, so it won't be wrapped in an SqlProcedure like 'sp_executesql', so it will remain visible to subsequent queries using that same SqlCommand and SqlConnection object. On the other hand, if it's a complex statement, the temp table may be created within a stored procedure like 'sp_executesql', and will go out of scope by the time the command is finished. – Triynko Feb 25 '15 at 21:10

If ADO.NET is indeed using sp_executesql for your table creation command, then you might be able to coax it into not using it, by breaking up the command into 2 SqlCommands: one to drop the temp table if it exists, and another to just create the temp table.

EDIT : on a side note, this code:

IF OBJECT_ID('#CRM2Oxa_ID_MAPPING') IS NOT NULL
       DROP TABLE #CRM2Oxa_ID_MAPPING

should probably be:

IF OBJECT_ID('tempdb..#CRM2Oxa_ID_MAPPING') IS NOT NULL
       DROP TABLE #CRM2Oxa_ID_MAPPING

otherwise OBJECT_ID('#CRM2Oxa_ID_MAPPING') will always be null (unless you are already in the temp database).

EDIT 2 : here's some simple code which works for me:

        DataSet ds = new DataSet();

        using(SqlConnection conn = new SqlConnection("YourConnectionString"))
        {
            conn.Open();

            string str = "if object_id('tempdb..#mytest') is not null drop table #mytest; create table #mytest (id int)";

            // create temp table
            using(SqlCommand cmdc = new SqlCommand(str, conn))
            {
                cmdc.ExecuteNonQuery(); 
            }

            // insert row
            using (SqlCommand cmdi = new SqlCommand("insert #mytest (id) values (1)", conn))
            {
                cmdi.ExecuteNonQuery();
            }

            // use it
            using (SqlCommand cmds = new SqlCommand("dbo.mytestproc", conn))
            {
                cmds.CommandType = CommandType.StoredProcedure;
                cmds.Parameters.Add("@id", SqlDbType.Int).Value = 1;
                cmds.Connection = conn;

                using (SqlDataAdapter da = new SqlDataAdapter(cmds))
                {
                    da.Fill(ds);
                }
            } 

            // clean up - drop temp table
            string strd = "if object_id('tempdb..#mytest') is not null drop table #mytest";
            using (SqlCommand cmdd = new SqlCommand(strd, conn))
            {
                cmdd.ExecuteNonQuery();
            }
        }

        MessageBox.Show("done, num rows " + ds.Tables[0].Rows.Count);

The stored proc looks like this:

create proc dbo.mytestproc(@id int)
as
select * from #mytest where id = @id
GO

At the end, it displays : "done, num rows 1"

Wednesday, October 20, 2021
 
Ivan Stoev
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share