April 9, 2019 at 5:11 pm
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+
April 9, 2019 at 5:42 pm
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:
SELECT *
FROM dbo.Product WITH(NOLOCK) p
This won't:
SELECT *
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.
April 10, 2019 at 1:12 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 10, 2019 at 2:40 pm
Not sure if this is correct, but I see this: "Any combination of valid query hints can be used in a plan guide. "
in https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms189854(v%3dsql.90)
No 2005 instance to test, but I hope someone can and post back here.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply