Dinesh's Blog :::: Being Compiled ::::: February 2014

Thursday, February 27, 2014

SQL Server: Calculating running totals using T-SQL

This is not a new topic. If you search, you will surely find many posts on this, mostly with traditional techniques but not using newest capabilities such as SQL Windowing. Since I wrote a post on Analysis Services for the same, thought to write the same on T-SQL too. Here is the way of calculating running totals using Window components and functions which provides an efficient way of calculating and simple code structure.

The following query shows the way of calculating. The first code creates a window based on SalesOrderId (which is unique) and get the running totals over SubTotal for a given year. The second code creates a window on OrderDate (which is not unique). This will show the totals for the date instead of Running-Totals for the date unless the range is specified using boundaries. That is the reason for adding upper and lower boundaries using ROW, UNBOUNED PRECEDING and CURRENT ROW inside the window for restricting rows to be participated for the calculation.

 -- Window based on OrderId which is unique
SELECT OrderDate, SalesOrderID, SalesOrderNumber, CustomerID
    , SubTotal Total
    , SUM(SubTotal) OVER(ORDER BY SalesOrderID) RunningTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
 
-- Window based on OrderDate which is not unique
SELECT OrderDate, SalesOrderID, SalesOrderNumber, CustomerID
    , SubTotal Total
    , SUM(SubTotal) OVER(ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RunningTotal
FROM Sales.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID
 

Both queries produce same result;

Here is a comparison on the same using traditional techniques. Though it shows that the query uses window is faster than other queries, always check and pick the best.

 -- Using Window components
SELECT OrderDate, SalesOrderID, SalesOrderNumber, CustomerID
    , SubTotal Total
    , SUM(SubTotal) OVER(ORDER BY SalesOrderID) RunningTotal
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2006
ORDER BY OrderDate, SalesOrderID
 
-- Using self-join
SELECT h1.OrderDate, h1.SalesOrderID, h1.SalesOrderNumber, h1.CustomerID
    , h1.SubTotal Total
    , SUM(h2.SubTotal) RunningTotal
FROM Sales.SalesOrderHeader h1
    INNER JOIN Sales.SalesOrderHeader h2
        ON h1.SalesOrderID >= h2.SalesOrderID
            AND YEAR(h2.OrderDate) = 2006
WHERE YEAR(h1.OrderDate) = 2006
GROUP BY h1.OrderDate, h1.SalesOrderID, h1.SalesOrderNumber
    , h1.CustomerID, h1.SubTotal
ORDER BY h1.OrderDate, h1.SalesOrderID
 
-- Using sub query
SELECT h1.OrderDate, h1.SalesOrderID, h1.SalesOrderNumber, h1.CustomerID
    , h1.SubTotal Total
    , (SELECT SUM(h2.SubTotal) RunningTotal
        FROM Sales.SalesOrderHeader h2
        WHERE h1.SalesOrderID >= h2.SalesOrderID
            AND YEAR(h2.OrderDate) = 2006)
FROM Sales.SalesOrderHeader h1
WHERE YEAR(h1.OrderDate) = 2006
ORDER BY h1.OrderDate, h1.SalesOrderID
 

Wednesday, February 26, 2014

Analysis Services (SSAS): Calculating running totals for a given period using MDX

Calculating running totals against a numeric is not an easy task with both relational databases using T-SQL and multidimensional databases using MDX. In a way, calculation on relational databases is little bit easier using new window functions than using multi-dimensional queries. But it does not mean that the calculation using MDX is much more complex and difficult to write. However it depends on the familiarity. Comparatively we do not write MDX statements as much as we write T-SQLs. That is the reason for tagging “complex” on MDX, that is the reason for spending much time even on a simple query than the time spend with T-SQL for similar implementations.

I had to write a similar query today for calculating running totals mixing with YTD and previous year values. As usual I searched for best practices, there were many posts on this based on either calendar year or financial year but could not find a specific one for calculating running totals for a given period. Wrote it, and thought to share it. Here is the MDX, it is written on AdventureWorksDW. You may find it interesting and helpful.

 WITH MEMBER [Measures].[Running Total]
AS
(
    AGGREGATE([Date].[Calendar].[Month].&[2006]&[10]
            :[Date].[Calendar].CurrentMember
            , [Measures].[Sales Amount])
)
SELECT 
    {[Measures].[Sales Amount]
    , [Measures].[Running Total]} ON 0
    , [Date].[Calendar].[Month].Members ON 1
FROM (SELECT {[Date].[Calendar].[Month].&[2006]&[10]
            :[Date].[Calendar].[Month].&[2007]&[11]} ON 0
        FROM [Adventure Works])
 

Sunday, February 23, 2014

SQL Server Management Studio: Exploring General Options for T-SQL – Part I

SQL Server Management Studio is an integrated management, development and querying application that we use for working with SQL Server instances and databases. It is based on on Visual Studio shell and it is the key application for both developers and administrators for working with SQL Server.

Just like other Microsoft applications, Management Studio allows you to adjust/customize the environment as you wish. It offers many settings, mostly unknown to us, that can be used for making the application more user-friendly and more flexible. Thought to explore some of important ones via number of notes. Here is the first one, this note explores settings listed under Text Editing for T-SQL .

The general options related to T-SQL can be found under Tools Menu –> Options menu –> Text Editor –> Transact-SQL –> General;

There are 10 options that can be set under T-SQL –> General. However some of them are not related to T-SQL. Let’s explore one by one.

Auto list members
Selecting this option lists columns, functions, tables, etc. out on a pop-up menu based on the statement you write, making intelliSense enabled. By selecting the relevant item, the code can be completed without typing the whole word avoiding mistakes caused by misspelling and increasing the typing time.
Hide advanced members
I believe that this option is not applicable for SQL Server but yet to be confirmed. As per BOL, this hides members marked as “advanced” limiting items loaded to the pop-up. This option is disabled when “Auto list members” is cleared or no members are marked as “advanced”.
Parameter information
If this option is selected, the complete syntax of current declaration or procedure is displayed with its parameters. The parameter which is bold shows the one needs to be set as the next parameter.
Enable virtual space
Selecting this makes the position of cursor consistence with all the lines in the code regardless of the length of the line. By default, this is disable. Therefore the position of the cursor is not consistence when moving up and down. Have a look on below image;

Now, if the cursor is moved down, it will be positioned to column 31.

If the option is selected, position of the cursor will be remained in same column.

The reason for this is, when the option is selected, tabs or spaces are automatically added to complete the line.
Word wrap
This makes the entire line you have typed visible in viewable editor area even though it has extended beyond the area horizontally.

Editor with option cleared.

Editor with option selected.
Show visual glyphs for word wrap
This option comes as a sub option of “Word wrap” hence it is only enabled when “Word wrap” is selected. Selecting this makes a glyph (a graphical symbol that shows a returned-arrow) appeared on wrapped lines indicating that the lines are wrapped.
Apply Cut or Copy commands to blank line when there is no selection
This setting allows us to cut or copy blank lines and paste without selecting anything. Look at the below image. It has a blank line and the cursor is positioned in it. Now press Ctrl+C for copying;

If the option is selected, Ctrl+C will copy the blank line and Ctrl+V will insert a new blank line.
Line numbers
Selecting this options displays line numbers for each line;
Enable single-click URL navigation
Selecting this option makes URL in the editor clickable for opening the web page. If the option is cleared, there will be no change on the cursor when passing over the URL but if it is selected, URL will be shown as a hyperlink and can be click on it while holding the Ctrl key. The first image shows the editor with the option cleared and the second shows with the option selected.
Navigation bar
This option is for getting all objects and procedures displayed in drop-downs at the top of the editor for easy navigation. However this option is not enabled for SQL Server.

Friday, February 21, 2014

SQL Server 2012: Getting Previous and Next values using Offset functions

How can we access other rows in a set while accessing one particular row? In other words, can we access values in other rows other than current row while the current row is being processed? There were no built-in functions for supporting this functionality with previous versions of SQL Server but there were many ways of getting the required result generated. One common way was linking the same table to itself either using as a derived table or CTE. Microsoft SQL Server offers four offset functions for supporting this requirement. Here is note on it;

SQL Server 2012 Offset Functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE
Offset functions allow to access values located in other rows while accessing the current row;

Function	Description
LAG	LAG works on window partition and window order clauses. It allows to access a value of a row at a certain offset from the current row which appears before the current row based on the order specified. It accepts three parameters; value (or column) which needs to be returned, offset as optional (1 is default), and default value to be returned in case of no row at the specified offset (null is default).
LEAD	LEAD works on same manner, just like LAG. Only different is, while LAG is looking for records before the current row, LEAD is looking for records after the current row.
FIRST_VALUE	This allows to access values from the first row in the window frame. The first value of the first row is accessed with a window frame extent ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
LAST_VALUE	This allows to access values from the last row in the windows frame. The extent ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING should be used with LAST_VALUE.

Here is an example for offset functions;

 USE AdventureWorks2012
GO
 
-- Create a view for forming a set that contains sales amount for years and months.
CREATE VIEW dbo.SalesView
    AS
    SELECT YEAR(h.OrderDate) OrderYear, MONTH(OrderDate) OrderMonth
        , SUM(h.SubTotal) Total
    FROM Sales.SalesOrderHeader h
    GROUP BY YEAR(h.OrderDate), MONTH(OrderDate)
GO
 
 
SELECT
    OrderYear, OrderMonth, Total
    -- Getting previous month value for the year
    , LAG(Total, 1, 0) OVER (PARTITION BY OrderYear ORDER BY OrderMonth) PreviousMonthTotal
    -- Getting next month value for the year
    , LEAD(Total, 1, 0) OVER (PARTITION BY OrderYear ORDER BY OrderMonth) NextMonthTotal
    -- Getting first month value for the year
    , FIRST_VALUE(Total) OVER (PARTITION BY OrderYear ORDER BY OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) FirstMonthValue
    -- Getting last month value for the yea
    , LAST_VALUE(Total) OVER (PARTITION BY OrderYear ORDER BY OrderMonth ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) LastMonthValue
FROM dbo.SalesView
ORDER BY 1, 2
 

Monday, February 17, 2014

UNION ALL is possible. How about INTERSECT ALL and EXCEPT ALL?

Not expecting duplicates but a significant improvement in performance, we tend to use UNION ALL when no duplicates are guaranteed in combining two sets. The reason is, UNION ALL does not performing an additional task for filtering (or removing) duplicates, hence gives a better performance than UNION. However, the purpose of UNION ALL is not for improving the performance but for producing the result with duplicates if exist. While this was being taught during my classes, a thought came into my mind: Why other set operators such as INTERSECT and EXCEPT do not offer the same built-in functionality like INTERSECT ALL and EXCEPT all?

A usual quick search did not give me the required answer but proved that it is not available as a built-in functionality. There were few possible solutions but the one given by T-SQL expert Itzik Ben-Gan was quite interesting. Here is a small note on INTERSECT and EXCEPT and implementation suggested by Itzik Ben-Gan for INTERSECT ALL and EXCEPT ALL.

INTERSECT and INTERSECT ALL
INTERSECT allows us to retrieve only rows that are available in both two sets. This removes duplicates if found. It basically behaves as DISTINCT INTERSECT. If duplicates to be included to the result, a workaround is required and it can be easily done with ROW_NUMBER ranking function. All required columns need to be added under PARTITION BY clause and SELECT < constant> needs to be added for ODER BY clause for instructing SQL Server to not consider the order of the window. This generates aN unique number of all records, making them available in the final resultset of INTERSECT. Here is a sample code for this;

 USE tempdb
GO
 
-- creating table 1
CREATE TABLE dbo.InternetSales
(
    SaleDate datetime NOT NULL
    , Product varchar(100) NOT NULL
    , Amount money NOT NULL
)
GO
 
-- creating table 2
CREATE TABLE dbo.ResellerSales
(
    SaleDate datetime NOT NULL
    , Product varchar(100) NOT NULL
    , Amount money NOT NULL
)
GO
 
-- inserting values for table 1
INSERT INTO dbo.InternetSales
    (SaleDate, Product, Amount)
VALUES
    ('01/01/2014', 'A', 100)
    , ('02/01/2014', 'B', 100)
    , ('03/01/2014', 'B', 100)
    , ('04/01/2014', 'B', 100)
    , ('05/01/2014', 'C', 100)
    , ('05/01/2014', 'C', 100)
 
-- inserting values for table 2
INSERT INTO dbo.ResellerSales
    (SaleDate, Product, Amount)
VALUES
    ('01/01/2014', 'A', 100)
    , ('02/01/2014', 'B', 100)
    , ('03/01/2014', 'B', 100)
    , ('04/01/2014', 'D', 100)
    , ('05/01/2014', 'D', 100)
 
-- checking INTERSECT, will return only A, B
-- If duplicates were included, should return A, B, B
SELECT Product FROM dbo.InternetSales
INTERSECT
SELECT Product FROM dbo.ResellerSales
 
-- Adding ROW_NUMBER for creating unique numbers
-- And using CTE for removing the number
-- This returns A, B, B
WITH cte
AS
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 0)) AS number 
        , Product 
    FROM dbo.InternetSales
    INTERSECT
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 0)) AS number 
        , Product 
    FROM dbo.ResellerSales
)
SELECT Product FROM cte;
 

EXCEPT and EXCEPT ALL
EXCEPT returns all distinct rows found in “left” (or first) set that are not found in “right” (or second) set. This discards duplicates too. If duplicates are required, same workaround can be applied. Here is the code for it;

 -- checking EXCEPT, will return only C
-- If duplicates were included, should return B, C, C
SELECT Product FROM dbo.InternetSales
EXCEPT
SELECT Product FROM dbo.ResellerSales
 
-- Adding ROW_NUMBER for creating unique numbers
-- And using CTE for removing the number
-- This returns B, C, C (Duplicates)
WITH cte
AS
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 0)) AS number 
        , Product 
    FROM dbo.InternetSales
    EXCEPT
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 0)) AS number 
        , Product 
    FROM dbo.ResellerSales
)
SELECT Product FROM cte;
 
-- Cleaning
DROP TABLE dbo.InternetSales
DROP TABLE dbo.ResellerSales
 

Sunday, February 16, 2014

Best way to find records exist in one set that do not appear in other set: NOT IN | LEFT OUTER | EXCEPT

Finding records exist only in one set that do not exist in another is a common requirement in database developments. Finding customers who have not placed orders, finding products that have not been purchased by any customers are general examples for it. There are many difference ways of obtaining the required record set and the mentioned methods/operators in the title are the commonly used ones for achieving this. However which gives the better performance is questionable, hence let’s analyze them and see.

Let’s try to get all products that have not been purchased by customers from AdventureWorks database. Have a look on all three SELECTs. They return product ids from Production.Product table which are not exist in Sales.SalesOrderDetail table.

 USE AdventureWorks2012
GO
 
-- Finding unsold products using NOT IN
SELECT p.ProductID
FROM Production.Product p
WHERE p.ProductID NOT IN (SELECT ProductID from Sales.SalesOrderDetail)
ORDER BY 1
 
-- Finding unsold products using LEFT OUTER JOIN
SELECT p.ProductID
FROM Production.Product p
    LEFT OUTER JOIN Sales.SalesOrderDetail d
        ON p.ProductID = d.ProductID
WHERE d.ProductID IS NULL
ORDER BY 1
 
-- Finding unsold products using EXCEPT
SELECT p.ProductID
FROM Production.Product p
EXCEPT
SELECT ProductID
FROM Sales.SalesOrderDetail
ORDER BY 1
 

All three produce the same result;

Let’s analyze the execution plans of all three. Note the “Query Cost – Relative to the batch” too.

As you see, plans for NOT IN and EXCEPT are same and the performance of them are good but LEFT OUTER is different. LEFT OUTER has used “Merge Join” whereas other two have used “Nested Loops” which is low cost join. In this scenario, LEFT OUTER does not offer much benefits but NOT IN and EXCEPT give better performance.

However, this behavior is not guaranteed with all scenario hence we cannot conclude that NOT IN and EXCEPT provide better performance rather than LEFT OUTER. This is totally depend on factors such as index availability and number of records. Therefore, best way is, trying with all ways and pick the best for the situation. In addition to the mentioned methods, there are few more popular ways such as NOT EXISTS and OUTER APPLY. All these can be used for retrieving the required result, however, as mentioned above, best way can be determined only by trying the same with all the ways.

Saturday, February 15, 2014

What is the difference between CROSS APPLY and OUTER APPLY?

Processing rows in one set using another set is a common coding pattern used mostly with combining or comparing rows from sets. SQL Server offers three set operators: UNION, INTERSECT and EXCEPT, for handling scenario which compares rows from one set to another and completes the return set. In some specific cases, an alternative operator which is APPLY can be used for handling similar scenario. Here is post on APPLY operator;

The APPLY operator not exactly a set operator. It is a table operator which evaluates rows in one set based on an expression set with another set, NOT combining two sets in similar manner used by other set operators but like a JOIN. It is used with FROM clause and just like JOINs, two sets are marked as “left” set and “right” set. The “right” set is always either a table-valued function or a derived table which gets processed for each row returning from the “left” set. The syntax for APPLY is as follows;

SELECT <column list>
FROM <left-table> AS <alias>
APPLY <derived table | table-valued function> AS <alias>

There are two types of APPLY: CROSS APPLY and OUTER APPLY.

CROSS APPLY
CROSS APPLY processes the “right” set for each row found in the “left” set in a similar CROSS-JOIN manner. However, if an empty result is generated by the “right” set for the correlated row given by “left”, the row will NOT be included in the resultset, in a similar INNER-JOIN fashion. Here is an example for CROSS APPLY.

 USE AdventureWorks2012
GO
 
-- This returns all products
-- There are 504 products
SELECT ProductID, Name
FROM Production.Product
ORDER BY ProductID
GO
 
-- Create a table-valued function that returns top orders related to given product
CREATE FUNCTION dbo.GetTopOrdersForTheProduct (@ProductId int)
RETURNS TABLE
AS
RETURN 
    SELECT TOP (2) h.SalesOrderNumber, h.OrderDate, (d.OrderQty * d.UnitPrice) OrderAmount
    FROM Sales.SalesOrderHeader h 
        INNER JOIN Sales.SalesOrderDetail d
            ON h.SalesOrderID = d.SalesOrderID
    WHERE ProductID = @ProductId
    ORDER BY (d.OrderQty * d.UnitPrice) DESC
GO
 
-- check the function
-- this does not return any records as there are no order for the product id 1
SELECT * FROM dbo.GetTopOrdersForTheProduct (1)
-- this returns records as there are orders for the product 707
SELECT * FROM dbo.GetTopOrdersForTheProduct (707)
 
-- Joining SELECT with TVF using CROSS APPLY
-- This does not return products like 1, 2
SELECT ProductID, Name, o.SalesOrderNumber, o.OrderDate, o.OrderAmount
FROM Production.Product
    CROSS APPLY 
        dbo.GetTopOrdersForTheProduct (ProductID) o
ORDER BY ProductID
 

OUTER APPLY
The behavior of OUTER APPLY is same as CROSS APPLY except one which is the only difference between CROSS APPLY and OUTER APPLY. In the presence of an empty result from “right” set, CROSS APPLY excludes the row found in “left” from the returned result but OUTER APPLY includes it. This behavior is conceptually similar to LEFT OUTER JOIN. Here is the code describing it;

 -- Joining SELECT with TVF using CROSS APPLY
