select * from Tablename & select * from Tablename(NOLOCK)

Posted by Johnseelan under Sql Server on 2/2/2011 | Points: 10 | Views : 3979 | Status : [Member] | Replies : 6
what is the Difference between

select * from Tablename
&
select * from Tablename(NOLOCK)




Responses

Posted by: Karthikanbarasan on: 2/2/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Both the statements are almost the same in returning the values, But the main use of giving NOLOCK is NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

Thanks
Karthik
www.f5Debug.net

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

Posted by: Prabhakar on: 2/3/2011 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hi Johnseelan

i think applying nolock in select statement will increase concurreny and performance in fetching.
not a big difference ...


Best Regard's
Prabhakar

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

Posted by: Sathya4260 on: 2/3/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Second Statement results in a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data.
It allow es Shared locks that is simultaneous access,

Statement 1 blocks from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete.

Mark this as answer, it got the answer to Ur question

Sathish Kumar S

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

Posted by: Madhu.b.rokkam on: 2/3/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Because there were no shared locks on the table applying NOLOCK makes most
SELECT statements execute faster there by improving the performance.

Thanks and Regards
Madhu

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

Posted by: Sathya4260 on: 2/3/2011 [Member] Starter | Points: 25

Up
0
Down
I think ur wrong madhu,

Shared locks are applied on NOLOCK only...
Can you explain ur point of view clearly...

Sathish Kumar S

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

Posted by: Madhu.b.rokkam on: 2/3/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

Another benefit of the statement is that it allows you to keep the database engine from issuing locks against the tables in your queries,
and this increases concurrency and performance because the database engine does not have to maintain the shared locks involved

And the disadvantage is that there is possibility of dirty reads since the statement does not issue any locks against the tables being read.

Thanks and Regards
Madhu

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

Login to post response