|
| What you need to know about SET NOCOUNT ON in ADO.NET |
|
|
|
|
| Messages |
|
Related Types |
This message was discovered on ASPFriends.com 'aspngdata' list.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.
| David Sceppa |
Here's the scoop. It's a long message, so are the highlights first:
If you're submitting updates using DataAdapters and you're working with =
stored procedures and/or triggers that modify the contents of your =
database, you must make sure you set NOCOUNT properly. Otherwise, the =
DataAdapter will not correctly determine whether the intended update =
succeeded or failed.
The solution is to make intelligent use of the NOCOUNT setting to =
ensure that only the query that the client cares about returns messages =
about the number of rows it affected.
The Microsoft Press title "Microsoft ADO.NET" will cover this and many =
other common updating scenarios (retrieving new auto-increment and =
timestamp values, submitting hierarchical updates, detecting conflicts, =
etc.) and includes examples in both VB.NET and C#. =20
Now back to the actual technical content.
SET NOCOUNT ON suppresses the "(n row(s) affected)" messages that SQL =
Server returns for subsequent queries.
How does this affect ADO.NET?
If you're just calling stored procedures manually from ADO.NET, this =
setting will affect the return value for ExecuteNonQuery and the =
RecordsAffected property on the DataReader generated by ExecuteReader. =
Use SET NOCOUNT ON/OFF intelligently so the call to your stored =
procedure will receive the desired value for RecordsAffected.
If you want your application to receive and interpret the REAL results =
of the stored procedure call, you should make sure that only the query =
that modifies the desired row will return information about the number =
of rows affected. Here's an example of just such a stored procedure =
that turns off the "rows affected" messages for all queries except the =
one that the client application actually cares about.
SET NOCOUNT ON
INSERT INTO LogTable (Description) VALUES ('About to modify a row')
SET NOCOUNT OFF
UPDATE MyTable SET ...
SET NOCOUNT ON
IF @@ROWCOUNT =3D 1
INSERT INTO LogTable (Description) VALUES ('Update succeeded')
ELSE
INSERT INTO LogTable (Description) VALUES ('Update failed')
Now this stored procedure will report that it modified one row if the =
desired update succeeds and zero if it fails. If we had used the =
default (NOCOUNT OFF), a successful call to this stored procedure would =
report that it modified three rows. A call that fails to actually =
update the desired row would report that it modified two rows.
The same premise holds true for triggers. If your triggers add, update =
or delete rows and you don't want your client application to receive the =
"rows affected" messages, use SET NOCOUNT ON in your triggers.
What if you're submitting changes via a DataAdapter? The DataAdapter =
doesn't inherently know whether or not the update really succeeded. If =
the database returns a message saying that the query modified zero rows, =
it assumes the update attempt failed. Otherwise, it assumes success.
This means that if you're working with stored procedures and/or =
triggers that modify the contents of your database, you must make sure =
you set NOCOUNT properly to ensure that the DataAdapter understands =
whether the intended update succeeded or failed.
You could tell the DataAdapter to submit updates through the sample =
stored procedure I just described. But if the update attempt fails, the =
stored procedure will report that two rows were modified. The =
DataAdapter will interpret this message as a successful update. It =
doesn't know that for this particular stored procedure, modifying two =
rows implies failure but modifying three rows implies success.
If you suppress all "rows affected" messages, that won't help either. =
The stored procedure will report -1 row(s) affected. ADO.NET interprets =
that as success as well, regardless of whether or not the update attempt =
succeeded.
The solution is to make intelligent use of the NOCOUNT setting to =
ensure that only the query that the client cares about returns messages =
about the number of rows it affected.
If you simply want to call the stored procedure and the client =
application will not check the number of records it affects, then =
suppressing the "rows affected" messages by using SET NOCOUNT ON can =
improve performance slightly.
I hope this information proves helpful.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties, and confers no =
rights.
You assume all risk for your use. =A9 2001 Microsoft Corporation. All =
rights=20
reserved.
-----Original Message-----
From: Ben Lowery [mailto:Click here to reveal e-mail address]
Sent: Monday, November 26, 2001 3:03 PM
To: aspngdata
Subject: [aspngdata] What's the scoop on SET NOCOUNT ON
I see this pop up every now and then, and I was wondering:
a) what does it do?
b) is it a good thing to do if i don't care how many records are =
affected by
a query?
i could run some tests on my own, but i'm really curious if anyone else =
out
there has used this option much, and what they're experiences with it =
are.
thanks,
--b
|
|
|
| |
|
| |
| |
| David L. Penton (VIP) |
So what of any network traffic that is caused (or perhaps was caused in ADO
not .NET)? To me, if I want a count for Records Affected, I should use an
output parameter. Having all of the NOCOUNT statements interspersed in
procedures and triggers seems very counter productive. ADO.NET is also
depending on properties that may or may not exist in the particular database
platform that it is communicating with, or that particular behavior may
change in future versions of components/db technology (speculation).
Perhaps I am not seeing clearly on this, but retrieving a scalar would seem
more robust (scaleable) than depending on the developer to have NOCOUNT
placed strategically in a procedure.
David L. Penton, MCP
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address / Click here to reveal e-mail address
Do you have the VBScript Docs or SQL BOL installed? If not, why not?
http://msdn.microsoft.com/scripting/vbscript/download/vbsdoc.exe
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
-----Original Message-----
From: David Sceppa [mailto:Click here to reveal e-mail address]
Here's the scoop. It's a long message, so are the highlights first:
If you're submitting updates using DataAdapters and you're working with
stored procedures and/or triggers that modify the contents of your database,
you must make sure you set NOCOUNT properly. Otherwise, the DataAdapter
will not correctly determine whether the intended update succeeded or
failed.
The solution is to make intelligent use of the NOCOUNT setting to ensure
that only the query that the client cares about returns messages about the
number of rows it affected.
The Microsoft Press title "Microsoft ADO.NET" will cover this and many
other common updating scenarios (retrieving new auto-increment and timestamp
values, submitting hierarchical updates, detecting conflicts, etc.) and
includes examples in both VB.NET and C#.
Now back to the actual technical content.
SET NOCOUNT ON suppresses the "(n row(s) affected)" messages that SQL
Server returns for subsequent queries.
How does this affect ADO.NET?
If you're just calling stored procedures manually from ADO.NET, this
setting will affect the return value for ExecuteNonQuery and the
RecordsAffected property on the DataReader generated by ExecuteReader. Use
SET NOCOUNT ON/OFF intelligently so the call to your stored procedure will
receive the desired value for RecordsAffected.
If you want your application to receive and interpret the REAL results of
the stored procedure call, you should make sure that only the query that
modifies the desired row will return information about the number of rows
affected. Here's an example of just such a stored procedure that turns off
the "rows affected" messages for all queries except the one that the client
application actually cares about.
SET NOCOUNT ON
INSERT INTO LogTable (Description) VALUES ('About to modify a row')
SET NOCOUNT OFF
UPDATE MyTable SET ...
SET NOCOUNT ON
IF @@ROWCOUNT = 1
INSERT INTO LogTable (Description) VALUES ('Update succeeded')
ELSE
INSERT INTO LogTable (Description) VALUES ('Update failed')
Now this stored procedure will report that it modified one row if the
desired update succeeds and zero if it fails. If we had used the default
(NOCOUNT OFF), a successful call to this stored procedure would report that
it modified three rows. A call that fails to actually update the desired
row would report that it modified two rows.
The same premise holds true for triggers. If your triggers add, update or
delete rows and you don't want your client application to receive the "rows
affected" messages, use SET NOCOUNT ON in your triggers.
What if you're submitting changes via a DataAdapter? The DataAdapter
doesn't inherently know whether or not the update really succeeded. If the
database returns a message saying that the query modified zero rows, it
assumes the update attempt failed. Otherwise, it assumes success.
This means that if you're working with stored procedures and/or triggers
that modify the contents of your database, you must make sure you set
NOCOUNT properly to ensure that the DataAdapter understands whether the
intended update succeeded or failed.
You could tell the DataAdapter to submit updates through the sample stored
procedure I just described. But if the update attempt fails, the stored
procedure will report that two rows were modified. The DataAdapter will
interpret this message as a successful update. It doesn't know that for
this particular stored procedure, modifying two rows implies failure but
modifying three rows implies success.
If you suppress all "rows affected" messages, that won't help either. The
stored procedure will report -1 row(s) affected. ADO.NET interprets that as
success as well, regardless of whether or not the update attempt succeeded.
The solution is to make intelligent use of the NOCOUNT setting to ensure
that only the query that the client cares about returns messages about the
number of rows it affected.
If you simply want to call the stored procedure and the client application
will not check the number of records it affects, then suppressing the "rows
affected" messages by using SET NOCOUNT ON can improve performance slightly.
I hope this information proves helpful.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.
-----Original Message-----
From: Ben Lowery [mailto:Click here to reveal e-mail address]
I see this pop up every now and then, and I was wondering:
a) what does it do?
b) is it a good thing to do if i don't care how many records are affected by
a query?
i could run some tests on my own, but i'm really curious if anyone else out
there has used this option much, and what they're experiences with it are.
thanks,
--b
|
|
|
| |
|
|
| |
|
| |
| David Sceppa |
David,
Using an output parameter is more efficient, but the DataAdapter will =
not know to use that information to determine whether an update attempt =
succeeded or failed. If you're not relying on the DataAdapter to submit =
updates then I agree that this would be the best solution.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties, and confers no =
rights.
You assume all risk for your use. =A9 2001 Microsoft Corporation. All =
rights=20
reserved.
-----Original Message-----
From: David L. Penton [mailto:Click here to reveal e-mail address]
Sent: Tuesday, November 27, 2001 3:21 PM
To: aspngdata
Subject: [aspngdata] RE: What you need to know about SET NOCOUNT ON in
ADO.NET
So what of any network traffic that is caused (or perhaps was caused in =
ADO
not .NET)? To me, if I want a count for Records Affected, I should use =
an
output parameter. Having all of the NOCOUNT statements interspersed in
procedures and triggers seems very counter productive. ADO.NET is also
depending on properties that may or may not exist in the particular =
database
platform that it is communicating with, or that particular behavior may
change in future versions of components/db technology (speculation).
Perhaps I am not seeing clearly on this, but retrieving a scalar would =
seem
more robust (scaleable) than depending on the developer to have NOCOUNT
placed strategically in a procedure.
David L. Penton, MCP
JCPenney Technical Specialist / Lead
|
|
|
| |
|
|
| |
|
| |
| David L. Penton (VIP) |
David,
I just wanted to make sure that there wasn't a recommendation to *always*
use this methodology. That clears it up for me. Thanks for your concise
explanation.
David L. Penton, MCP
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address / Click here to reveal e-mail address
Do you have the VBScript Docs or SQL BOL installed? If not, why not?
http://msdn.microsoft.com/scripting/vbscript/download/vbsdoc.exe
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
-----Original Message-----
From: David Sceppa [mailto:Click here to reveal e-mail address]
David,
Using an output parameter is more efficient, but the DataAdapter will not
know to use that information to determine whether an update attempt
succeeded or failed. If you're not relying on the DataAdapter to submit
updates then I agree that this would be the best solution.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.
-----Original Message-----
From: David L. Penton [mailto:Click here to reveal e-mail address]
So what of any network traffic that is caused (or perhaps was caused in ADO
not .NET)? To me, if I want a count for Records Affected, I should use an
output parameter. Having all of the NOCOUNT statements interspersed in
procedures and triggers seems very counter productive. ADO.NET is also
depending on properties that may or may not exist in the particular database
platform that it is communicating with, or that particular behavior may
change in future versions of components/db technology (speculation).
Perhaps I am not seeing clearly on this, but retrieving a scalar would seem
more robust (scaleable) than depending on the developer to have NOCOUNT
placed strategically in a procedure.
|
|
|
| |
|
|
| |
|
|
|
|
|
|
|
|
BootFX
Reliable and powerful .NET application framework. |
|
|
|
|
|
|
