Inspired by this question where there are differing views on SET NOCOUNT...
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.
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