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.
Bob Barrows (reb01...@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
omtechguy (omtech...@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.
-- Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Erland Sommarskog wrote:
> Bob Barrows (reb01...@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
