Tag Archives: Microsoft SQL Server

PERCENTILE_DISC function in SQL Server 2012

PERCENTILE_DISC is one the new analytical\statistical functions that we have in SQL Server 2012. In this article we will see how we can use this function. To begin with let us see the syntax of this function as stated in BOL.

Syntax of PERCENT_DISC

PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP
( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition_by_clause> ] )

Now let us implement PERCENT_DISC and understand how it works.We will be using the below script to create a database and insert some values in it before we call this function.

USE [Master]
GO
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'PERCENTRANK')
BEGIN
DROP DATABASE PERCENTRANK;
END
GO
CREATE DATABASE PERCENTRANK;
GO
USE PERCENTRANK
GO
CREATE TABLE PERCENTRANKDEMO
(
[ProductId] int,
[SalesQty] int,
[Year] int
);
insert into PERCENTRANKDEMO
VALUES
(10,1000,2005),(20,1215,2005),(30,3327,2005),
(10,2000,2006),(20,2415,2006),(30,3429,2006),
(10,3050,2007),(20,3216,2007),(30,3737,2007),
(10,3026,2008),(20,1618,2008),(30,3452,2008),
(10,2004,2009),(20,4315,2009),(30,5435,2009),
(10,3040,2010),(20,4015,2010),(30,4343,2010),
(10,3060,2011),(20,4345,2011),(30,3237,2011),
(10,3075,2012),(20,4035,2012),(30,7450,2012),
(10,3055,2013),(20,5012,2013),(30,3321,2013),
(10,3059,2014),(20,6019,2014),(30,3245,2014),
(10,3150,2015),(20,6815,2015),(30,4000,2015);

Noe let’s use the below code to implement this function. With this code we are trying to find the 50th percentile from table we created.

USE PERCENTRANK
GO
select ProductId, SalesQty, Year,
PERCENTILE_DISC(.5)
WITHIN GROUP(ORDER BY SalesQty)
OVER(PARTITION BY ProductID) as [50Percentile]
from PERCENTRANKDEMO
WHERE ProductId = 20
order by ProductId;

Snapshot of the output of the above code
Perc1So what is the inference that we draw from the output. The function PERCENTILE_DISC calculates the Nth percentile based on a discrete distribution of the column values. The result set would have the Nth percentile value which would equal to a specific value in the column. Any NULLS would be ignored.

FIRST_VALUE – New analytical function in SQL Server 2012

In this article we will be taking a look at the new analytical function FIRST_VALUE  in SQL Server 2012. Let us understand them with examples.

This function returns the first value from an ordered set of values. The syntax for the same is

FIRST_VALUE ( [scalar_expression ] )
OVER ( [ partition_by_clause ]
order_by_clause [ rows_range_clause ] )

Let us create a sample table and understand this on SQL Server 2012. The following script does the same. Lets observe the output of the script below

CREATE TABLE [dbo].[MyFirstLastVaL](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NOT NULL,
[MarksInMaths] [int] NOT NULL )
GO
insert into MyFirstLastVaL values('Alan',99)
insert into MyFirstLastVaL values('Brent',29)
insert into MyFirstLastVaL values('Charlie',25)
insert into MyFirstLastVaL values('David',37)
insert into MyFirstLastVaL values('Ello',15)
insert into MyFirstLastVaL values('Frank',59)
insert into MyFirstLastVaL values('Grant',16)
insert into MyFirstLastVaL values('Honey',18)
insert into MyFirstLastVaL values('Irene',86)
insert into MyFirstLastVaL values('Jack',76)
insert into MyFirstLastVaL values('Kalou',66)
insert into MyFirstLastVaL values('Lisa',68)
insert into MyFirstLastVaL values('Mike',98)
insert into MyFirstLastVaL values('Lisa',28)
insert into MyFirstLastVaL values('Mike',45)
insert into MyFirstLastVaL values('Alan',19)
insert into MyFirstLastVaL values('Brent',92)
insert into MyFirstLastVaL values('Charlie',35)
insert into MyFirstLastVaL values('Irene',36)
insert into MyFirstLastVaL values('Jack',67)
insert into MyFirstLastVaL values('Kalou',96)

SELECT id,name,MarksInMaths,
FIRST_VALUE(marksinmaths) OVER (ORDER BY marksinmaths) MyFirstVal
FROM MyFirstLastVaL

fvl1Now let us use the function with partition by clause and observe the output

SELECT id,name,MarksInMaths,FIRST_VALUE(MarksInMaths)
OVER (PARTITION BY Name ORDER BY MarksInMaths) MyFirstVal
FROM MyFirstLastVaL

