Home > SQL Server 2012 > DENALI TSQL – New features | Built-in Functions

DENALI TSQL – New features | Built-in Functions


Here is a list of some new function introduced in SQL Server Denali, some examples are taken from MSDN and here is the link for more details.

Logical Functions:

-- CHOOSE()
SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result; -- Developer
SELECT CHOOSE ( 2, 'Manoj', 'Saurabh', 'Andy', 'Dave' ) AS Result; -- Saurabh
GO

-- IFF()
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result; -- TRUE
GO

Note: The IFF() function was important for the developers that migrated from ACCESS to SQL Server. Introduction of these 2 functions will make their life easy.

Conversion Functions:

-- PARSE()
SELECT PARSE ('Monday, 13 December 2010' AS datetime2, 'en-US') AS Result;
SELECT PARSE ('€345,98' AS money, 'de-DE') AS Result;
GO

-- TRY_CONVERT()
SELECT
CASE WHEN TRY_CONVERT (float,'test') IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
GO

-- TRY_PARSE()
SELECT
CASE WHEN TRY_PARSE ('Aragorn' AS decimal, 'sr-Latn-CS') IS NULL
THEN 'True'
ELSE 'False'
END
AS Result;
GO

Note: The TRY_CONVERT() & TRY_PARSE() functions added exception handling to the existing CONVERT() and new PARSE() function. Now developers can fully rely on these 2 functions while type converting values and defaulting them to something else on any execption.

Analytic Functions:

USE AdventureWorks2008R2;
GO

-- FIRST_VALUE() & LAST_VALUE()
SELECT Name, ListPrice, ProductSubcategoryID,
FIRST_VALUE (Name) OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice ASC) AS LeastExpensive,
LAST_VALUE (Name) OVER (PARTITION BY ProductSubcategoryID ORDER BY ListPrice ASC) AS LeastExpensive
FROM Production.Product;
GO

-- LAG()
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LAG (SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

-- LEAD()
SELECT TerritoryName, BusinessEntityID, SalesYTD,
LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;

Note: The LAG() and LEAD() functions are already in Oracle and by introducing them in MS SQL Server’s Denali version is very much welcomed by TSQL developers. Plus the addition of FIRST_VALUE(), LAST_VALUE(), PERCENTILE_XXX(), etc will really add value and provide zeal to programmers in TSQL development, just like Ranking functions and Cube/Rollup did in 2005(08).

Some other new functions introduced are:
– CUME_DIST()
– PERCENTILE_CONT()
– PERCENTILE_DISC()
– PERCENT_RANK()

More details here on MS BOL: http://technet.microsoft.com/en-us/library/hh213234%28SQL.110%29.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 405 other followers

%d bloggers like this: