To Check the Default Isolation level of SQL Sever we can use
Let's consider couple of scenarios where we want to read uncommited data
- We are deleting alot of records from Table and want to take a look on progress
- We are inserting a lot of records by using SSIS Package, even the records are not committed, we want to see the progress of load.
Sometime we are preparing our Scripts for deployments and we run the statement and then want to see if script worked as expected before committing the transaction.
WITH (NOLOCK) is hint that we can use to read dirty or uncommitted records.
Let's create a table in one of our Database by using below DDL Script and Insert couple of Records
USE TestDB go Create Table dbo.TblCustomer( Id int, NAME VARCHAR(100), Region VARCHAR(100) ) go insert into dbo.TblCustomer values (1,'Aamir',NULL) go Insert into dbo.TblCustomer values (2,'Raza',Null)
Now let's Begin our Transaction in a connection and update the Region Column with value='NA' as shown below.
BEGIN TRAN Update dbo.TblCustomer set Region='NA'
As we can see in Fig 2. Two rows are effected. As we did not Commit the Transaction. The records are still not committed to the table. Let's open a new query windows (Connection/Session) and try to Select the Rows from dbo.tblCustomer table.