Microsoft SQL Server 2008 R2 Unleashed- P141:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1344 CHAPTER 37 Locking and Performance table-level isolation hints within the query. Using table-level hints is covered later in this chapter in the section Table Hints for Locking. Read Uncommitted Isolation If you set the Read Uncommitted mode for a session no isolation is provided to the SELECT queries in that session. A transaction that is running with this isolation level is not immune to dirty reads nonrepeatable reads or phantom reads. To set the Read Uncommitted mode for a session you run the following statements from the client T-SQL Use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. ODBC Use the function call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_UNCOMMITTED. OLE DB Use the function call ITransactionLocal StartTransaction with the isoLevel set to ISOLATIONLEVEL_READUNCOMMITTED. ADO Set the IsolationLevel property of the Connection object to adXactReadUncommitted. For applications using the managed namespace call the method and set the IsolationLevel option to ReadUncommitted. You need to be careful when running queries at Read Uncommitted isolation it is possible to read changes that have been made to data that are subsequently rolled back. In essence the accuracy of the results cannot be guaranteed. You should use this mode only when you need to get information quickly from an online transaction processing OLTP database without affecting or being affected by the ongoing updates and when the accuracy of the results is not critical. Read Committed Isolation The Read Committed mode is the default locking-isolation mode for SQL Server. With Read Committed as the transaction isolation level read operations can read pages only for transactions that have already been committed. No dirty reads are allowed. Locks acquired by update transactions are held for the duration of the transaction. However in this mode read requests within the .
