Row Versioning in SQL Server 2005

2 Replies

SQL Server 2005 has introduced two new Isolation Levels. We can use these Isolation Levels for row versioning.

1. READ_COMMITTED_SNAPSHOT (statement level)
2. ALLOW_SNAPSHOT_ISOLATION (transaction level)

These Isolation level turned on database level. You can turn on the Isolation Level using below command.

ALTER DATABASE sqldbpool
SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE sqldbpool
SET ALLOW_SNAPSHOT_ISOLATION ON

When above Isolation level is turned on it will enable the row versioning at database level. Transaction or statement views the data as it existed at the start of the statement or transaction, instead of protecting all reads with locks. Row versioning will reduce the blocking/deadlock issues and boost the database performance. Row versioning also prevents users from reading uncommitted data and prevents multiple users from attempting to change the same data at the same time.

You can query sys.databases to check the above isolation level status.

SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases

SQL Server 2005 Output Clause

Leave a reply

The OUTPUT clause helps in returning the actual data from a table soon after an insert, update or delete. Output clause uses two virtual tables inserted and deleted. Output clause can be useful to detect actual rows affected by DML statements.


CREATE TABLE Emp(
EmpName VARCHAR(9),Age INT,MaritalStatus char(1))

INSERT INTO Emp VALUES ('Dhvani',20,'M')
INSERT INTO Emp VALUES ('Nehal',20,'M')
INSERT INTO Emp VALUES ('Sunil',95,'M')
INSERT INTO Emp VALUES ('Suvrendu',40,'M')
INSERT INTO Emp VALUES ('Bill',11,'S')
INSERT INTO Emp VALUES ('Ram',100,'S')
INSERT INTO Emp VALUES ('Nirmal',50,'S')
INSERT INTO Emp VALUES ('R',30,'S')

Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))

INSERT INTO Emp (EmpName,Age,MaritalStatus)
OUTPUT inserted.EmpName,inserted.Age,inserted.MaritalStatus INTO @dummyEMP
VALUES ('Dhvani',20,'M')

select * from @dummyEMp

Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
INSERT INTO Emp (EmpName,Age,MaritalStatus)
OUTPUT inserted.* INTO @dummyEMP
VALUES ('Deepali',27,'M')
select * from @dummyEMp

Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
update Emp
set MaritalStatus = 'M'
output deleted.* into @dummyEMP
where EmpName = 'Dhvani'
select * from @dummyEMp

Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
delete from Emp
output deleted.* into @dummyEMP
where Age > 50
select * from @dummyEMp

Ranking Function – NTITLE()

Leave a reply

NTITLE() function is used to break up a record set into a specific number of groups.

SELECT NTILE(3) OVER (ORDER BY Age) AS [Group by Age],
EmpName,
Age
FROM Emp

Group by Age EmpName Age
-------------------- --------- -----------
1 Bill 11
1 Dhvani 20
1 Nehal 20
2 R 30
2 Abhinav 40
2 Suvrendu 40
3 Nirmal 50
3 Sunil 95
3 Ram 100

(9 row(s) affected)

Ranking Function – Dense_Rank()

Leave a reply

Dense_Rank() :- The DENSE_RANK function is similar to the RANK function, although this function doesn’t produce gaps in the ranking numbers. Instead this function sequentially ranks each unique ORDER BY value. With the DENSE_RANK function each row either has the same ranking as the preceeding row, or has a ranking 1 greater then the prior row.

CREATE TABLE Emp(
EmpName VARCHAR(9),
Age INT,
MaritalStatus char(1))

INSERT INTO Emp VALUES ('Abhinav',40,'S')
INSERT INTO Emp VALUES ('Dhvani',20,'M')
INSERT INTO Emp VALUES ('Nehal',20,'M')
INSERT INTO Emp VALUES ('Sunil',95,'M')
INSERT INTO Emp VALUES ('Suvrendu',40,'M')
INSERT INTO Emp VALUES ('Bill',11,'S')
INSERT INTO Emp VALUES ('Ram',100,'S')
INSERT INTO Emp VALUES ('Nirmal',50,'S')
INSERT INTO Emp VALUES ('R',30,'S')

SELECT Dense_RANK() OVER (ORDER BY Age) AS [Rank by Age],
EmpName,
Age
FROM Emp

Rank by Age EmpName Age
-------------------- --------- -----------
1 Bill 11
2 Dhvani 20
2 Nehal 20
3 R 30
4 Abhinav 40
4 Suvrendu 40
5 Nirmal 50
6 Sunil 95
7 Ram 100

(9 row(s) affected)

SELECT Dense_RANK() OVER (PARTITION BY MaritalStatus ORDER BY Age) AS [Partition by MaritalStatus],
EmpName,
Age,
MaritalStatus
FROM emp

Partition by MaritalStatus EmpName Age MaritalStatus
-------------------------- --------- ----------- -------------
1 Dhvani 20 M
1 Nehal 20 M
2 Suvrendu 40 M
3 Sunil 95 M
1 Bill 11 S
2 R 30 S
3 Abhinav 40 S
4 Nirmal 50 S
5 Ram 100 S

(9 row(s) affected)

Ranking Function – Rank()

Leave a reply

Rank()
The RANK function sequentially numbers a record set, but when two rows have the same order by value then they get the same ranking. Ranking value will be incremented by 1 for next un-matched row.

Syntax
RANK ( ) OVER ( [ ] )

Examples
CREATE TABLE Emp(
EmpName VARCHAR(9),
Age INT,
MaritalStatus char(1))

INSERT INTO Emp VALUES (‘Abhinav’,40,’S’)
INSERT INTO Emp VALUES (‘Dhvani’,20,’M’)
INSERT INTO Emp VALUES (‘Nehal’,20,’M’)
INSERT INTO Emp VALUES (‘Sunil’,95,’M’)
INSERT INTO Emp VALUES (‘Suvrendu’,40,’M’)
INSERT INTO Emp VALUES (‘Bill’,11,’S’)
INSERT INTO Emp VALUES (‘Ram’,100,’S’)
INSERT INTO Emp VALUES (‘Nirmal’,50,’S’)
INSERT INTO Emp VALUES (‘R’,30,’S’)

Query – I

SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],
EmpName,
Age
FROM Emp

Rank by Age EmpName Age
-------------------- --------- -----------
1 Bill 11
2 Dhvani 20
2 Nehal 20
4 R 30
5 Abhinav 40
5 Suvrendu 40
7 Nirmal 50
8 Sunil 95
9 Ram 100

(9 row(s) affected)


SELECT RANK() OVER (PARTITION BY MaritalStatus ORDER BY Age) AS [Partition by MaritalStatus],
EmpName,
Age,
MaritalStatus
FROM emp

Partition by MaritalStatus EmpName Age MaritalStatus
-------------------------- --------- ----------- -------------
1 Dhvani 20 M
1 Nehal 20 M
3 Suvrendu 40 M
4 Sunil 95 M
1 Bill 11 S
2 R 30 S
3 Abhinav 40 S
4 Nirmal 50 S
5 Ram 100 S