-- This returns all from Product table
SELECT ProductID, Name, o.SalesOrderNumber, o.OrderDate, o.OrderAmount
FROM Production.Product
    OUTER APPLY 
        dbo.GetTopOrdersForTheProduct (ProductID) o
ORDER BY ProductID
 

Friday, February 14, 2014

Types of SQL Server Sub Queries: Self-Contained, Correlated, Scalar, Multi-Valued, Table-Valued

A Sub query is a SELECT statement that is embedded to another query. Or in other words, a SELECT statement that is nested to another SELECT. Or in a simplest way, it is a query within a query. This posts speaks about types related and terms used with sub queries.

What are Inner queries and Outer queries?
Once a SELECT is written within another SELECT, the one written inside becomes the INNER QUERY. The query that holds the inner query is called as OUTER QUERY. See the below query. The query refers the Sales.Customer table is the Outer Query. The query written on Sales.SalesOrderHeader is the Inner Query.

 SELECT *
FROM Sales.Customer
WHERE CustomerID = (SELECT TOP (1) 
                CustomerID 
                FROM Sales.SalesOrderHeader 
                ORDER BY SubTotal DESC)
 

What are Scalar, Multi-valued and Table-valued Sub Queries?
Sub queries can be categorized based on their return type. If the query returns a single value, it becomes a scalar sub query. Scalar sub query behaves as an expression for the outer query and it can be used with clauses like SELECT and WHERE. Sub query produces NULL value if the result of it is empty and equality operators (=, != , <, etc.) are used with predicates when they are used with WHERE clauses.

Multi-valued sub query still return a single column but it may produce multiple values (can be considered as multiple records) for the column. This is mainly used with IN predicate and based on matching values, predicate either returns TRUE or FALSE.

Table-valued sub query returns a whole table; multiple columns, multiple rows. This is mainly used with derived tables.

Here are some samples for these three types;

 -- Get all orders from last recorded customer
-- This sub query is a scalar sub query
SELECT *
FROM Sales.SalesOrderHeader
WHERE CustomerID = (SELECT MAX(CustomerID) 
                FROM Sales.Customer)
 
-- Get top Customers
-- This sub query is a multi-valued sub query
SELECT * 
FROM Sales.Customer
WHERE CustomerID IN (SELECT CustomerID
                    FROM Sales.SalesOrderHeader
                    WHERE SubTotal > 100000)
 
-- Get order amount for years and months
-- This sub query is a table-valued sub query
SELECT ROW_NUMBER() OVER (ORDER BY d.OrderYear, d.OrderMonth)
    , d.OrderYear, d.OrderMonth
    , d.OrderAmount
FROM 
(SELECT YEAR(OrderDate) OrderYear
    , MONTH(OrderDate) OrderMonth
    , SUM(SubTotal) OrderAmount
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
) d
 

What are Self-Contained and Correlated Queries?

Sub query always has an outer query which it is nested with. If the sub query is completely independent and do not require any input from outer query, it is called as a Self-Contained Sub Query. Self-Contained sub query is evaluated once for the outer query and result is used with all records produced by outer query.

Correlated sub query is a query that requires an input from its outer query. This sub query is fully dependent on the outer query and cannot be executed without required attributes from outer query. This behavior increases the cost of the execution of the sub query as it needs to be executed for each of row of outer query.

Here are some samples for them.

 -- This returns year total with full total and last year total
-- First sub query is a Self-Contained Scalar sub query
-- Second sub query is a Correlated Scalar sub query
SELECT YEAR(h.OrderDate) OrderYear
    , SUM(h.SubTotal) OrderAmount
    , (SELECT SUM(SubTotal) 
        FROM Sales.SalesOrderHeader) TotalOrderAmount
    , (SELECT SUM(SubTotal) 
        FROM Sales.SalesOrderHeader 
        WHERE YEAR(OrderDate) = YEAR(h.OrderDate) - 1) LastYearTotalAmount
FROM Sales.SalesOrderHeader h
GROUP BY YEAR(h.OrderDate)
 