Database Simplified Headline Animator

Database Simplified

Wednesday 31 August 2011

Try Catch and Error Handling In SQL Server

In the SQL Server before 2005 error handling was not easy we used to work with @@Error Global Variable in order to handle error but SQL Server 2005 onward we have a easy method "Try And Catch" to handle errors.

Syntax :

Begin Try
   --Try Statements :
End Try
Begin Catch
    --Handle Error Thrown By Try Statement(s)
End Catch

Lets See With the Following Example How Try Catch Block Helps in Handling Error In your Code.

Create Following Table and Store Procedure

Create Table CheckHandling
(
Id Int Identity(1,1), 
EmpName Varchar(40),
Salary Decimal(10,2) Not null
)

Go

Create Procedure Usp_CheckHandling
As
Insert Into CheckHandling(EmpName,Salary)
Values('Sanjay Joshi',null)

Go


As You Can See In Above Table “Salary” Column Is Defined as “Not Null” Means Salary Column Doesn’t Allow Null Values. But To Produce Error I Am Trying To Insert Null Into Salary Column Inside The Procedure.

CASE – 1 : Without Using Try Catch Block

Query Window

Exec Usp_CheckHandling

On Executing above Store Procedure the SQL throws following error

Msg 515, Level 16, State 2, Procedure Usp_CheckHandling, Line 3
Cannot insert the value NULL into column 'Salary', table 'TEST80.dbo.CheckHandling'; column does not allow nulls. INSERT fails.
The statement has been terminated.

So Its Clear that if your application executes the above stored procedure then application may encounter the above error resulting in Application Crash.


CASE – 1 : Using Try Catch Block

Now let’s create another Stored Procedure which handles the Exceptions

Create Procedure Usp_HandleException
As
Begin Try

    Insert Into CheckHandling(EmpName,Salary)
    Values('Sanjay Joshi',null)

End Try
Begin Catch

    Print 'Exception Handled'

End Catch

Go

Query Window

Exec Usp_HandleException

On Executing The Procedure You Get Following Message

(0 row(s) affected)
Exception Handled

But you Stored Procedure Executes Safely Without exception.

 

So Under Catch Block You Can Capture the Error Number and Error Message using Following Function

  • ERROR_NUMBER()
  • ERROR_MESSAGE()

You Can Create A Log Table And Insert Value Of Above Functions In Catch Block Into the Table To Track Errors. See Following For Example


Create Procedure Usp_HandleException
As
Begin Try

    Insert Into CheckHandling(EmpName,Salary)
    Values('Sanjay Joshi',null)

End Try
Begin Catch

    Insert Into Error_Log(ErrorNumber,ErrorMessage,ErrorDateTime)
    Values (ERROR_NUMBER(),ERROR_MESSAGE(),GETDATE())


End Catch

Go



Tuesday 30 August 2011

How to Get Last And First Date Of Current Month And Working with Different Date Time Formats

In This Blog We’ll see the different date time formats and how to get desirable format with simple Query.

Declare @DateNow DateTime
Set @DateNow=GETDATE()

--Current DateTime
Select @DateNow As [Now]

--Last Day Of Previous Month
SElect @DateNow-DAY(@DateNow) AS [Last Day Of Previous Month]

--First Day Of Current Month
Select @DateNow-DAY(@DateNow)+1 AS [First Day Of Current Month]

--Last Day Of Current Month
Select DateAdd(mm,1,@DateNow)-DAY(@DateNow) As [Last Day Of Current Month]

--Extrating WeekDay Name, Month Name, Year
Select DATENAME(dw,@DateNow) [WeekDay Name],DATENAME(mm,@DateNow) [Name Of Month],MONTH(@DateNow) [Month],YEAR(@DateNow) [Year],DAY(@DateNow) [Day]
--Extracting Time
Select Right(Convert(Varchar,@DateNow,100),7) [Current Time]
How to Format A Date ? See Following

Lets Say You Want Get Date Format = “dd/mm/yyyy”


As You’ve already seen GetDate() Function Returns = ‘2011-08-29 20:33:07.040’ Format

