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.

  1. -- Window based on OrderId which is unique
  2. SELECT OrderDate, SalesOrderID, SalesOrderNumber, CustomerID
  3.     , SubTotal Total
  4.     , SUM(SubTotal) OVER(ORDER BY SalesOrderID) RunningTotal
  5. FROM Sales.SalesOrderHeader
  6. ORDER BY OrderDate, SalesOrderID
  8. -- Window based on OrderDate which is not unique
  9. SELECT OrderDate, SalesOrderID, SalesOrderNumber, CustomerID
  10.     , SubTotal Total
  11.     , SUM(SubTotal) OVER(ORDER BY OrderDate
  13. FROM Sales.SalesOrderHeader
  14. 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.

  1. -- Using Window components
  2. SELECT OrderDate, SalesOrderID, SalesOrderNumber, CustomerID
  3.     , SubTotal Total
  4.     , SUM(SubTotal) OVER(ORDER BY SalesOrderID) RunningTotal
  5. FROM Sales.SalesOrderHeader
  6. WHERE YEAR(OrderDate) = 2006
  7. ORDER BY OrderDate, SalesOrderID
  9. -- Using self-join
  10. SELECT h1.OrderDate, h1.SalesOrderID, h1.SalesOrderNumber, h1.CustomerID
  11.     , h1.SubTotal Total
  12.     , SUM(h2.SubTotal) RunningTotal
  13. FROM Sales.SalesOrderHeader h1
  14.     INNER JOIN Sales.SalesOrderHeader h2
  15.         ON h1.SalesOrderID >= h2.SalesOrderID
  16.             AND YEAR(h2.OrderDate) = 2006
  17. WHERE YEAR(h1.OrderDate) = 2006
  18. GROUP BY h1.OrderDate, h1.SalesOrderID, h1.SalesOrderNumber
  19.     , h1.CustomerID, h1.SubTotal
  20. ORDER BY h1.OrderDate, h1.SalesOrderID
  22. -- Using sub query
  23. SELECT h1.OrderDate, h1.SalesOrderID, h1.SalesOrderNumber, h1.CustomerID
  24.     , h1.SubTotal Total
  25.     , (SELECT SUM(h2.SubTotal) RunningTotal
  26.         FROM Sales.SalesOrderHeader h2
  27.         WHERE h1.SalesOrderID >= h2.SalesOrderID
  28.             AND YEAR(h2.OrderDate) = 2006)
  29. FROM Sales.SalesOrderHeader h1
  30. WHERE YEAR(h1.OrderDate) = 2006
  31. 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.

  1. WITH MEMBER [Measures].[Running Total]
  2. AS
  3. (
  4.     AGGREGATE([Date].[Calendar].[Month].&[2006]&[10]
  5.             :[Date].[Calendar].CurrentMember
  6.             , [Measures].[Sales Amount])
  7. )
  9.     {[Measures].[Sales Amount]
  10.     , [Measures].[Running Total]} ON 0
  11.     , [Date].[Calendar].[Month].Members ON 1
  12. FROM (SELECT {[Date].[Calendar].[Month].&[2006]&[10]
  13.             :[Date].[Calendar].[Month].&[2007]&[11]} ON 0
  14.         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.

  1. 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.

  2. 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”.
  3. 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.

  4. 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.
  5. 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.
  6. 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.

  7. 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.

  8. Line numbers
    Selecting this options displays line numbers for each line;

  9. 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.


  10. 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;

  1. USE AdventureWorks2012
  2. GO
  4. -- Create a view for forming a set that contains sales amount for years and months.
  5. CREATE VIEW dbo.SalesView
  6.     AS
  7.     SELECT YEAR(h.OrderDate) OrderYear, MONTH(OrderDate) OrderMonth
  8.         , SUM(h.SubTotal) Total
  9.     FROM Sales.SalesOrderHeader h
  10.     GROUP BY YEAR(h.OrderDate), MONTH(OrderDate)
  11. GO
  14. SELECT
  15.     OrderYear, OrderMonth, Total
  16.     -- Getting previous month value for the year
  17.     , LAG(Total, 1, 0) OVER (PARTITION BY OrderYear ORDER BY OrderMonth) PreviousMonthTotal
  18.     -- Getting next month value for the year
  19.     , LEAD(Total, 1, 0) OVER (PARTITION BY OrderYear ORDER BY OrderMonth) NextMonthTotal
  20.     -- Getting first month value for the year
  22. AND CURRENT ROW) FirstMonthValue
  23.     -- Getting last month value for the yea
  26. FROM dbo.SalesView
  27. 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 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;

  1. USE tempdb
  2. GO
  4. -- creating table 1
  5. CREATE TABLE dbo.InternetSales
  6. (
  7.     SaleDate datetime NOT NULL
  8.     , Product varchar(100) NOT NULL
  9.     , Amount money NOT NULL
  10. )
  11. GO
  13. -- creating table 2
  14. CREATE TABLE dbo.ResellerSales
  15. (
  16.     SaleDate datetime NOT NULL
  17.     , Product varchar(100) NOT NULL
  18.     , Amount money NOT NULL
  19. )
  20. GO
  22. -- inserting values for table 1
  23. INSERT INTO dbo.InternetSales
  24.     (SaleDate, Product, Amount)
  25. VALUES
  26.     ('01/01/2014', 'A', 100)
  27.     , ('02/01/2014', 'B', 100)
  28.     , ('03/01/2014', 'B', 100)
  29.     , ('04/01/2014', 'B', 100)
  30.     , ('05/01/2014', 'C', 100)
  31.     , ('05/01/2014', 'C', 100)
  33. -- inserting values for table 2
  34. INSERT INTO dbo.ResellerSales
  35.     (SaleDate, Product, Amount)
  36. VALUES
  37.     ('01/01/2014', 'A', 100)
  38.     , ('02/01/2014', 'B', 100)
  39.     , ('03/01/2014', 'B', 100)
  40.     , ('04/01/2014', 'D', 100)
  41.     , ('05/01/2014', 'D', 100)
  43. -- checking INTERSECT, will return only A, B
  44. -- If duplicates were included, should return A, B, B
  45. SELECT Product FROM dbo.InternetSales
  47. SELECT Product FROM dbo.ResellerSales
  49. -- Adding ROW_NUMBER for creating unique numbers
  50. -- And using CTE for removing the number
  51. -- This returns A, B, B
  52. WITH cte
  53. AS
  54. (
  55.     SELECT
  56.         ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 0)) AS number
  57.         , Product
  58.     FROM dbo.InternetSales
  59.     INTERSECT
  60.     SELECT
  61.         ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 0)) AS number
  62.         , Product
  63.     FROM dbo.ResellerSales
  64. )
  65. SELECT Product FROM cte;

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;

  1. -- checking EXCEPT, will return only C
  2. -- If duplicates were included, should return B, C, C
  3. SELECT Product FROM dbo.InternetSales
  5. SELECT Product FROM dbo.ResellerSales
  7. -- Adding ROW_NUMBER for creating unique numbers
  8. -- And using CTE for removing the number
  9. -- This returns B, C, C (Duplicates)
  10. WITH cte
  11. AS
  12. (
  13.     SELECT
  14.         ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 0)) AS number
  15.         , Product
  16.     FROM dbo.InternetSales
  17.     EXCEPT
  18.     SELECT
  19.         ROW_NUMBER() OVER (PARTITION BY Product ORDER BY (SELECT 0)) AS number
  20.         , Product
  21.     FROM dbo.ResellerSales
  22. )
  23. SELECT Product FROM cte;
  25. -- Cleaning
  26. DROP TABLE dbo.InternetSales
  27. 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.

  1. USE AdventureWorks2012
  2. GO
  4. -- Finding unsold products using NOT IN
  5. SELECT p.ProductID
  6. FROM Production.Product p
  7. WHERE p.ProductID NOT IN (SELECT ProductID from Sales.SalesOrderDetail)
  8. ORDER BY 1
  10. -- Finding unsold products using LEFT OUTER JOIN
  11. SELECT p.ProductID
  12. FROM Production.Product p
  13.     LEFT OUTER JOIN Sales.SalesOrderDetail d
  14.         ON p.ProductID = d.ProductID
  15. WHERE d.ProductID IS NULL
  16. ORDER BY 1
  18. -- Finding unsold products using EXCEPT
  19. SELECT p.ProductID
  20. FROM Production.Product p
  21. EXCEPT
  22. SELECT ProductID
  23. FROM Sales.SalesOrderDetail
  24. 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 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.

  1. USE AdventureWorks2012
  2. GO
  4. -- This returns all products
  5. -- There are 504 products
  6. SELECT ProductID, Name
  7. FROM Production.Product
  8. ORDER BY ProductID
  9. GO
  11. -- Create a table-valued function that returns top orders related to given product
  12. CREATE FUNCTION dbo.GetTopOrdersForTheProduct (@ProductId int)
  14. AS
  15. RETURN
  16.     SELECT TOP (2) h.SalesOrderNumber, h.OrderDate, (d.OrderQty * d.UnitPrice) OrderAmount
  17.     FROM Sales.SalesOrderHeader h
  18.         INNER JOIN Sales.SalesOrderDetail d
  19.             ON h.SalesOrderID = d.SalesOrderID
  20.     WHERE ProductID = @ProductId
  21.     ORDER BY (d.OrderQty * d.UnitPrice) DESC
  22. GO
  24. -- check the function
  25. -- this does not return any records as there are no order for the product id 1
  26. SELECT * FROM dbo.GetTopOrdersForTheProduct (1)
  27. -- this returns records as there are orders for the product 707
  28. SELECT * FROM dbo.GetTopOrdersForTheProduct (707)
  30. -- Joining SELECT with TVF using CROSS APPLY
  31. -- This does not return products like 1, 2
  32. SELECT ProductID, Name, o.SalesOrderNumber, o.OrderDate, o.OrderAmount
  33. FROM Production.Product
  34.     CROSS APPLY
  35.         dbo.GetTopOrdersForTheProduct (ProductID) o
  36. ORDER BY ProductID


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;

  1. -- Joining SELECT with TVF using CROSS APPLY
  2. -- This returns all from Product table
  3. SELECT ProductID, Name, o.SalesOrderNumber, o.OrderDate, o.OrderAmount
  4. FROM Production.Product
  5.     OUTER APPLY
  6.         dbo.GetTopOrdersForTheProduct (ProductID) o
  7. 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.

  1. SELECT *
  2. FROM Sales.Customer
  3. WHERE CustomerID = (SELECT TOP (1)
  4.                 CustomerID
  5.                 FROM Sales.SalesOrderHeader
  6.                 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;

  1. -- Get all orders from last recorded customer
  2. -- This sub query is a scalar sub query
  3. SELECT *
  4. FROM Sales.SalesOrderHeader
  5. WHERE CustomerID = (SELECT MAX(CustomerID)
  6.                 FROM Sales.Customer)
  8. -- Get top Customers
  9. -- This sub query is a multi-valued sub query
  10. SELECT *
  11. FROM Sales.Customer
  12. WHERE CustomerID IN (SELECT CustomerID
  13.                     FROM Sales.SalesOrderHeader
  14.                     WHERE SubTotal > 100000)
  16. -- Get order amount for years and months
  17. -- This sub query is a table-valued sub query
  18. SELECT ROW_NUMBER() OVER (ORDER BY d.OrderYear, d.OrderMonth)
  19.     , d.OrderYear, d.OrderMonth
  20.     , d.OrderAmount
  21. FROM
  22. (SELECT YEAR(OrderDate) OrderYear
  23.     , MONTH(OrderDate) OrderMonth
  24.     , SUM(SubTotal) OrderAmount
  25. FROM Sales.SalesOrderHeader
  26. GROUP BY YEAR(OrderDate), MONTH(OrderDate)
  27. ) 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.

  1. -- This returns year total with full total and last year total
  2. -- First sub query is a Self-Contained Scalar sub query
  3. -- Second sub query is a Correlated Scalar sub query
  4. SELECT YEAR(h.OrderDate) OrderYear
  5.     , SUM(h.SubTotal) OrderAmount
  6.     , (SELECT SUM(SubTotal)
  7.         FROM Sales.SalesOrderHeader) TotalOrderAmount
  8.     , (SELECT SUM(SubTotal)
  9.         FROM Sales.SalesOrderHeader
  10.         WHERE YEAR(OrderDate) = YEAR(h.OrderDate) - 1) LastYearTotalAmount
  11. FROM Sales.SalesOrderHeader h
  12. GROUP BY YEAR(h.OrderDate)

Monday, February 10, 2014

Types of SQL Server Built-In Functions: Scalar, Grouped Aggregate, Window, Rowset

SQL Server offers many number of built-in functions for getting commonly required operations done, minimizing the time and resources need to implement them for handling business logics. These functions can be categorized with various classifications. One way of categorizing them is, understanding their scope of input and return type. Under this categorization, they can be organized into four groups: Scalar, Grouped Aggregate, Window, and Rowset. The organization of functions under this categorization makes some of functions member of more than one group.

Here is a small note on this categorization.

Scalar Functions
These functions accept zero or one or more than one value (or a row), process them as per its intended functionalities, and return a single value. These functions can be further categorized as string, conversion, mathematical, etc.. Some of these functions are deterministic  and some are nondeterministic. (Deterministic functions always return same result any time they are called with same input and same database state. Nondeterministic functions may return different result each time they are called with same input and same database state) Here are some examples for Scalar Functions;

  1. -- accepts no arguments
  4. -- accepts one argument
  5. SELECT LEN('Hello World')
  7. -- accepts multiple argument
  8. SELECT STUFF('accepts one argument', 9, 3, 'multiple')

Grouped Aggregate Functions

These functions perform their intended operations on a set of rows defined in a GROUP BY clause and return a single value. If GROUP BY is not provided, all rows are considered as one set and operation is performed on all rows. All aggregate functions are deterministic and ignore NULLs except the COUNT(*).

  1. -- Aggregate functions without GROUP BY
  2. SELECT COUNT(*), MIN(ListPrice), MAX(ListPrice)
  3. FROM Production.Product
  5. -- Aggregate functions with GROUP BY
  6. SELECT YEAR(OrderDate) OrderYear, SUM(SubTotal) Total
  7. FROM Sales.SalesOrderHeader
  8. GROUP BY YEAR(OrderDate)

Window Functions

The operation of Window Functions are bit different from other functions. These functions produce a scalar value based on a calculation done on a subset of the main recordset. The subset used for producing the result is called as the Window. A different order can be defined for the window for performing the calculation without affecting the order of input rows or output rows. In addition to that, partitioning the window is also allowed.

Window is specified using OVER clause with its specification. SQL Server offers number of Window function for handling ranking, aggregation and offset comparisons between rows.

Here are few example on Window Functions;

  1. -- This uses SUM over Territory subsets (window)
  2. SELECT t.Territory, t.OrderYear, t.Total
  3.     , SUM(t.Total) OVER (PARTITION BY MONTH(t.Territory)) TotalByTerritory
  4. FROM (SELECT TerritoryId Territory, YEAR(OrderDate) OrderYear, SUM(SubTotal) Total
  5.     FROM Sales.SalesOrderHeader
  6.     GROUP BY TerritoryId, YEAR(OrderDate)) as t
  7. ORDER BY 1, 2
  9. -- This uses ROW_NUMBER over all records
  10. SELECT SalesOrderID, SubTotal
  11.     , ROW_NUMBER() OVER (ORDER BY SubTotal) AS OrderValuePosition
  12. FROM Sales.SalesOrderHeader
  13. WHERE YEAR(OrderDate) = 2008
  14. ORDER BY SalesOrderID

Rowset Functions
Rowset functions accept input parameters and return objects that can be used as tables in TSQL statements. SQL Server offers four Rowset functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML) and they are nondeterministic functions.

Here is an example for it.

  1. -- Loading data from a text file
  2. -- as single BLOB
  4. OPENROWSET(BULK N'D:\TestData.txt', SINGLE_BLOB) AS Document