ISOLATIONS

1. Isolation Levels?

This article provides an easy-to-understand view of what Isolation Levels really mean and when to use which level. Isolation Level is a setting that decides how data which is a part of an ongoing transaction is made visible to other transactions.

Before going into know the isolation type let us first have a look of what are the problems that might occur if isolation levels are not set properly.

1. Dirty Read

This occurs when a second transaction selects a row which is being modified by another transaction which is not committed. Since the data is not committed there is a possibility that we might end up reading data which is not the actual one that should reflect.

2. Non-repeatable read

This occurs when a second transaction reads a particular row many times within a single transaction and each time end up with a different value. Take a case for a scenario you read a data and do some manipulation by that time there is another transaction that has modified the same row and after your manipulations you read the row again but find to be changed. This case is called as non-repeatable read.

3. Phantom rows

This occurs when you have already read a range of values during which time they have again changed. The rows that have changed are called as Phantom rows.

Now having know what would each problem cause. Let us look at ways of avoiding or minimizing them with the isolation level settings.

Isolation levels

* Read Uncommitted

This is as good (or bad) as not having any isolation. All data which is uncommitted is readable from any connection. This should not be used unless you have a very good reason to do so.

* Read Committed

This prevents dirty reads. This does not prevent phantoms or non-repeatable reads. This is the default. Although it is the default, it does not mean that this isolation level is ideal for all transactions. One of the regular tendencies amongst techies is to simply use default values without giving it a second thought! I cannot imagine the number of phantom and non-repeatable reads that must be occurring in the world because someone simply used the default value. It is a scary thought to say the least.

This level is obviously more restrictive than the Read Uncommitted level.

* Repeatable read

This prevents dirty reads as well as non-repeatable reads. It does not prevent phantom rows. This is more restrictive than Read Committed level. When I say restrictive what does it mean? It means that the chances of other transactions having to wait for this one to finish are INCREASED. Another way of saying this is Repeatable Read reduces concurrency compared to Read Committed level.

* Serializable

This is the most restrictive of the options. This should never be used as the default level. If you use this one as the default, it will most probably create a single user system!

So now lets look at what isolation level can be used for what problem as given in the table below.

Dirty Read	Read Committed (Default of SQL Server)
Dirty Read and Repeatable Read	Non-Repeatable Read
Dirty Read and Non-Repeatable Read and Phantom Rows	Serializable
To retain all three problems	Read Uncommitted

How to set isolation levels:

The syntax to set isolation levels is as given below

SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE }

Hope this article gave a understanding of the isolation levels, different problems due to incorrect isolation levels and how to solve them.

2 explain Isolation in a simple way?

Below are the list of 5 available Isolation levels in SQL Server.

Read Uncommitted – This level allows any transaction to fetch records even though there is lock on those particular records.

Read Committed – This level does not allow any transaction to fetch records if there is lock on those particular records. This is default Isolation level in SQL Server.

Serializable – This level does not allow any transaction to fetch the data only when the other transactions are done with their data modification.

Repeatable Read – This level does not allow any transaction to fetch the data which is being changed by another transaction also which is not committed data.

Snapshot – This level cannot fetch the data which is modified after the transaction begins. It can fetch the data which is already committed before it starts. 

3 What are isolation levels and use of isolation levels?

A While executing queries, SQL Server often required to acquire locks on resources to prevent dirty reads,phantom reads etc. These locking behavior will be depends on isolation levels. Based on isolation level of transaction, SQL Server will acquire locks on resources.

There are different isolation levels

1. Read Committed
2. Read Uncommitted
3. Snap-shot
4. Serializable
5. Repeatable Read

4. What is difference between read committed and snapshot isolation level?

A:

5 What are Sparse columns?

• Sparse column is a type of column which is used to optimize storage for null values.
• When a column there is big number of null then by defining that column as spars column we can save a large amount of disk space.
• The drawback of sparse column is that it requires more space for the non null values. When we define a column as sparse it requires additional 4 Byte for not null values.
• For example, a DATETIME column in a non-sparse column required 8 bytes of storage whereas if we define that column as a sparse column then it will require 12 bytes.
• It is not possible to set ROWGUIDCOL and IDENTITY properties in sparse column.

6.  How to find Isolation Level?

DBCC useroptions