Let’s See How to Convert Getdate() returned format to desirable Format with a simple query, there are many format code provided by SQL Server. We'll Look at those format as well.

So To Convert DateTime To A Desirable Format Use Following Syntax
Convert(Varchar(30),[DateTimeToConvert],[Target FormatCode])
Following is the list of formats.
To achieve above format “dd/mm/yyyy” we wil use highlighted format which is “103”

Ok, Now Use The Above Syntax To Get Desired Format which is “103”
Select Convert(Varchar(10),GetDate(),103)
So with the small query and with the help of format provided you can achieve any format

Format Code

Format

Format Code

Format

1

08/29/11

100

Aug 29 2011 8:15PM

2

11.08.29

101

08/29/2011

3

29/08/11

102

2011.08.29

4

29.08.11

103

29/08/2011

5

29-08-11

104

29.08.2011

6

29 Aug 11

105

29-08-2011

7

Aug 29, 11

106

29 Aug 2011

8

20:15:13

107

Aug 29, 2011

9

Aug 29 2011 8:15:13:180PM

108

20:15:13

10

08-29-11

109

Aug 29 2011 8:15:13:180PM

11

11/08/29

110

08-29-2011

12

110829

111

2011/08/29

13

29 Aug 2011 20:15:13:180

112

20110829

14

20:15:13:180

113

29 Aug 2011 20:15:13:180

20

2011-08-29 20:15:13

114

20:15:13:180

21

2011-08-29 20:15:13.180

   

22

08/29/11 8:15:13 PM

   

23

2011-08-29

   

24

20:15:13

   

25

2011-08-29 20:15:13.180

   

Tuesday 23 August 2011

SNAPSHOT Isolation Level

So far we have saw following Isolation Levels and read phenomenon that exists with them

1. Read Uncommitted

2. Read Committed

3. Repeatable Read

4. Serializable

With the above series of post we saw that each isolation has different read phenomenon, the above isolation levels exists in SQL Server 2000 and higher version. From SQL Server 2005 onwards we have another isolation level which is “SNAPSHOT ISOLATION LEVEL”.

Since in our last blog over Serializable we saw that, with Serializable Isolation Level we can prevent Dirty, Non-Repeatable and Phantom Reads. So what other things Snapshot can do ?

Let see the behavior of  Snapshot isolation level.

Create Database From Following Script

IF Exists(SElect * From Sys.databases where name='DBCheckIsoLevel')
Begin
Drop Database DBCheckIsoLevel
End
Go

Create Database DBCheckIsoLevel
Go

Use DBCheckIsoLevel
Go

Before using Snapshot Isolation Level we need to allow database to use Snapshot isolation level.

Check whether database is allowed to use Snapshot Isolation level or Not,

Select snapshot_isolation_state_desc from sys.databases Where name='DBCheckIsoLevel'

Go

image

IF the Value returned by Above Query  is “OFF” means Snapshot Isolation Is Not Allowed, If Returned “ON”  Means  Allowed.

Since Query Above Returned value “OFF” Then we need to allow snapshot isolation, Use following query to allow snapshot isolation on a database.

Alter Database DBCheckIsoLevel Set Allow_Snapshot_Isolation On

Go

Check The Status Now

Select snapshot_isolation_state_desc from sys.databases Where name='DBCheckIsoLevel'

Go

Should return “ON”

---Script To Create Test Table

Use DBCheckIsoLevel

Go

If (Select Object_Id('CheckIsolationLevel')) is not null
Drop Table CheckIsolationLevel

Create Table CheckIsolationLevel(id int Identity,Name Varchar(20),EncodedDate DateTime)

Insert Into CheckIsolationLevel Values('Pankaj',GETDATE()),('Santosh',GETDATE()),('Hitesh',GETDATE())

Select * From CheckIsolationLevel
Go


Id Name EncodedDate
1 Pankaj 2011-07-28 20:50:53.247
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247

Now, Open a Connection At Query Window 1

Query Window 1

Set Transaction Isolation Level Snapshot
Begin Tran
Update CheckIsolationLevel Set Name='Pankaj Arya' Where Id=1

--Note: The Transaction is Still Pending.

 

Query Window 2

Set Transaction Isolation Level Snapshot
Select * From CheckIsolationLevel Where Id=1

As You Can See Below Record Is Returned At Query Window 2

Id Name EncodedDate
1 Pankaj 2011-07-28 20:50:53.247

Surprisingly, We saw that transaction at query window 2 was not locked  by Pending transaction at query window 1 ( which is working same as Read Uncommitted Isolation Level) but here the main thing is that it did not return the updated value rather it returned old value.

Lets Go To Query Window 1, We have one pending transaction at Query Window 1

Let Fire The Following Query At Query Window 1

Query Window 1

--Continue after pending transaction

Select * From CheckIsolationLevel Where Id=1
Id Name EncodedDate
1 Pankaj Arya 2011-07-28 20:50:53.247

 

We noted that Query Window 1 result has been changed from “Pankaj” To “Pankaj Arya” But Similar Query At Query Window 2 Returned Old result. So now the question arises is where the changes of Query Window 1 are taking place ?.

So This is The Special Feature provided by snapshot isolation where modification or data inserts within snapshot isolation level creates row version in tempdb.

So It is clear in snapshot isolation level reads are not locked or blocked by writes.

But What about Write with Write ??

Now Fire The Following Query At Query Window 2 , Remember That Query Window 1 has a pending transaction.

Update CheckIsolationLevel Set Name='Pankaj Kumar' Where Id=1

--You will see that the above Query is blocked by Query at Query Window 1

So It is clear that Write with Write in Snapshot Isolation Level is Blocked.

 

 

Ok Let See Another Case Under Snapshot Isolation

Rollback Transaction At Query Window 1 and Run the Table Script to Re-Create and Fill Data.

Query Window 1

Set Transaction Isolation Level Snapshot
Begin Tran
Select * From CheckIsolationLevel Where Id=2

--Output ":

Id Name EncodedDate
2 Santosh 2011-07-28 20:50:53.247

--Note Transaction is Still Pending

Query Window 2

Update CheckIsolationLevel Set Name='Satosh Panday' Where Id=2

--Note : Above Query Updates the Data. Also note that query runs without transaction and under Read_Committed Isolation Level.

Let Go Back to Query Window 1 where You have 1 pending transaction and run the following Query.


Update CheckIsolationLevel Set Name='Satosh Kumar Panday' Where Id=2

 

image

So you can see that above query at Query Window 1 raises error. Due to Update Conflict and rollback the entire transaction. These conflict error is a problem with snapshot isolation and one must handle there error while writing queries.

Hope Above Detail Might have given you basis info about Snapshot isolation.

Monday 15 August 2011

SERIALIZABLE Isolation level

 

In my blog about Read Phenomenon against each isolation level . we saw and understood what is Dirty, non repeatable, Phantom reads.

SERIALIZABLE ISOLATION : This Isolation Level is Same as Repeatable Read Isolation Level. The Main Difference Between SERIALIZABLE And REPEATABLE READ is that SERIALIZABLE Isolation Level Applies a Range Lock so that you cannot insert new rows or records within the range locked for other transaction which prevents Phantom Reads.

We also saw that Dirty, Non Repeatable and Phantom Read phenomenon doesn’t exists with SERIALIZABLE Isolation Level.

Ok. So Lets Prove Above

---Script To Create Test Table

If (Select Object_Id('CheckIsolationLevel')) is not null
Drop Table CheckIsolationLevel

Create Table CheckIsolationLevel(id int Identity,Name Varchar(20),EncodedDate DateTime)

Insert Into CheckIsolationLevel Values('Pankaj',GETDATE()),('Santosh',GETDATE()),('Hitesh',GETDATE())

Select * From CheckIsolationLevel
Go
Id Name EncodedDate
1 Pankaj 2011-07-28 20:50:53.247
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247

 

CASE -1

CASE – 1 ( Does SERIALIZABLE ISOLATION Prevents Dirty Read ?)

Query Window 1

Begin Transaction
Update CheckIsolationLevel Set Name='MSSQLGUIDE' Where Id=1
--Note the transaction is still pending since no commit and rollback has been issued

--Also Note That We Have Not Changed Isolation Level For Above Transaction So Update Is Taking Place Under Default Isolation Level (Read Committed).


 

Lets Open Another Connection

Query Window 2

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Select * From CheckIsolationLevel Where Id=1
 

As you see Query at Query Window 2 waits till Transaction at Query Window 1 Completes. So here while reading data or records which are in-flight or between transaction wait till transaction completes (which is preventing dirty read).

The Above Query At Query Window 2 Can be Written In Following Way Also.

Syntax 1 : Using “REPEATABLEREAD” Hint

Select * From CheckIsolationLevel (REPEATABLEREAD)  Where Id=1

 

Conclusion of CASE-1 (“YES”, SERIALIZABLE Isolatiton Prevents Dirty Read)

 

CASE –2

 

CASE – 2 (Does SERIALIZABLE Isolation Prevents Non-Repeatable Read ?)

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Begin Transaction

Select * From CheckIsolationLevel Where Id = 1

--Note the transaction is still pending since no commit and rollback has been issued

Let’s  Go To Query Window 2

Query Window 2

Begin Transaction

Update CheckIsolationLevel Set Name=’MSSQLGUIDE’ Where Id=1

--And you noted that Above Query waits. The reason is that to prevent Non-Repeatable Read. Query at Query Window 1 Puts Shared Lock on the Key Records(If Table Is Cluster) and Share Lock on Table(If Table Is Heap) and shared lock prevents data from being modified by other transactions until shared lock is released at Query Window 1.

Commit Transaction

Let’s Go Back to Query Window 1

Query Window 1

--Let’s Fire The Same Query Again

Select * From CheckIsolationLevel Where Id=1

--As You see the Data Is Same As We Read Before With the Same Query, So It Clearly Shows That It Prevents No-Repeatable Read


Conclusion Of CASE –2 ( “YES” , SERIALIZABLE Isolation Prevent Non-Repeatable Read)


 

CASE – 3

CASE – 3 (Does SERIALIZABLE Isolation Prevents Phantom Read ?)

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Begin Transaction

Select * From CheckIsolationLevel Where Id Between 2 and 5

--Note the transaction is still pending since no commit and rollback has been issued and Following Is Your Result

 

Id Name EncodedDate
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247

 

Let Go To Next Query Window 2

Query Window 2

Begin Transaction

Insert Into CheckIsolationLevel Values(‘Sandeep’,GetDate()),(‘Naren’,Getdate())

--So you see above insert wait as there is range lock acquired by Transaction at Query Window 1 and Query at Query Window 2 waits till lock is released by Query at Query Window 1.

Let’s Go To Query Window 1

Query Window 1

--Run the Same Query and See the Result

Select * From CheckIsolationLevel Where Id Between 2 and 5

--Following result is Retrieved

Id Name EncodedDate
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247

 

As You Can See The Above result is same as First Result (Preventing Phantom Read)

Conclusion Of CASE – 3 (“YES”,SERIALIZABLE Isolation Level Prevents Phantom Read)

Friday 5 August 2011

REPEATABLE READ Isolation Level

 

In my blog about Read Phenomenon against each isolation level . we saw and understood what are Dirty, non repeatable, Phantom reads.

Also in my last blogs on  Read Uncommitted and Read Committed Isolation Level we saw that non repeatable read phenomenon exists with them.

with above se saw read phenomenons that exists with Repeatable Read. And we saw

With Repeatable Read : Dirty Read( Not Exists) ,  Non Repeatable Read (Not Exists), Phantom Read( Exists).

Ok. So Lets Prove Above

 

---Script To Create Test Table

If (Select Object_Id('CheckIsolationLevel')) is not null
Drop Table CheckIsolationLevel

Create Table CheckIsolationLevel(id int Identity,Name Varchar(20),EncodedDate DateTime)

Insert Into CheckIsolationLevel Values('Pankaj',GETDATE()),('Santosh',GETDATE()),('Hitesh',GETDATE())

Select * From CheckIsolationLevel
Go
Id Name EncodedDate
1 Pankaj 2011-07-28 20:50:53.247
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247

 

