Fransiscus Setiawan

Most people are using “WITH (NOLOCK)” instead of “ (NOLOCK)” and sometimes they asked you to put it “WITH” in front of it. But it is not necessary for the reason below

WITH is not required for 2 reasons:

1. The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND

2. "WITH" keyword is deprecated feature in the future SQL server

Omitting the WITH keyword is a deprecated feature and will be removed in a future version of Microsoft SQL Server. Always specify WITH in new development work, and modify applications that currently omit this keyword.

Link:

http://msdn.microsoft.com/en-us/library/ms187373.aspx