Answer / michael

Advantage:
applying nolock in select statement will increase
concurreny and performance in fetching.

Disadvantage:
it does'nt consider the transaction scenario, even if the
transaction is under process not yet commited or rollback
it will fetch the current record
eg

begin tran
insert into tablename
values('1','asdf')

select * from tablename with (nolock)

-- it will display the table with inserted record.
but the transaction is not completed, it can be rollback
so it became dirty read.

Answer / naren

Using NOLOCK politely asks SQL Server to ignore locks and
read directly from the tables. This means you completely
circumvent the lock system, which is a major performance
and scalability improvement. However, you also completely
circumvent the lock system, which means your code is living
dangerously. You might read the not-necessarily-valid
uncommitted modifications of a running transaction. This is
a calculated risk.

Answer / guest

Do not issue shared locks and do not honor exclusive locks.
When this option is in effect, it is possible to read an
uncommitted transaction or a set of pages that are rolled
back in the middle of a read. Dirty reads are possible. Only
applies to the SELECT statement.

What new changes are being made in SQL Server?

0 Answers   Blue Star,

---

How to provide Security for package?

1 Answers   Convergys,

---

Why do we use stored procedures in sql server?

0 Answers  

---

How to provide default values to function parameters?

0 Answers  

---

Why should one not prefix user stored procedures with ‘sp_’?

0 Answers  

---

What are the main differences between #temp tables and @table variables and which one is preferred?

0 Answers  

---

Define tool Manage Statistics in SQL Server 2000 query ?

0 Answers   NA,

---

What is the difference between OLEDB and ODBC

4 Answers   CFCI,

---

What will be the value of @@fetch_status if a row that was a part of the cursor resultset has been deleted from the database after the time the stored procedure that opened the cursor was executed?

0 Answers  

---

How do I create an extended event in sql server?

0 Answers  

---

Please tell me some knowledge about Clustering and how to add clustering?

1 Answers  

---

what is denormalization and when would you go for it? : Sql server database administration

0 Answers  

---