Geeks With Blogs

News View David Douglass's profile on LinkedIn

My Presentations
David Douglass .NET on My Mind

In SQL Server T-SQL, you can follow a table name with a table hint.  The formal syntax is:


WITH (table hint)


But, for backward compatibility reasons, WITH is optional.  So, these are equivalent:


select * from Person.Contact with (nolock);

select * from Person.Contact (nolock);


But, what does this do?


select * from Person.Contact nolock;


Most likely, not what you want.  This query aliases Person.Contact as nolock.  Unlike the first 2 queries, it will obtain a shared lock on all the data it touches.  This is easy to miss in testing.  The query works and unless you explicitly test your locking scenarios, you probably won't catch this.

Interestingly, the SQL Server 2005 Books On Line state "The WITH keyword is not mandatory with hints when the database has a compatibility level of 80 or earlier."  But in testing with the copy of AdventureWorks that ships with SQL Server 2005, even though that database is at the SQL Server 2005 (90) compatibility level, the query works as expected without the WITH keyword.

Posted on Saturday, May 19, 2007 9:03 AM | Back to top

Comments on this post: SQL Server Gotcha

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © David Douglass | Powered by: