Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #926

Re: Select affected rows

Show all headers | View raw

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. 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Select affected rows omtechguy <omtechguy@gmail.com> - 2012-02-05 01:19 -0800
  Re: Select affected rows bradbury9 <ray.bradbury9@gmail.com> - 2012-02-05 05:04 -0800
  Re: Select affected rows "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-02-07 07:30 -0500
    Re: Select affected rows Erland Sommarskog <esquel@sommarskog.se> - 2012-02-07 12:54 +0000
      Re: Select affected rows "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-02-07 10:33 -0500
    Re: Select affected rows bradbury9 <ray.bradbury9@gmail.com> - 2012-02-07 07:03 -0800
  Re: Select affected rows Erland Sommarskog <esquel@sommarskog.se> - 2012-02-07 12:56 +0000

csiph-web