fvl2In the first case the query simply returns the first value of the entire data set whereas in the second case the output is based on the marksinmaths column, so the FIRST_VALUE is different but the same for each partition.

EOMONTH() – End of the month – SQL Server 2012

So how do we calculate the last date of the month which is 4 months from now?

SQL Server 2012 presents EOMONTH() function. Let us understand this function with an example.

DECLARE @MyDate datetime
SET @MyDate = GETDATE()
SELECT EOMONTH (@MyDate,4)
SELECT EOMONTH (@MyDate,-4)
as LastDayOfTheMonth

eom2
So what do we find from the 2 outputs. Well it gives me the last date of the 4th month from the current month i.e. 4 months from now would be April and the last date of April is 30th. The second output gives the last date of 4 months previous i.e August. This function would be very handy in calculations where the last date of the month is a critical factor. Previously to achieve this we would have to do a bit of manipulation.

Pagination – Using OFFSET and FETCH in SQL Server 2012

OFFSET and FETCH are two new clauses introduced in SQL Server 2012 that allows us to extract a portion of rows from the result set. When we need to display a large result set to the user, the best way of going about it is to split them .i.e use pagination. In SQL Server 2012, we can achieve pagination by using the ‘OFFSET’ and ‘FETCH’ commands. Let us understand this with an example:

USE AdventureWorks2012
GO
SELECT pp.ProductId,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.productid

OandF1
The query returns 504 rows as depicted above. So what do i do if i need to fetch only a portion of the above rows?

Enter OFFSET and FETCH. How? let us understand this with an example

USE AdventureWorks2012
GO
SELECT PP.ProductID,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.ProductID
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY

OandF2Here, the OFFSET tells the query to ignore the first 100 rows and then return only the following 10 rows. This is very easy to use and a quick way to return just a portion of records.
Few Limitations of the OFFSET and FETCH clause as stated in msdn:
a) ORDER BY is mandatory to use OFFSET and FETCH clause.
b) OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
c) TOP cannot be combined with OFFSET and FETCH in the same query expression.
d) The OFFSET/FETCH row count expression can be any arithmetic, constant, or parameter expression that will return an integer value. The row count expression does not support scalar sub-queries.

TRY_PARSE – Conversion function in SQL Server 2012

In this article I will discuss about the TRY_PARSE function in SQL Server 2012. Conversion functions helps us avoid errors when dealing with different data types. Let us understand TRY_PARSE with an example.

SELECT TRY_PARSE ('12-18-2013' AS datetime) AS Alpha
SELECT TRY_PARSE ('2013' AS decimal) AS Beta
SELECT TRY_PARSE ('2013.00' AS decimal) AS Gamma
SELECT TRY_PARSE ('2013.0000' AS float) AS Theta
SELECT TRY_PARSE ('Arsenal' AS float) AS Delta

The TRY_PARSE() function can convert any string value to a Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result to a NULL.

Lets have a look at the output of the above
TP2In the above case it would not convert ‘Arsenal’ to a date time or Numeric value and hence it results in NULL output. TRY_PARSE function is not a native SQL SERVER function, instead it is a CLR dependent function.

IIF – The InLine conditional Statement in SQL Server 2012

IIF is a new inline conditional statement in SQL Server. We can pass an expression that can be evaluated to either true or false to the function and it returns one value for true and another one for false.

Let us understand this with an example:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Arsenal'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

The output of the above as below. In this we have passed two different values.
IIF1Now let us pass two same values and see how this works:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Manchester United'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

IIF2
IIF is very useful where a straightforward comparison has to be made without writing case statements.

How to use ‘WITH RESULT SETS’ in SQL Server 2012

In SQL Server 2012, the execute statement has been enhanced with an option called ‘WITH RESULT SETS‘. So let us understand what this does.

This option enables us to change the column names and data types of the returning result set from the stored procedure. Let us understand this with an example.

Create a new stored procedure

USE AdventureWorks2012
go
CREATE PROC Usp_products
AS
SELECT ProductID,Name,ProductNumber FROM Production.Product
ORDER BY ProductID

Let us now execute the above procedure and see the outcome
withresultset1Now let us use this new option and change the name of the columns and their data types while the procedure returns the output at run time.

EXEC Usp_products
WITH RESULT SETS
(
(
SerialNumber varchar(15),
Name1 varchar(30),
ProductNumber1 varchar(30)
)
)

withresultset2So this is how we can use this option to tweak the output set to different data types and column names from an stored procedure.

%d bloggers like this: