ISOLATION LEVEL READ COMMITTED

It is the default isolation level for SQL Server. Read committed isolation reads only committed data in a database. It gives a guarantee of sensible committed data exists in Table. It is not a part of Dirty read. When read committed isolation level in on then, shared locks are held while the data is being read to avoid dirty reads.

Shared locks held for the duration of statement to prevent a concurrent transaction from concurrently modifying the data, because modification would require an exclusive lock, which is not compatible with the shared lock.

Now will go for an practical example.

I have created a database (Test_db) and inside it created a table named “Pritam_table”.

Now, we have to open 2 instance by opening 2 new window in sql server and get the instance number.

Then, write following codes in instance number 79 window

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

SELECT * FROM PRITAM_TABLE

UPDATE PRITAM_TABLE SET LEAVE_TYPE_DESCR='ASHGFSGDFGHJSD' WHERE DIM_LEAVE_TYPE_ID=1

ROLLBACK

And, write following codes in instance number 81 window

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM PRITAM_TABLE

Now we have to open one more new window and write the codes given below.

select request_session_id,

case resource_type

when 'DATABASE' then DB_NAME(resource_database_id)

when 'PAGE' then OBJECT_NAME(OBJECT_ID)

when 'RID' then OBJECT_NAME(OBJECT_ID)

when 'OBJECT' then OBJECT_NAME(resource_associated_entity_id)

ELSE 'UNKNOWN'end AS name

,resource_type,request_mode,resource_description,request_status from sys.dm_tran_locks

LEFT JOIN SYS.partitions ON sys.dm_tran_locks.resource_associated_entity_id=SYS.partitions.hobt_id

where request_session_id in (79,81)

If we look on this code the last line we can see the request_session_id in (79,81)

This is nothing but a new window number.

Now will go for 79 window and execute the yellow shared code only

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

Will see the table now

SELECT * FROM PRITAM_TABLE

Next, will run update command to this table. Remember in first step we started BEGIN TRAN.

UPDATE PRITAM_TABLE SET LEAVE_TYPE_DESCR='Lisa Das' WHERE DIM_LEAVE_TYPE_ID=1

Next will see the locking type exists in for above update query.

Now will go to 81 window and execute the yellow shared code only

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

If we run SELECT * FROM PRITAM_TABLE we then it will wait for the updating of the row in 81 window. This situation is called Deadlock. 

Will look on which locking type exists now.

Now will go for 79 window and execute the yellow shared code only

ROLLBACK

If we run SELECT * FROM PRITAM_TABLE. Will see the result come immediately.

Lock Compatibility Chart in SSMS

Before going to isolation level, we have to know about various types of lock in SSMS because Locking facilities that preserve transaction isolation.

If a resource (data row/rows of table/tables) is already locked by another transaction, a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock. If the mode of the requested lock is not compatible with the existing lock, the transaction requesting the new lock waits for the existing lock to be released or for the lock timeout interval to expire.

Microsoft provide Lock Compatibility chart given below.

Now will know that how to read the chart from below picture.

so, for example

1. If we run Intent shared lock on a data row of a table and anther person want to shared lock of the same data row, it is possible no need to wait for Intent shared lock.

2. If we run Intent shared lock on a data row of a table and anther person want to exclusive lock of the same data row, then he/she has to wait till Intent shared lock job is over.

3. If we run shared lock on a data row of a table and anther person want to exclusive lock of the same data row, it is not possible at same time. then it has to wait till shared lock's job is over.

4. If we run exclusive lock on a data row of a table then none of the lock can be applied until exclusive lock's job is over.

For definition of this locks please visit

https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx

Recursive CTE in SSMS

We know that CTE is famous for one of it's best features called recursive. Means, at the time of declaring CTE we can call the same inside it.

Will see this features by an example given below.

I am having a table named EMPLOYEE.

Now we have to arrange the employee in to employee hierarchy or level wise from to to bottom.

To solve this we have to use recursive CTE.

;WITH CTE_MANAGER_H AS(

      SELECT EMPLOYEE_ID,NAME,MANAGER_ID, 1 AS E_LEVEL

      FROM DBO.EMPLOYEE

      WHERE EMPLOYEE_ID IS NULL

     

      UNION ALL

     

      SELECT E.EMPLOYEE_ID,E.NAME,E.MANAGER_ID,(E_LEVEL + 1) AS E_LEVEL

      FROM DBO.EMPLOYEE E

      INNER JOIN CTE_MANAGER_H CMH

            ON E.MANAGER_ID = CMH.EMPLOYEE_ID

      WHERE E.EMPLOYEE_ID IS NOT NULL

)

SELECT EMPLOYEE_ID,NAME,E_LEVEL FROM CTE_MANAGER_H ORDER BY E_LEVEL

Output:

Remember maximum recursion level possible for CTE is 100 in default.

We can change it on per-query basis by appending a hint :

OPTION (MAXRECURSION 0)

Recursive function in SSMS

