Select affected rows

[omtechguy]

Hi,

is there an option to get the identities of the affected rows and not
only a counter that mention the number of the affected rows?

Another question, is there an (NOLOCK) equivalent option for update
statement?

[bradbury9]

Rows affected for select? there isn't any. But you can declare a table
variable and get into that table the rows affected

There is no sense for nolock with update statements, but you can try
to lock only in a per-row basis, using the rowlock command

[Bob Barrows]

omtechguy wrote:
> Hi,
>
> is there an option to get the identities of the affected rows and not
> only a counter that mention the number of the affected rows?

It depends on the version of SQL Server you are using. SQL 2008 introduced
the OUTPUT clause that you can read about here:
http://msdn.microsoft.com/en-us/library/ms177564.aspx

With earlier versions, you needed to create a trigger that selected the
contents of the temporary inserted table
(http://technet.microsoft.com/en-us/library/ms191300.aspx), but of course,
that would fire for all update statements, not particular ones. Of courrse,
you could crate and then disable it, and for updates where you need to see
the affected rows, enable the trigger before running them, and disable it
after.
http://technet.microsoft.com/en-us/library/ms182706.aspx

>
> Another question, is there an (NOLOCK) equivalent option for update
> statement?

No. Of course not. This question is really fundamental and leads me to
suspect that you have not done your homework. At the very least, you should
get a book about SQL Server and read it, maybe one of Davidson's, or
Delaney's.
You could attempt to specify ROWLOCK, but that is the default, and if your
query affects too many rows, it will escalate to a table lock anyways.

[Erland Sommarskog]

Bob Barrows (reb0...@NOSPAMyahoo.com) writes:
> It depends on the version of SQL Server you are using. SQL 2008 introduced
> the OUTPUT clause that you can read about here:
> http://msdn.microsoft.com/en-us/library/ms177564.aspx

The OUTPUT clause was added in SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

omtechguy (omte...@gmail.com) writes:
> Another question, is there an (NOLOCK) equivalent option for update
> statement?

You can use NOLOCK in an UPDATE statement, even with the table you are
updating. But of course it does not have any effect.

Overall, NOLOCK is nothing to use at routine, but you need careful
understanding of the consequences. Not only can you happen to read
uncommitted data, but you also fail to read existing and committed data,
which can lead to seriously incorrect results.

If you have problems with locking, you should explore the database setting
READ_COMMITTED_SNAPSHOT. When this setting is in effect, readers to not
block writers and vice versa.

[bradbury9]

> > is there an option to get the identities of the affected rows and not
> > only a counter that mention the number of the affected rows?
>
> It depends on the version of SQL Server you are using. SQL 2008 introduced
> the OUTPUT clause that you can read about here:http://msdn.microsoft.com/en-us/library/ms177564.aspx

Thanks on the OUTPUT clause info, was not aware of it (lots of sql
server 6 and 2000) and seems useful.

[Bob Barrows]

Erland Sommarskog wrote:
> Bob Barrows (reb0...@NOSPAMyahoo.com) writes:
>> It depends on the version of SQL Server you are using. SQL 2008
>> introduced the OUTPUT clause that you can read about here:
>> http://msdn.microsoft.com/en-us/library/ms177564.aspx
>
> The OUTPUT clause was added in SQL 2005.

Of course! My mistake.