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