Efficient Way of Handling SCD Type 2 - Using Merge (Upsert) Statement

I feel happy that people around the globe reach my blog when they google for " SQL Server Upsert ". You can read the Basic Merge Post here. It was for beginners who want to use Merge for the first time and also for referral purpose. In that post, I only discussed about changed overwrites (Slowly Changing Dimension (SCD) Type 1), and now in this post, we shall see how to use Merge for SCD Type 2 (Dates and Flag) implementation.

Source Table: DEPT

CREATE TABLE [dbo].[DEPT]

(

            [DEPTNO] [numeric](2, 0) NULL,

            [DNAME] [varchar](14) NULL,

            [LOC] [varchar](13) NULL

)

Destination Table: DEPT_SCD2DATE

CREATE TABLE [dbo].[DEPT_SCD2DATE]

(

            [ID] [int] IDENTITY(1,1) NOT NULL,

            [DEPTNO] [numeric](2, 0) NULL,

            [DNAME] [varchar](14) NULL,

            [LOC] [varchar](13) NULL,

            [STARTDATE] [datetime] NULL,

            [ENDDATE] [datetime] NULL,

            [FLAG] [bit] NULL

)

Merge Code:

INSERT INTO DEPT_SCD2DATE (DEPTNO, DNAME, LOC, STARTDATE, ENDDATE, FLAG)

SELECT DEPTNO, DNAME, LOC, STARTDATE, ENDDATE, FLAG FROM

(MERGE DEPT_SCD2DATE D

            USING DEPT S

                        ON (D.DEPTNO = S.DEPTNO)

            WHEN NOT MATCHED

            THEN

                        INSERT VALUES (S.DEPTNO, S.DNAME, S.LOC, GETDATE(), '2999-12-31', 1)

            WHEN MATCHED AND D.FLAG = 1 AND (D.DNAME <> S.DNAME OR D.LOC <> S.LOC)

            THEN

                        UPDATE SET D.FLAG = 0, D.ENDDATE = GETDATE()

                        OUTPUT $ACTION ACTIONOUT, S.DEPTNO, S.DNAME, S.LOC,

                        GETDATE() AS STARTDATE, '2999-12-31' AS ENDDATE, 1 AS FLAG

) AS MERGE_OUT

WHERE MERGE_OUT.ACTIONOUT = 'UPDATE';

Observation - Before Run 1:

Observation - After Run 1:

Observation - Before Run 2:

Observation - After Run 2:

Conclusion: Slowly Changing Dimension Type 2 is a method to maintain complete historical Data and Merge (Upsert) handles it easily. Hope this helps! Happy TSQL'ing :)

Nth Highest Salary in Employee Table

-- Query to find the Nth Highest Salary in Employee Table

SELECT EMPNO, ENAME, SAL

FROM

(

SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS N,

EMPNO, ENAME, SAL FROM EMP

) DT

WHERE N = 3

I used N=3, as I need the third highest salary. We can check the data for various ranks.

Also check how the below window functions return data differently.

SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP

SELECT RANK() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP

SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS RN, ENAME, SAL FROM EMP

Update Statement with Custom Requirement

Hi Guys! hope you all doing great! Today I came up with a post on an interview question based on EMPLOYEE Table.

Task: Update Salaries of the Employees as per below requirement.

DEPTNO	INCREMENT
10	100
20	200
30	300
40	400
X0	X00

We can notice there is a correlation between DEPTNO and INCREMENT. For DEPTNO 10, its 100, DEPTNO 20, its 200, and so on.......for DEPTNO X0 its X00.

The structure of EMPLOYEE table is as below:

Input: SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMPLOYEE;

Solution: Using WHILE Loop

DECLARE @INCREMENT INT = 100, @DEPTNO INT

DECLARE @TV TABLE (DEPTNO INT, FLAG BIT)

INSERT INTO @TV

SELECT DISTINCT DEPTNO, FLAG = 0 FROM EMPLOYEE

SET @DEPTNO = (SELECT TOP 1 DEPTNO FROM @TV WHERE FLAG = 0)

WHILE (@DEPTNO IS NOT NULL)

BEGIN

            UPDATE EMPLOYEE

            SET SAL = SAL + @INCREMENT

            WHERE DEPTNO = @DEPTNO

            UPDATE @TV

            SET FLAG = 1

            WHERE DEPTNO = @DEPTNO AND FLAG = 0

            SET @INCREMENT = @INCREMENT + 100

            SET @DEPTNO = (SELECT TOP 1 DEPTNO FROM @TV WHERE FLAG = 0)

END

Output: SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMPLOYEE;

Conclusion: Compare both the screen shots and note that, the salaries are updated as per the requirement table mentioned on the top of this post. Hope this helps! Happy TSQL'ing ;)

Advanced T-SQL Coding Example

Here is a T-SQL task that needs a non-traditional (I repeat, Non Traditional.) way of coding. It should reach the expectations of Senior T-SQL Coding Standards and obviously highly tuned for performance.

Input Data: Here is the sample input data of Employees.

Output Needed: The expected output is as shown in figure below.

You guessed it right! Both detail and aggregate level data in a single output. 

Let me add, "the objective of this post here is about discussing the standard of T-SQL coding we use to achieve the task".

Traditional Way:

The below code is what comes to ones mind to achieve the task in a traditional way.

SELECT A.EMPNO, A.ENAME, A.SAL, B.SumSalDeptWise, C.SumSalWholeOrg

FROM EMP A

INNER JOIN

            (SELECT DEPTNO, SUM(SAL) AS SumSalDeptWise

                        FROM EMP

                        GROUP BY DEPTNO) B

            ON A.DEPTNO = B.DEPTNO

CROSS JOIN

            (SELECT SUM(SAL) AS SumSalWholeOrg FROM EMP) C

Now let's see the Non-Traditional and Highly Tuned Version of Code.

Non Traditional Way: (The future T-SQL Code)

SELECT

            EMPNO, ENAME, SAL,

            SUM(SAL) OVER (PARTITION BY DEPTNO) AS SumSalDeptWise,

            SUM(SAL) OVER () AS SumSalWholeOrg

FROM EMP;

Conclusion: Found the difference? The future of T-SQL coding is changing with newer versions of SQL Server. Hope someone gets benefitted with this post ! Happy TSQL'ing :)

Multiplication Table Using WHILE Loop

Spending sometime for T-SQL on weekends made me write a stored procedure that returns Multiplication Table for any given number.

Stored Procedure Code:

CREATE PROCEDURE dbo.usp_MultiplicationTable

@N INT

AS

BEGIN

SET NOCOUNT ON;

            DECLARE @I INT = @N, @J INT = 1

            DECLARE @TV TABLE (RESULT VARCHAR(20))

            WHILE (@J <= 10)

            BEGIN

                        INSERT INTO @TV

                        SELECT CAST(@I AS VARCHAR) + CHAR(32) + '*' + CHAR(32) +

                        CAST(@J AS VARCHAR) + CHAR(32) + '=' + CHAR(32) + CAST(@I * @J AS VARCHAR)

                       

                        SET @J = @J + 1

            END

            SELECT * FROM @TV;

END

Execution Code:

On passing '2' as the input value to the stored procedure, it returned below output:

Hope this helps someone!

Conclusion: T-SQL is like a girl friend. You get close to it, it loves back. You go away, it starts hating you. So Happy TSQLing ;)