Hi all,
I want to share knowledge with you regarding NOLOCK and With (NOLOCK) hint in SQL queries we use in our day-to-day work/activities.

According to MSDN,
“In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. Table hints also must be specified with parentheses.

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. When these table hints are specified without the WITH keyword, the hints should be specified alone.”

Source:
http://msdn.microsoft.com/en-US/library/ms187373%28v=SQL.90%29.aspx

Conclusion & Advice:
While development always use “WITH” keyword while providing hints.
(I guess, any fixed patterns in SQL query will definitely make SQL parser’s job easier and hence results into efficient query processing.)

Reason:
Hints without “WITH” keyword feature will be deprecated in SQL Server 2008 onwards.

Hope this will be helpful.
Thanks, Khilit