understanding on Surrogate key

To understand Surrogate key concept we need to have clear picture on OLTP database and OLAP data Database.

In Data Warehouse (OLAP), there are 2 types of table (Dimension and Fact). By default we set a primary key on each dimension Table. This primary key column is the same primary key column (Business Key) form OLTP database table.

This is not recommended to be used as primary key in dimension table because of following reason mentioned below.

1. In OLTP database the the datatype of primary key column will be Unique-identifier or Alphanumeric character.
which consumes lot of indexes space when used as primary key. Since index size big, it makes data pooling slower.

2. In MNC business (Multiple Data source system) when we pool the data from various different source, there will be chance to lose control over record identifiers.(Same Primary value cumming from 2 different sources for 2 different objects)

3. SCD Implementation is not possible.(Historical data)


A surrogate key is a primary key, also having name meaningless key. We are using this key because, it is small and so efficient to store. To join dimension table and fact table using only surrogate key is recomanded, not business key. 

Why surrogate key required ?

1. Surrogate keys are generally small integer numbers, which makes index size smaller      when used as index column. This gives better performance due small index size.

2. We can tell something about the record just by looking at this key.

3. Historical versions of same data can be evaluate.

Implementation :

Semi-join in SQL Server

This is very interesting and good to know that Correlated sub queries is called as Semi-join in SQL Server.

Semi-Join returns rows from a table that would join with another table without performing a complete join.

Ex:

SELECT a.FirstName, a.LastName

FROM Person.Person AS a

WHERE EXISTS

(SELECT *

    FROM HumanResources.Employee AS b

    WHERE a.BusinessEntityID = b.BusinessEntityID

    AND a.LastName = 'Pritam');

GO

Stuff number inside square brackets (Ex: [123]) from a string in SQL Server

When you Search square brackets from a string... you can't get it if you write query
like '%[ ]%'

Here my goal is to remove the number with square brackets but not the character  inside square brackets.

To achieve this goal you have to follow the code given below.

DECLARE @TEXT VARCHAR(MAX),@INITIAL INT,@FINAL INT,@PATSTART 

INT,@PATEND INT;

SET @TEXT='ASFASFA [123] ASF [ABC] AFAFS [567] 123 AFASFA [123] AFASF PRITAM[12]DAS'

SET @INITIAL=1;

SET @INITIAL=PATINDEX('%[[][0-9]%',@TEXT)

WHILE (@INITIAL>0)

BEGIN

       SET @PATSTART=@INITIAL-1;

       SET @PATEND=CHARINDEX(']',@TEXT,@INITIAL);

       SET @TEXT=STUFF(@TEXT,@PATSTART,@PATEND+1-@PATSTART,'');

       SET @INITIAL=PATINDEX('%[[][0-9]%',@TEXT);

END

SELECT @TEXT;

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