Showing posts with label T-SQL. Show all posts
Showing posts with label T-SQL. Show all posts

Sunday, December 15, 2013

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



Thursday, December 12, 2013

Sleep if file not available. Load if it is.

One of my old friends, asked me couple of days back how to design a process that will check for file existence in a folder. On finding the file the process should load the file else should sleep for 15 mins and then check for file existence. This process can be handled using advanced T-SQL batch that contains "WAITFOR" and "BCP" like below:

-- Code Starts here
DECLARE @File VARCHAR(500), @Sleeps INT, @FileAvailable INT, @Iteration INT

SET @FileAvailable = 0
SET @Iteration = 0
SET @Sleeps = 4
SET @File = 'C:\Data_DEPT.dat'

-- Check for File Existence
EXEC MASTER.dbo.xp_FileExist @File, @FileAvailable OUTPUT

-- If the file is not available, then the process will wait (sleep) for specified time frame.
-- Here I am placing just 15 seconds time frame, but can you can specify anything.
WHILE @FileAvailable = 0 AND @Iteration < @Sleeps
  BEGIN
    SET @Iteration = @Iteration + 1
    WAITFOR DELAY '00:00:15'
    EXEC MASTER.dbo.xp_FileExist @File, @FileAvailable OUTPUT
  END 

-- If the file is available, then load the file into DEPT2 table.
IF @FileAvailable = 1
BEGIN
DECLARE @BCP VARCHAR(500)
SET @BCP = 'bcp AdventureWorks2012.dbo.DEPT2 IN ' + @File + ' -f C:\Metadata_DEPT.fmt -S HPS\SQL -T'
EXEC MASTER..xp_cmdshell @BCP
END
-- Code Ends here

Execution of above code will result this:



Output of the data in table after file load:


Conclusion: I used two master stored procedures here "xp_FileExist" and "xp_cmdshell". The metadata file (format file) of the FlatFile is assumed to be predefined here. So similar structured files carrying different data can be processed. Hope this helps! Happy TSQLing ;)

Friday, June 1, 2012

UPSERT Statement in T-SQL

The MERGE statement is also referred as the UPSERT statement. It allows us to merge multiple rows from one table into another table. Illustrating MERGE Statement with an example using step by step process and with multiple executions. We shall see SCD Type 1 Implementation here.

Note: If you are looking for MERGE using SCD Type 2 Implemenation, click here.

          MERGE is about comparing a source and destination table and depending upon the changes, new records get inserted and existing get updated.

Basic Syntax:
MERGE [INTO] Target_Table
   USING Source_Table
   ON JOIN_Condition
[WHEN MATCHED [AND Search_Conditions]
   THEN DML_Statement]
[WHEN NOT MATCHED
   THEN DML_Statement]

Implementation:

Source Table: DEPT

CREATE TABLE [dbo].[DEPT](
            [DEPTNO] [numeric](2, 0) NOT NULL,
            [DNAME] [varchar](25) NULL,
            [LOC] [varchar](25) NULL
)

Target Table: DEPT_MERGE

CREATE TABLE [dbo].[DEPT_MERGE](
            [DEPTNO] [numeric](2, 0) NOT NULL,
            [DNAME] [varchar](25) NULL,
            [LOC] [varchar](25) NULL,
            [START_DATE] [datetime] NULL,
            [END_DATE] [datetime] NULL
)

INSERT data into DEPT:

INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (10, N'ACCOUNTING', N'NEW YORK')
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (20, N'RESEARCH', N'MIAMI')
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (30, N'SALES', N'CHICAGO')
INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (40, N'OPERATIONS', N'BOSTON')


Screen Shot:



MERGE Statement Code:
/************************************* MERGE STATEMENT ***********************************/
MERGE DEPT_MERGE AS D
            USING DEPT AS S
            ON D.DEPTNO = S.DEPTNO
WHEN MATCHED AND (S.DNAME <> D.DNAME OR S.LOC <> D.LOC)
THEN
            UPDATE SET
            D.DNAME = S.DNAME,
            D.LOC = S.LOC,
            D.END_DATE = GETDATE()
WHEN NOT MATCHED
THEN
            INSERT (DEPTNO, DNAME, LOC, [START_DATE])
            VALUES (S.DEPTNO, S.DNAME, S.LOC, GETDATE());

Run-1 Execution: 4 Rows affected.





















Run-1 Results: All rows from source into the destination along with start dates.























Further Observation:
To observe how it behaves for the second execution, lets make some changes in the source data. A new record is inserted and an existing record is updated in the source table as below.


Run-2 Execution:  2 rows affected. (1 new row inserted and 1 row updated).






















Run-2 Results: Observe the data changes.



























Summary: This is identical to Slowly Changing Dimension of SSIS. If you want to handle the data changes in T-SQL then this is most efficient way. Hope this helps. Happy T-SQLing. :)