New T-SQL Features in SQL Server 2012

CUME_DIST / PERCENT_RANK

CUME_DIST and PERCENT_RANK can be used to calculate the relative rank of a row within a group of rows in SQL Server 2012.

 Use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition.

PERCENT_RANK = (RANK() – 1) /
(TotalRows – 1)

CUME_DIST calculates the relative position of a specified value in a group of values.

The below example displays the Rank, PERCENT_RANK and CUME_DIST

SELECT SalesOrderID,
 RANK() OVER(ORDER BY SalesOrderID) RANK,
 PERCENT_RANK() OVER(ORDER BY SalesOrderID) AS PERCENT_RANK,
 CUME_DIST() OVER(ORDER BY SalesOrderID) AS CUME_DIST
 FROM Sales.SalesOrderDetail
 WHERE SalesOrderID IN (75121,75122,75123 )

The result for the above query is shown below:

IIF

I’m not sure why the IIF function which is a very simple function has not been included in previous versions of SQL Server. IIF is similar to the CASE function but it is optimized for evaluating two values. For example:

SELECT PersonType,
IIF(PersonType = 'EM','Employee','Other') PersonType
FROM Person.Person 

The above query will return an Employee for the rows where PersonType equals EM and Other if not.

CHOOSE

This new simple feature is useful for extracting data elements from a data array:

DECLARE @MonthNumber int = 5
SELECT CHOOSE(@MonthNumber, 'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC') AS Month

The above will return the 5th element from the above array which is MAY.

TRY_CONVERT

The

CONVERT function was used for earlier versions of SQL Server.

Let us say, we have a table with dates.

INSERT INTO MyData
(MyDate)
VALUES
('2011-01-01'),
('2012-12-31'),
('2012-02-29'),
('2011-02-29')

Note that 2011-02-29 is an invalid date. You could use CONVERT function to convert varchar to the date data type:

SELECT CONVERT(date,MyDate) Mydate
FROM MyData

However, will return an error since you have an error in the last row.  In SQL Server 2012, you can use the new feature, TRY_CONVERT.

SELECT TRY_CONVERT(date,MyDate) Mydate

FROM MyData

TRY_CONVERT will return all the correct rows and invalid row will return null value. Main important thing is this won’t trigger an exception which will fail.

Apart from this you can use TRY_CONVERT function to retrieve only the error records.

SELECT MyDate
FROM MyData
WHERE TRY_CONVERT(date,Mydate) IS NULL
Instead of throwing an error TRY_CONVERT returns a NULL value and you can then more easily handle this in your code.

CONCAT

CONCAT is a significant addition for DQL Server developers.

If you have table with Title, FirstName, MiddleName, LastName  columns and you may need to combine these columns and display them as a single column as shown below.

SELECT Title,FirstName,MiddleName, LastName ,
Title + ' '+ FirstName+ ' '+MiddleName+ ' '+LastName FullName
FROM Person.Person

Results are shown below.

However, except for three records and all the rows are null. That is because when a NULL value is added to a value the result is NULL.

The solution is to utilize the ISNULL function.

SELECT Title,FirstName,MiddleName, LastName ,
ISNULL(Title,'') + ' '+ FirstName+ ' '+ ISNULL(MiddleName, '' )+ ' '+LastName FullName
FROM Person.Person

However, this might be a tedious task if you have numerous columns to combine. In SQL Server 2012, you have a new function CONCAT which returns a string that is the result of concatenating two or more string values.

SELECT Title,FirstName,MiddleName, LastName ,
CONCAT(Title, ' ',FirstName, ' ',MiddleName, ' ',LastName) FullName
FROM Person.Person

From the below results you can see the null issue is eliminated in the FullName column.

Note that the CONCAT function has no performance impact compared with the old-style syntax.

PARSE & TRY_PARSE

PARSE is somewhat similar to CONVERT in that it can convert one value to another specified value, however it should only be used for converting from a string to a date/time or number type. Note that since this is a CLR function it may have some performance impact.

SELECT PARSE('11/28/2011' AS datetime)
GO
SELECT PARSE('Monday, 28 November 2011' AS datetime USING 'en-US')
GO

As with TRY_CONVERT there is a function called TRY_PARSE to facilitate errors – ie it returns a NULL instead of throwing an error.

Date Time Functions

There are quite few date time functions introduced as shown in the below code. All the below functions will take parameters and generate datetime values from (except for DATEPART which outputs a part of a date as an integer).

SELECT
  DATEFROMPARTS(2012, 01, 31)   AS DATE_FROM_PARTS,
  DATETIME2FROMPARTS(2012, 01, 31, 15, 24, 5, 2, 7)  AS DATETIME2_FROM_PARTS,
  DATETIMEFROMPARTS(2012, 01, 31, 15, 30, 5, 997)   AS DATETIME_FROM_PARTS,
  DATETIMEOFFSETFROMPARTS(2012, 01, 31, 15, 30, 5, 1, -8, 0, 7)   AS DATETIMEOFFSET_FROM_PARTS,
  SMALLDATETIMEFROMPARTS(2012, 01, 31, 15, 30)   AS SMALLDATETIME_FROM_PARTS,
  TIMEFROMPARTS(15, 30, 5, 1, 7)    AS TIME_FROM_PARTS

All above functions will add different date parts and outputs will be given as a datetime.

EOMonth

In many human resource applications, you are required to get last date of the month. In previous versions of SQL Server, you might need to use following syntax.

SELECT
CAST(DATEADD(d,-1,(DATEADD(mm,DATEDIFF(m,0,GETDATE())+1,0)))AS DATE)

In SQL Server 2012 you can simply use EOMONTH to perform this:

SELECT EOMONTH(GETDATE())

There will not be any performance gain or loss since this function and the old-style function will both have same performance impact.

Pages: 1 2




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |