Does anyone know for certain if SQL 2005 supports the nolock hint on a plan guide? Gettting a complaint about hint syntax that it hates in 2005 but loves in 2008+
I'm not sure about plan guides, but in regular queries it's legal in 2005. If you are using aliases the nolock needs to come after the alias.
This will generate a syntax error:
FROM dbo.Product WITH(NOLOCK) p
FROM dbo.Product p WITH(NOLOCK)
Be aware that using NOLOCK without the WITH is deprecated and will stop working in some future version of SQL. That said, most of the folks around here will tell you it's best not to use NOLOCK at all as it can cause inconsistent results.
It's been a while, but I'm pretty sure that NOLOCK was only a table hint in 2005, not a query hint. That means you can't use it in plan guide as the guides only support query hints.
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
The Scary DBA Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Not sure if this is correct, but I see this: "Any combination of valid query hints can be used in a plan guide. "
No 2005 instance to test, but I hope someone can and post back here.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)