SET NOCOUNT ON usage

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