web 2.0

Ranking Functions in SQL SERVER 2008


Introduction

Ranking functions are used to provide simple analytics such as statistical ordering or segmentation. In this Article, you learn how to use the four ranking functions that ship with SQL Server 2008.


What Ranking Functions are available in SQL Sever?

T-SQL has four functions that can be used for ranking data:

What is ROW_NUMBER function?

The ROW_NUMBER function assigns a number from 1 to n based on a user-specified sorting order. ROW_NUMBER does not account for ties within the result set, so if you have rows with the same values within the column(s) that you are ordering by, repeated calls to the database for the same result set can produce different row numbering.

T-SQL Syntax for ROW_NUMBER


ROW_NUMBER() OVER (ORDER BY <Column_Name>) 

ROW_NUMBER() OVER (PARTITION BY <Column_Name>)


Examples of ROW_NUMBER

The following examples returns the salesperson along with his or her year-to-date sales and is numbered in descending order according to the year-to-date sales amount:


Using ROW_NUMBER function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


Using ROW_NUMBER function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


What is RANK function?

If you need to number a result set but also deal with ties, you can use the RANK function. If the result set does not have any ties, RANK produces the same results as ROW_NUMBER.

However, if there are ties, RANK assigns the same value to each row that is tied and then skips to the next value, leaving a gap in the sequence corresponding to the number of rows that were tied.

T-SQL Syntax for RANK


RANK() OVER (ORDER BY <Column_Name>) 

RANK() OVER (PARTITION BY <Column_Name>)


Examples of RANK

The following examples show how RANK is applied to duplicates as well as within each aggregate grouping:


Using RANK function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,RANK() OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


Using RANK function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,RANK() OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


What is DENSE_RANK function?

If you do not want any gaps in a sequence, you can use the DENSE_RANK function. DENSE_ RANK assigns the same value to each duplicate but does not produce gaps in the sequence.

T-SQL Syntax for DENSE_RANK


DENSE_RANK() OVER (ORDER BY <Column_Name>) 

DENSE_RANK() OVER (PARTITION BY <Column_Name>)


Examples of DENSE_RANK

The following examples show how DENSE_RANK is applied to duplicates as well as within each aggregate grouping:


Using DENSE_RANK function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,DENSE_RANK() OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


Using DENSE_RANK function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,DENSE_RANK() OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


What is NTILE function?

NTILE is used to divide a result set into approximately equal groups. For example, if you wanted to split a result set into six groups with approximately the same number of rows in each group, you could use NTILE(6).

T-SQL Syntax for NTILE


NTILE(integer_expression) OVER (ORDER BY <Column_Name>) 

NTILE(integer_expression) OVER (PARTITION BY <Column_Name>)


Examples of NTILE

The following examples show how NTILE can be used to segment a result set:


Using NTILE function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


Using NTILE function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


References

MSDN - SQL Server 2008

Tags: , , , , , , , , ,

SQL Server

GROUPING SETS in SQL SERVER 2008



Introduction

GROUPING SET were introduced in SQL Server 2008, which allow multiple groupings to be returned in one record set. In this article I explained the answers for the questions which raises to everybody’s mind who hear this for the first time.


What is GROUPING SETS?

GROUPING SETS were added in SQL Server 2008 to give you greater flexibility when defining SELECT statements that include aggregate functions. We can fetch multiple groups set by executing just one SQL query. Although there are other alternates to get multiple group sets with one SQL query for example by using ROLLUP and CUBE clauses. But GROUPING SETS provide more flexibility than other alternates.


T-SQL Syntax for GROUPING SETS

SELECT 
     Column1, 
     Column2, 
     COUNT(Column3) As Alias_Name 
FROM Table_Name 
GROUP BY GROUPING SETS 
     ( 
        (Column1), 
        (Column2) 
     ); 


Examples of GROUPING SETS

The examples in this section provide you with an idea of some of the options available when defining GROUPING SETS. I have taken different examples here to explain different scenarios.