CASE -1

CASE – 1 ( Does REPEATABLE READ ISOLATION Prevents Dirty Read ?)

Query Window 1

Begin Transaction

Update CheckIsolationLevel Set Name='MSSQLGUIDE' Where Id=1

--Note the transaction is still pending since no commit and rollback has been issued

--Also Note That We Have Not Changed Isolation Level For Above Transaction So Update Is Taking Place Under Default Isolation Level (Read Committed).


 

Lets Open Another Connection

Query Window 2

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Select * From CheckIsolationLevel Where Id=1

 

As you see Query at Query Window 2 waits till Transaction at Query Window 1 Completes. So here while reading data or records which are in-flight or between transaction wait till transaction completes (which is preventing dirty read).


The Above Query At Query Window 2 Can be Written In Following Way Also.

Syntax 1 : Using “REPEATABLEREAD” Hint

Select * From CheckIsolationLevel (REPEATABLEREAD)  Where Id=1

 

Conclusion of CASE-1 (“YES”, Repeatable Read Isolatiton Prevents Dirty Read)

 

CASE –2

 

CASE – 2 (Does Repeatable Read Isolation Prevents Non-Repeatable Read ?)

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Begin Transaction

Select * From CheckIsolationLevel Where Id=1

--Note the transaction is still pending since no commit and rollback has been issued


-- Also note that we have only fetched record where Id=1


Let’s  Go To Query Window 2

Query Window 2

Begin Transaction

Update CheckIsolationLevel Set Name=’MSSQLGUIDE’ Where Id=2

--Note The Above “Update Query” Updates The Records

--Now Lets Fire Below Update Query

Update CheckIsolationLevel Set Name=’NEW VALUE’ Where Id=1

--And you noted that Above Query waits. The reason is that to prevent Non-Repeatable Read. Query at Query Window 1 Puts Shared Lock On the Record fetched and shared lock prevents data from being modified by other transactions until shared lock is released at Query Window 1.

--at Query Window 1 just fetched Record where ID=1 that is why Query at Query Window 2 Allowed us to Update Data Other Than ID=1

Commit Transaction

--Note The Transaction is Committed

 

Let’s Go Back to Query Window 1

Query Window 1


--Let’s Fire The Same Query Again

Select * From CheckIsolationLevel Where Id=1

--As You see the Data Is Same As We Read Before With the Same Query, So It Clearly Proves That It Prevents No-Repeatable Read


Conclusion Of CASE –2 ( “YES” , Repeatable Read Isolation Prevent Non-Repeatable Read)

 

CASE – 3

CASE – 3 (Does Repeatable Read Isolation Prevents Phantom Read ?)

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Begin Transaction

Select * From CheckIsolationLevel Where Id Between 2 and 5

--Note the transaction is still pending since no commit and rollback has been issued and Following Is Your Result

 


Id Name EncodedDate
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247

 

Let Go To Next Query Window 2

Query Window 2

Begin Transaction

Insert Into CheckIsolationLevel Values(‘Sandeep’,GetDate()),(‘Naren’,Getdate())

Commit Transaction

--Note The Transaction is Committed

 

Let’s Go To Query Window 1

Query Window 1

--Run the Same Query and See the Result

Select * From CheckIsolationLevel  Where Id Between 2 and 5

--Following result is Retrieved

Id Name EncodedDate
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247
4 Sandeep 2011-07-28 21:17:25.820
5 Naren 2011-07-28 21:17:25.820

 

As You Can See The Above result with 2 New Rows which were not there in First Result (Causing Phantom Read)

Conclusion Of CASE – 3 (“NO”,Repeatable Read Isolation Does Not Prevents Phantom Read)

Thursday 4 August 2011

Read Uncommitted Isolation Level

and in last blog we saw the behavior of  default isolations (Read Committed) for each read phenomenon.

Read Uncommitted Isolation level : This is the Lowest restrictive isolation level and as the name Suggests, The transactions running under Read Uncommitted Isolation Level does not prevent reading of data that has been modified by other transaction which are not committed.

In my blog about Read Phenomenon against each isolation level .

