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

How to create data base diagram in ms sql

Creating data base diagram is a few step process which are given as below.

Step First :

Open object explorer , click on Databases and  select on one database for which you want to create data base diagram. Now click on database and then right click on Database Diagrams. Now select New Database Diagram.


 Step Second

After selecting New Database Diagram a Add Table window will be appeared. You have to select either existing table or select new table after right clicking on the diagram window.

Step Three

Now I have create 4 new tables like Office , Department, Employee , Employee_Details

Now we will establish the relationship between tables. First we set primary key using right click on cloumn Office Id of the table Office ,

Now we establish the relationship between tables. This is very simple drag and drop process for table relationship. We will click on a column in a table and drag on another column of the second table.Now a window will be appered on the screen as below

 After Click on ok button you have to set Delete and Update rule in Foreign Key Relationship Window

We will establih relationship among all tables, we can remove realtionship between tabels using right click on connection bar and select Delete Relationship from Database. It will remove relationship from the database.

Now finally all table structure will look like this as below...