with(nolock) in sql server 2008

Posted by Nadh123 under Sql Server on 3/25/2013 | Points: 10 | Views : 1544 | Status : [Member] | Replies : 3
any one explain about with(nolock) in sql server 2008

Mahendra
91-9908699686



Responses

Posted by: Jayakumars on: 3/25/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

nolock option select query working fastly .

May be more than system access one db so some Traffic issue so we are put nolock option in select query

ex:

Select * from Table1(NOLOCK);

Mark as Answer if its helpful to you

Nadh123, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prateekagra on: 3/29/2013 [Member] Starter | Points: 25

Up
0
Down
NOlock doesnt mean it will run the query fastly,

It means query will run untill the result will not come. and it will not create deadlock on the query


Actually it will lock the scheme of the table and run until or unless it will not give the result.

e.g of writing the nolock
select * from table with (Nolock) is the query

Prateek Agarwal
DBA

Nadh123, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeraaj.Sharma on: 6/27/2013 [Member] Starter | Points: 25

Up
0
Down



Oh dear horrible Answers


Nolock is a table hint , which request to query optimizer the get me the data and i don`t care if any operation
(delete update insert) happening on this table. and allow dirty reading,
so what data you get with the nolock might not be consistent.

its a request only to query optimizer, In some cases query optimizer does`t look at what hint you submitted.




By default database isolation is Read committed that means if any (delete update insert) then do not select the data from the objects if locked.
here i refereed objects not table because if indexed are on the table the query optimizer look data in the index






Visit my blog
http://www.tutorialsqlserver.com/




Nadh123, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response