Sometimes for a long calculation, If the problem can be broken down into smaller versions of itself. We can be able to find a way to solve one of these smaller versions and then be able to build up to a solution to the entire problem. This is the idea behind recursion; recursive algorithms break down a problem into smaller pieces of itself which you either already know the answer to, or can solve by applying the same algorithm to each piece, and then combining the results in to a result set.

For example we can go for Factorial calculation.

We know that, fact(5)= 5*4*3*2*1= 120

Step 1: Will take the given value 5,

Step 2: we have to calculate (given value(5) – 1) and multiply with first given value (5) = 5 x4=20

Step 3: After that we have to calculate (previous calculated value (4) – 1) and multiply with older    multiplication (20) = 3 x 20 = 60

Step 4: Next we have to calculate (previous calculated value (3) – 1) and multiply with older multiplication (60) = 2 x 60 = 120

Step 5: Finally we have to calculate (previous calculated value (2) – 1) and multiply with older multiplication (60) = 1 x 120 = 120. Once the value is reached 1 then we have to stop the calculation and display the total value.

So, it is a long calculation and it call the same operation repeatedly. So, when a function call itself for execution something, then we can call the function as recursive function.

Now will go for practical experience.

CREATE FUNCTION dbo.UDF_Factorial_Calculation ( @InputNumber INT )

RETURNS INT

AS

BEGIN

DECLARE @Result_set  INT

    IF @InputNumber <= 1

        SET @Result_set  = 1

    ELSE

        SET @Result_set  = @InputNumber * dbo.UDF_Factorial_Calculation( @InputNumber - 1 )

RETURN (@Result_set)

END

Now will see the Out put:

SELECT dbo.UDF_Factorial_Calculation ( 5 ) as Factorial

Output:    Factorial

                 120

RANKING FUNCTIONS in SSMS (ROW_NUMBER, RANK, DENSE_RANK, NTILE)

There are various ranking function exists in SSMS. We will to through one by one.

ROW_NUMBER: This is a kind of unique number generation function in SSMS. It will count the row of table and set the row count by incremental without bothering data value.

For example, suppose there are 4 rows exists in a table, if we use row_number over a column, then we will get 1,2,3,4.

RANK: It returns ranking of data value of mentioned column under the function. Here we cannot get the proper ranking deviation. It detect the highest value of mentioned column and check for same value exists in other row, and based on the row count of same valued row, the second highest valued row will be ranked.

For example, as previous there are 4 rows exists in a table, if the highest value is exist in  3 rows, then this 3 rows rank will be 1,1,1 and second highest row rank will be 4.

DENSE_RANK: It returns ranking of data value of mentioned column under the function. Here we get the original rank over the data value of the column.

For example, there are 4 rows exists in a table, if the highest value is exist in  3 rows, then this 3 rows rank will be 1,1,1 and second highest row rank will be 2.

NTILE: It is same as portion concepts in SQL. It display the rows in mentioned number of group.

For example, there are 4 rows exists in a table, if we perform Ntile(2) operation then it will group the 4 rows into 2 set. The 1st and 2^nd row will be 1 and 3^rd and 4^th will be 2.

Like this if there are 9 rows exists in a table, if we perform Ntile(2) operation then it will group the 9 rows into 2 set. The (1st, 2nd, 3rd, 4th)row will be 1 and (5^th,6^th,7^th,8^th,9^th )will be 2. This row grouping is based on rows exists in table.

Now will check this function practically.

select Emp_Id,Name, Salary from Table_a

Now will write all the rank function together and test.

select Name, Salary,

    ROW_NUMBER() over(order by Salary) as [RowNum],

    RANK() over(order by Salary) as [Rank],

    DENSE_RANK() over(order by Salary) as [DenseRank],

    NTILE(3) over(order by Salary) as [nTile]

from Table_a

Output:

Now we understand these function individually from below picture.

ROLLUP Grouping set in SSMS

Roll up aggregates selected column value for a hierarchy of values along with subtotal and grand total.

For better understanding will go for a example.

Suppose we are having a student marks record of a school in a table named Marks_Record .

select Class,Roll_No,Name,Section,Scored_marks from Marks_Record

Now ,if we do Roll up operation based on 'Section' column of this table then will form the column like below result set Model.

Hope now we get a clear picture of this concept, will see the SQL query for this. 

SELECT CLASS,SECTION,SUM(SCORED_MARKS) AS [TOTAL]

FROM Marks_Record

GROUP BY CLASS, SECTION WITH ROLLUP

Output:

Instead of NULL value we can do some decoration to this result.

SELECT ISNULL(CLASS,'ALL') AS CLASS ,

CASE WHEN CLASS IS NULL THEN ISNULL(SECTION,'GRAND TOTAL')ELSE ISNULL(SECTION,'SUB TOTAL') END AS SECTION ,

SUM(SCORED_MARKS) AS [TOTAL]

FROM Marks_Record

GROUP BY CLASS, SECTION WITH ROLLUP

Output: