Temp Variable Assignment WITH (NOLOCK)

Asked Jun 16 '17 at 15:49

Active Jun 16 '17 at 15:49

Viewed 334 times

Is there a speed benefit to using WITH (NOLOCK) in temporary variable assignment. Meaning:

This

DECLARE @oldestPerson INT = (SELECT TOP 1 Age FROM tbl_Persons)

Verses

DECLARE @oldestPerson INT = (SELECT TOP 1 Age FROM tbl_Persons WITH (NOLOCK))

given that NOLOCK and READUNCOMMITTED are not allowed for UPDATE, INSERT and DELETE statements https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql. Does temporary variable assignment qualify under this rule as an INSERT?

asked Jun 16 '17 at 15:49

Joe McCarty

1

[Effect of NOLOCK hint in SELECT statements](https://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements) - FYI without an ORDER BY there is no guarantee what row you will get. – Alex K. Jun 16 '17 at 15:52
Why do you want to use `NOLOCK` in the first place? – alroc Jun 16 '17 at 15:53
There is no effect unless the table is locked – Pரதீப் Jun 16 '17 at 16:01

0 Answers0