We saw Dirty, Repeatable & Phantom Reads occurrence. And these phenomenon exists in Read Uncommitted Isolation.

---Script To Create Test Table

If (Select Object_Id('CheckIsolationLevel')) is not null
Drop Table CheckIsolationLevel

Create Table CheckIsolationLevel(id int Identity,Name Varchar(20),EncodedDate DateTime)

Insert Into CheckIsolationLevel 
Values('Pankaj',GETDATE()),('Santosh',GETDATE()),('Hitesh',GETDATE())

Select * From CheckIsolationLevel
Go

Id Name EncodedDate
1 Pankaj 2011-07-28 20:50:53.247
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247

 

CASE -1

CASE – 1 ( Does READ UNCOMMITTED ISOLATION Prevents Dirty Read ?)

Query Window 1


Begin Transaction
    Update CheckIsolationLevel Set Name='MSSQLGUIDE' Where Id=1
--Note the transaction is still pending since no commit and rollback has been issued
--Also Note That We Have Not Changed Isolation Level For Above Transaction So Update Is Taking Place Under Default Isolation Level (Read Committed).

Lets Open Another Connection

Query Window 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Select * From CheckIsolationLevel Where Id=1

Output :

Id Name EncodedDate
1 MSSQLGUIDE 2011-07-28 20:50:53.247

 

As you see above the pending update transaction at Query Window 1 doesn’t lock records since user at Query Window 2 can easily read those records (Which is Dirty Read).

Here Data can be inconsistent but certainly give performance over read since there is no locking and wait.

The Above Query At Query Window 2 Can be Written In Following Ways Also.

Syntax 1 : Using “NOLOCK” Hint


Select * From CheckIsolationLevel (NOLOCK)  Where Id=1

 

Syntax 2 : Using READUNCOMMITTED HINT



Select * From CheckIsolationLevel (READUNCOMMITTED)  Where Id=1

 


Conclusion of CASE-1 (“NO”, Read UnCommitted Isolatiton Does Not Prevent Dirty Reads.)


 

CASE –2

 

CASE – 2 (Does Read UnCommitted Isolation Prevents Non-Repeatable Read ?)

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Begin Transaction

Select * From CheckIsolationLevel Where Id=1

--Note the transaction is still pending since no commit and rollback has been issued

 

Let’s  Go To Query Window 2

Query Window 2


Begin Transaction

Update CheckIsolationLevel Set Name=’MSSQLGUIDE’ Where Id=1

Commit Transaction

--Note The Transaction is Committed

 

Let’s Go Back to Query Window 1

Query Window 1

--Let’s Fire The Same Query Again

Select * From CheckIsolationLevel Where Id=1

--As Expected Data is Changed even executing identical query within a transaction ( This is forming a Non-Repeatable Read).

Conclusion Of CASE –2 ( “NO” , Read UnCommitted Isolation Doesn’t Prevent Non-Repeatable Read)

 

CASE – 3

CASE – 3 (Does Read UnCommitted Isolation Prevents Phantom Read ?)

Drop And Recreate Table and Data

Query Window 1


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Begin Transaction

Select * From CheckIsolationLevel Where Id Between 2 and 5

--Note the transaction is still pending since no commit and rollback has been issued and Following Is Your Result

 


Id Name EncodedDate
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247

 

Let Go To Next Query Window 2

Query Window 2

Begin Transaction

Insert Into CheckIsolationLevel 
Values(‘Sandeep’,GetDate()),(‘Naren’,Getdate())

Commit Transaction

--Note The Transaction is Committed

 

Let’s Go To Query Window 1

Query Window 1

--Run the Same Query and See the Result

Select * From CheckIsolationLevel Where Id Between 2 and 5

--Following result is Retrieved

Id Name EncodedDate
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247
4 Sandeep 2011-07-28 21:17:25.820
5 Naren 2011-07-28 21:17:25.820

 

As You Can See The Above result with 2 New Rows which were not there in First Result (Causing Phantom Read)

Conclusion Of CASE – 3 (“NO”,Read UnCommitted Isolation Does Not Prevents Phantom Read)