Using GROUP BY with GROUPING SETS

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS
    (T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

RESULT:


Using GROUPING SETS with composite elements

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

RESULT:


Using GROUP BY with multiple GROUPING SETS

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID,T.[Group])
    ,(H.SalesPersonID)
    ,())
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

RESULT:


Using GROUPING SETS with a ROLLUP of part of the GROUP BY list

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
   ,ROLLUP(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

RESULT:


Using GROUPING SETS with a CUBE of part of the GROUP BY list

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
    ,CUBE(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

RESULT:


GROUPING SETS Equivalents

A GROUP BY clause that uses GROUPING SETS can generate a result set equvalent to that generated by a UNION ALL of multiple simple GROUP BY clauses. GROUPING SETS can generate a result equivalent to that generated by a simple GROUP BY, ROLLUP or CUBE operation. Different combinations of GROUPING SETS, ROLLUP, or CUBE can generate equivalent result sets.


GROUPING SETS Equivalents of a Simple GROUP BY
GROUP BY GROUPING SETS ( (C1, C2, ..., Cn) )
GROUP BY C1, C2, ..., Cn
GROUPING SETS Equivalent of UNION ALL
SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))
SELECT customer, NULL as year, SUM(sales)
FROM T 
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T 
GROUP BY year
GROUPING SETS ROLLUP Equivalents
GROUP BY ROLLUP (C1, C2, …, Cn-1, Cn)
GROUP BY GROUPING SETS ( (C1, C2, …, Cn-1, Cn)
    ,(C1, C2, ..., Cn-1)
    ...
    ,(C1, C2)
    ,(C1)
    ,() )
GROUPING SETS CUBE Equivalents
GROUP BY CUBE (C1, C2, C3)
GROUP BY GROUPING SETS ( (C1, C2, C3)
    ,(C1, C2)
    ,(C1, C3)
    ,(C2, C3)
    ,(C1)
    ,(C2)
    ,(C3)
    ,() )

References

MSDN - SQL Server 2008

Tags: , , , ,

SQL Server

Data Type Synonyms

Introduction

This article basically illustrates the concept of Data type synonyms which are included in SQL Server 2005 for SQL-92 compatibility.

What are Data Type Synonyms?

Data type synonyms can be used instead of the corresponding base data type name in data definition language (DDL) statements, such as CREATE TABLE, CREATE PROCEDURE, or DECLARE @variable. However, after the object is created, the synonyms have no visibility. When the object is created, the object is assigned the base data type that is associated with the synonym. There is no record that the synonym was specified in the statement that created the object.
All objects that are derived from the original object, such as result set columns or expressions, are assigned the base data type. All subsequent metadata functions performed on the original object and any derived objects will report the base data type, not the synonym. This behavior occurs with metadata operations, such as sp_help and other system stored procedures, the information schema views, or the various data access API metadata operations that report the data types of table or result set columns.

List of Data Type Synonyms  

Synonym SQL Server system data type
Binary varying varbinary
char varying  varchar
character char
character char(1)
character(n) char(n)
character varying(n) varchar(n)
Dec decimal
Double precision float
float[(n)] for n = 1-7 real
float[(n)] for n = 8-15 float
integer int
national character( n ) nchar(n)
national char( n ) nchar(n)
national character varying( n ) nvarchar(n)
national char varying( n ) nvarchar(n)
national text ntext
rowversion timestamp

How to use Data Type Synonyms 

For example, you can create a table by specifying Data Type Synonyms:

 	CREATE TABLE Student(
	ID integer PRIMARY KEY,
	FirstName character varying(20),
	LastName national character varying(20),
	Marks Double precision)

FirstName is actually assigned an varchar(20) data type, and all subsequent metadata functions will report the column as an varchar(20) column. The metadata functions will never report them as a character varying(20) column. Similarly, LastName is an nvarchar(20) data type column and Makes is a float column.

Conclusion

This is an introduction to the Data Type Synonyms in SQL Server. The idea is to get familiar with another way of writing DDL queries using Data Type Synonyms.

Reference

MSDN

Tags:

SQL Server