none
NOLOCK on INSERTED pseudo table

    Question

  • I was just reviewing an SQL 2008 trigger and it contained a number of references to the INSERTED and DELETED pseudo tables with the NOLOCK optimizer hint.

    Does the NOLOCK optimizer hint do anything with either the INSERTED or DELETED pseudo tables other than maybe consume extra time to type out the hint?

    Dave

    Wednesday, October 16, 2013 6:52 PM
    Moderator

Answers

  • Does the NOLOCK optimizer hint do anything with either the INSERTED or DELETED pseudo tables ...

    ... Dave

    Hello Kent (or Dave?),

    The virtual table INSERTED/DELETED are only available within the scope of the trigger; no other session could access it and so no other could create a lock on those tables; so the table hint WITH (NOLOCK) makes not difference.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, October 16, 2013 6:57 PM

All replies

  • Does the NOLOCK optimizer hint do anything with either the INSERTED or DELETED pseudo tables ...

    ... Dave

    Hello Kent (or Dave?),

    The virtual table INSERTED/DELETED are only available within the scope of the trigger; no other session could access it and so no other could create a lock on those tables; so the table hint WITH (NOLOCK) makes not difference.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, October 16, 2013 6:57 PM
  • Thank you, Olaf; yeah, I looked at the nolock hint in this case as being basically pointless.

    How have you been?


    Wednesday, October 16, 2013 7:36 PM
    Moderator