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

Re: Select affected rows

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From	"Bob Barrows" <reb01501@NOSPAMyahoo.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Select affected rows
Date	Tue, 7 Feb 2012 07:30:48 -0500
Organization	A noiseless patient Spider
Lines	31
Message-ID	<jgr5g4$vqh$1@dont-email.me> (permalink)
References	<93b61b75-3623-40cb-943e-84afb1451403@dp8g2000vbb.googlegroups.com>
Injection-Date	Tue, 7 Feb 2012 12:29:56 +0000 (UTC)
Injection-Info	mx04.eternal-september.org; posting-host="vQmQhKLKuh0OvmkK+suVIw"; logging-data="32593"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+2vehRWSqm7i+CxidBER98fRpoBVpaJBc="
X-MimeOLE	Produced By Microsoft MimeOLE V6.00.2900.6157
X-RFC2646	Format=Flowed; Original
X-Antivirus-Status	Clean
X-Newsreader	Microsoft Outlook Express 6.00.2900.5931
X-Antivirus	avast! (VPS 120206-1, 02/06/2012), Outbound message
Cancel-Lock	sha1:StblYgAdKjGfMVZBkSGfFBU61uM=
X-Priority	3
X-MSMail-Priority	Normal
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:926

Show key headers only | 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