Isolation level in Sql Server

There are 5 types of isolation level in Sql Server 2008. These isolation level are as given below

1. Read Uncommited
2. Read Commited
3.Repetable Read
4.Serializable
5.Snapshot

1.Read Uncommited

Specify that statement can read row that have been modified by other transactions

 but not yet commited. For example i have two session of the Sql Server

SET ISOLATION TRANSACTION LEVEL

READ UNCOMMITED

Session I                                                                        
BEGIN TRAN                                                                   
UPDATE TABLE SET COL=1 WHERE                              
COL =2
WAIT FOR DELAY '00:00:10'
ROLL BACK TRAN

Session II
Select * from TABLE

When i execute the query for second session after the session I  then select query will given us uncommited record. In this case dirty read will be occurred.This is same as NOLOCK table for retrieving the record.

READ COMMITED :

Specify that statement cannot read data that has been modified but not commited by the transaction.It prevents dirty read data.


 

Partitioning Data

Table Partitioning is a new features define in Sql Server 2005. When database size increases too more , then it is very difficult to handle the data for maintenance like update or retrieve the record from the table.Data partition increase performance , reduce contention and increase availability of the data.

Creating a partitioned table or index typically happens in four parts :

1. Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme.
2. Create a partition functions that maps the rows of a table or index into partitions based on the values of a specified column.
3. Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.
4. Create or modify a table or index and specify the partition scheme as the storage locations.

Step 1 . Now first of all we will create file groups of the database.

Lets assume that we have 3 different file groups in the database DB_DATA, we will create 3 file groups as below.



Step 2
A partition function defines the boundary points that will used to split data across a partition scheme.

An example of partition function is

CREATE PARTITION FUNCTION
TablePartition(int)
AS RANGE LEFT
FOR VALUES (10,20,30,40,50,60)

Each partition function requires a name and a data type. The data type for a partition function can be any native SQL Server Data type , except text , ntext , image , varbinary(max) , timestamp , xml and varchar(max).

A partition function always maps  the entire range of data ;therefore ,no gaps are present.You cannot specify duplicate boundary points. This ensures that any value stored in a column always evaluates to a single partition. Null values are always stored in the leftmost partition until you explicitly specify null as a boundary and use the RANGE RIGHT syntax, in which case nulls are stored in the rightmost partition.

You can have a maximum of 1000 partitions for an object ; therefore you are allowed to specify a maximum of 999 boundary points.

Step 3 : (Creating a partition schema)

A partition schema defined the storage structures and collection of file groups that you want to use with a given partition function.

You can define partition schema as below

CREATE PARTITION SCHEME tablePartScheme AS PARTITION tablePartFunction TO (Filegroup 1,Filegroup2,Filegroup3, Filegroup4,Filegroup5)

or

CREATE PARTITION SCHEME tablePartSchem AS PARTITION tablePartFunction TO (Filegroup1, Filegroup1,Filegroup2,Filegroup2,Filegroup3)

or

CREATE PARTITION SCHEME tablePartScheme AS PARTITION tablePartFunction ALL TO (Filegroup 1)

Here ALL keyword which allows you to create all partition defined by the partition function within the