November 15, 2011 at 6:57 am
What is the use of Table Hint in SQL like NOLOCK, READUNCOMMITTED
please explain this with example. i'm newbie
also why it cannot be specified for tables modified by insert, update, or delete operations???
November 15, 2011 at 7:11 am
Start with never use it assuming you care about data quality and you'll be better off.
There are very specific case uses but they are rare.
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
November 15, 2011 at 7:17 am
ashuthinks (11/15/2011)
also why it cannot be specified for tables modified by insert, update, or delete operations???
Because data modifications have to take exclusive locks to protect data integrity. Read uncommitted (as its name implies) only affects read operations.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2011 at 9:46 am
As mention above use with caution.
It all depends on what your database is used for. The organisation I currently work for read performance is paramount and every attention gets paid durring the Design \ Development \Test ..... stages.
However as a coding standard we always specify with NOLOCK hints for select statements as blocking and waits caused by locking is to be minimized at all cost. This is based on a business decision and suits the industry we operate in. As dirty reads are acceptable.
Where as my last employer was a clinical IT supplier where the most upto date data is paramount so the use was banned.
Again it depends on the situation but table hints do have there place.
MCITP SQL 2005, MCSA SQL 2012
November 15, 2011 at 9:51 am
What industry are you in?
November 15, 2011 at 10:20 am
ashuthinks (11/15/2011)
What is the use of Table Hint in SQL like NOLOCK, READUNCOMMITTEDplease explain this with example. i'm newbie
also why it cannot be specified for tables modified by insert, update, or delete operations???
Duplicate Thread: what is the use of : WITH (NOLOCK, READUNCOMMITTED)
http://www.sqlservercentral.com/Forums/Topic1205185-391-1.aspx#bm1205352
November 17, 2011 at 5:52 am
Would rather not say but falls into the general category of Online advertising space.
MCITP SQL 2005, MCSA SQL 2012
November 17, 2011 at 6:14 am
RTaylor2208 (11/15/2011)
As mention above use with caution.It all depends on what your database is used for. The organisation I currently work for read performance is paramount and every attention gets paid durring the Design \ Development \Test ..... stages.
However as a coding standard we always specify with NOLOCK hints for select statements as blocking and waits caused by locking is to be minimized at all cost. This is based on a business decision and suits the industry we operate in. As dirty reads are acceptable.
Where as my last employer was a clinical IT supplier where the most upto date data is paramount so the use was banned.
Again it depends on the situation but table hints do have there place.
If read performance is paramount, and you want the speed benefits and lack of blocking from nolock, why not snapshot isolation? Read Committed Snapshot Isolation will make tempdb grow a bit (how much depends on a lot of factors, mainly frequency and size of updates and deletes), but gives all the speed benefits of nolock, without the dirty reads drawbacks. I've seen dirty reads combined with connection pooling cause database corruption.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 17, 2011 at 8:52 am
GSquared, I hadn't really considered that as an option, but will have a look thanks for the info
MCITP SQL 2005, MCSA SQL 2012
November 17, 2011 at 1:22 pm
OP, please don't start multiple threads for the same discussion, it causes confusion and results in too much redundancy. Refer to http://www.sqlservercentral.com/Forums/Topic1205185-391-1.aspx
Edit: Sorry I was a bit loud earlier...
Jared
CE - Microsoft
November 17, 2011 at 2:39 pm
And late! 😀
Dev (11/15/2011)
ashuthinks (11/15/2011)
What is the use of Table Hint in SQL like NOLOCK, READUNCOMMITTEDplease explain this with example. i'm newbie
also why it cannot be specified for tables modified by insert, update, or delete operations???
Duplicate Thread: what is the use of : WITH (NOLOCK, READUNCOMMITTED)
http://www.sqlservercentral.com/Forums/Topic1205185-391-1.aspx#bm1205352
November 17, 2011 at 2:52 pm
Ninja's_RGR'us (11/17/2011)
And late! 😀Dev (11/15/2011)
ashuthinks (11/15/2011)
What is the use of Table Hint in SQL like NOLOCK, READUNCOMMITTEDplease explain this with example. i'm newbie
also why it cannot be specified for tables modified by insert, update, or delete operations???
Duplicate Thread: what is the use of : WITH (NOLOCK, READUNCOMMITTED)
http://www.sqlservercentral.com/Forums/Topic1205185-391-1.aspx#bm1205352
Touche Ninja... Touche...
Jared
CE - Microsoft
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply