aiDeeva.com

Author: Jugal Shah

  • Row Versioning in SQL Server 2005

    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

    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()

    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()

    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()

    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