SQL Server 2012 T-SQL at a Glance: New and Enhanced Functions

SQL Server 2012 (formerly code-named Denali) CTP3 adds several new functions that will make our lives a little bit easier in several areas: conversion and parsing, date and time, logical, string manipulation and math. Some of the functions are objectively very important and convenient to have, whereas others are important because they enable easier migration from other platforms, e.g., Microsoft Access. I’ll describe the new functions according to their categories.

For sample data I used a database called TSQL2012.

Conversion and Parsing

New functions in this category: TRY_CONVERT, PARSE and TRY_PARSE.

TRY_CONVERT

The TRY_CONVERT function is one that many developers have been hoping to get for a long time. It works pretty much like the existing CONVERT function, only when the input value isn’t convertible, instead of generating an error, the function returns a NULL. Here’s an example, followed by its output:

SELECT TRY_CONVERT(INT, 100) AS try1, TRY_CONVERT(INT, 'abc') AS try2; try1        try2----------- -----------100         NULL

Like the CONVERT function, also TRY_CONVERT supports a third style argument where relevant.

One classic case where this function can be useful is in dynamic schema scenarios. Each row represents a single entity, attribute, value (EAV), where the values are stored as character strings. But even though the values are all stored in a character string column, each attribute conceptually can have a different type (number, data, etc.). You have the conceptual type of the value stored in its own column (call it thetype). Suppose you attempt to filter only attributes representing integers, convert to an INT type, and then do something with the result. So your filter looks something like this:

WHERE thetype = 'INT' AND CAST(val AS INT) > 10

I get into the details of why this form can actually fail in SQL Server in the second part of a two part series in my column titled T-SQL String Manipulation Tips and Techniques. For the purposes of this blog, suffice to say that this form can fail on a conversion error because SQL Server may actually attempt to handle the conversion before evaluating the left expression. With TRY_CONVERT you can easily avoid such failures using the following form:

WHERE thetype = 'INT' AND TRY_CONVERT(INT< val) > 10

Would be nice if SQL Server added in the future also a TRY_CAST function where the style isn’t relevant; perhaps even a more general TRY_THIS function that would work with any expression that generates a trappable error. For example, why not allow TRY_THIS(col1 / col2), and in case of an error like divide-by-zero simply return a NULL?

PARSE

The PARSE function in essence does a conversion of an input string to the target type, but unlike CAST and CONVERT, it supports an optional USING clause indicating the culture. The culture is any valid culture supported by the .NET framework. If a culture isn’t specified, SQL Server will rely on the current session’s effective language. One of the benefits in this function when an explicit culture is used is that it allows you to phrase the values in a form that is based on your culture’s conventions, without worrying about the language of the user running your code.

Here’s an example parsing strings as dates, in one case using US English culture, and in the other, Japanese:

SELECT PARSE('7/17/2011' AS DATE USING 'en-US') AS dt1, PARSE('2011/7/17' AS DATE USING 'ja-JP') AS dt2;

dt1        dt2---------- ----------2011-07-17 2011-07-17

TRY_PARSE

The TRY_PARSE to PARSE is like TRY_CONVERT is to CONVERT; namely, TRY_PARSE does the same as PARSE, only when the input isn’t converted to the target type, instead of generating an error, the function returns a NULL. For example, the following invocation of PARSE fails:

SELECT PARSE('7/17/11' AS DATE USING 'ja-JP') AS dt;Msg 9819, Level 16, State 1, Line 1Error converting string value '7/17/11' into data type date using culture 'ja-JP'.

A similar attempt with TRY_PARSE returns a NULL:

SELECT TRY_PARSE('7/17/11' AS DATE USING 'ja-JP') AS dt;dt----------NULL

Date and Time

New functions in this category: EOMONTH,  DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS and TIMEFROMPARTS.

EOMONTH

The EOMONTH function returns the end of month date corresponding to the input date and time value, with the time set to midnight, retaining the time zone if it exists. Here’s an example I ran on September 28^th, 2011, invoking EOMONTH with SYSDATETIME as input:

SELECT EOMONTH(SYSDATETIME()) AS endofmonth;endofmonth----------------------2011-09-30 00:00:00.00

If the input type is one of the supported date and time datatypes, the type of the output is that of the input. Otherwise, as long as the input is convertible to a date and time type, the type of the output is DATETIME2(7).

It’s important to note is that the function doesn’t return the last possible point in time for the respective month based on the type of the input, rather midnight of the last day of the month. As long as the values stored in the data have only the date, or use only midnight, it is actually quite convenient to use this function. As an example, the following query returns orders placed on the last day of the month:

SELECT orderid, orderdate, custid, empidFROM Sales.OrdersWHERE orderdate = EOMONTH(orderdate);

Here I’m relying on the fact that all order dates are stored with midnight in the time. If that’s not a guarantee, and the time can be other than midnight, to address the task correctly you would need to express the filter as a range, like so:

SELECT orderid, orderdate, custid, empidFROM Sales.OrdersWHERE orderdate >= EOMONTH(orderdate)  AND orderdate < DATEADD(day, 1, EOMONTH(orderdate));

Surprisingly, SQL Server Denali CTP3 doesn’t also support functions for end of other periods (e.g., quarter, year), or the beginning of any period. For now, you have to roll your own. This reminds me of an amusing, though very practical, suggestion, by my friend and colleague Gianluca Hotz when he learned that there’s no support for other similar functions. He suggested creating a function called ENDOFTIME that will accept the period (e.g., MONTH, QUARTER, YEAR) as another input, and return the end of that period. Similarly a function called BEGINNINGOFTIME or STARTOFTIME could accept a value and a period, and return the beginning of that period.

%FROMPARTS

For each date and time data type, SQL Server Denali provides a FROMPARTS function that allows constructing a value of this type from integer parts. This is useful in general, but also important for migrations from environments like Excel, Access and others that support such functionality.

Here’s sample code constructing a value of each of the date and time types:

SELECT  DATEFROMPARTS(2012, 02, 12)    AS DATE_FROMPARTS,  DATETIME2FROMPARTS(2012, 02, 12, 13, 30, 5, 1, 7)    AS DATETIME2_FROMPARTS,  DATETIMEFROMPARTS(2012, 02, 12, 13, 30, 5, 997)    AS DATETIME_FROMPARTS,  DATETIMEOFFSETFROMPARTS(2012, 02, 12, 13, 30, 5, 1, -8, 0, 7)    AS DATETIMEOFFSET_FROMPARTS,  SMALLDATETIMEFROMPARTS(2012, 02, 12, 13, 30)    AS SMALLDATETIME_FROMPARTS,  TIMEFROMPARTS(13, 30, 5, 1, 7)    AS TIME_FROMPARTS; DATE_FROMPARTS DATETIME2_FROMPARTS    DATETIME_FROMPARTS     -------------- ---------------------- -----------------------2012-02-12     2012-02-12 13:30:05.00 2012-02-12 13:30:05.997 DATETIMEOFFSET_FROMPARTS           SMALLDATETIME_FROMPARTS TIME_FROMPARTS---------------------------------- ----------------------- ----------------2012-02-12 13:30:05.0000001 -08:00 2012-02-12 13:30:00     13:30:05.0000001

DATE_FROMPARTS DATETIME2_FROMPARTS    DATETIME_FROMPARTS     

-------------- ---------------------- -----------------------

2012-02-12     2012-02-12 13:30:05.00 2012-02-12 13:30:05.997

DATETIMEOFFSET_FROMPARTS           SMALLDATETIME_FROMPARTS TIME_FROMPARTS

---------------------------------- ----------------------- ----------------

2012-02-12 13:30:05.0000001 -08:00 2012-02-12 13:30:00     13:30:05.0000001

Logical

Functions in this category: CHOOSE and IIF.

The IIF and CHOOSE functions are another pair of functions that Denali supports to allow easier migration from environments running Access.

CHOOSE

The CHOOSE function accepts an integer input followed by a list of values of any data type, and returns as output the value from the list in the position indicated by the first input. Here’s an example for using the function:

SELECT CHOOSE(1, 'a', 'b', 'c') AS first, CHOOSE(2, 'a', 'b', 'c') AS second; first second----- ------a     b

Of course you can implement similar logic very easily using the standard CASE expression, but as mentioned, the point is making migrations from Access smoother.

IIF

The IIF function is another function Access supports. It accepts as first input a predicate, as second input an expression to return in case the predicate is true, and as third input an expression to return in case the input is false or unknown. Here’s an example for using the function:

SELECT IIF(1 = 2, 'a', 'b') AS iif_result; iif_result----------b

Just like with CHOOSE, it’s very simple to implement the IIF logic with a standard CASE expression, but the point was making Access migrations easier.

String Manipulation

New functions in this category: CONCAT and FORMAT.

CONCAT

The CONCAT function concatenates the input values into a single result string. If you’re wondering what’s the reason for adding such a function when T-SQL already has a concatenation operator (+), there are two main reasons:

1. The concatenation operator + yields a NULL on NULL input. The CONCAT function converts NULL inputs to empty strings before concatenation. Of course you can get by using the COLAESCE function, replacing a NULL input with an empty string, but this makes the code messy.

2. Other platforms support the CONCAT function, and adding such support in SQL Server makes migration from those platforms easier.

As an example, consider the location attributes country, region and city, of customers. The region attribute simply isn’t applicable in some locations, in which case it is set to NULL. You want to generate a single string of all three location attributes, separating the applicable ones by commas. With the CONCAT function it’s very simple:

SELECT custid, city, region, country,  CONCAT(city, ', ' + region, ', ' + country) AS locationFROM Sales.CustomersWHERE custid > 85;

custid      city            region          country         location----------- --------------- --------------- --------------- ---------------------86          Stuttgart       NULL            Germany         Stuttgart, Germany87          Oulu            NULL            Finland         Oulu, Finland88          Resende         SP              Brazil          Resende, SP, Brazil89          Seattle         WA              USA             Seattle, WA, USA90          Helsinki        NULL            Finland         Helsinki, Finland91          Warszawa        NULL            Poland          Warszawa, Poland

Notice that when the region attribute was applicable it was made part of the result string, and when it wasn’t, the NULL was simply replaced with an empty string.

FORMAT

The FORMAT function allows you to format an input value to a character string based on a .NET format string. You can optionally indicate a culture when relevant. As an example, the following code formats the result of the GETDATE() function using the format string 'd' (meaning, short date pattern), in one case using US English culture, and in another Japanese:

SELECT FORMAT(GETDATE(), 'd', 'en-US') AS us, FORMAT(GETDATE(), 'd', 'ja-JP') AS jp

 us          jp----------- ------------9/28/2011   2011/09/28

This function allows a lot of flexibility in formatting inputs. For example, the following code formats product IDs as 10-digit strings with leading zeros:

SELECT FORMAT(productid, '0000000000') AS strproductid, productnameFROM Production.Products;

 strproductid  productname------------- --------------0000000058    Product ACRVI0000000009    Product AOZBW0000000051    Product APITJ0000000045    Product AQOKR0000000033    Product ASTMN...

Note, though, that the function relies on .NET for the purposes of formatting, which has overhead. Compared, for example, with functions like STR, the FORMAT function is much slower.

Math

Enhanced function in this category: LOG.

LOG

So far SQL Server supported two functions that compute logarithms: LOG (for natural logarithm) and LOG10 (for logarithm with a base of 10). If you wanted to compute a logarithm with any other base, you had to do it mathematically, using a log with a supported base, dividing the log of the input value by the log of the input base. For example, to compute the logarithm of 256 using the base 2, you could use the following expression:

SELECT LOG(256) / LOG(2);

SQL Server Denali enhances the LOG function by supporting a second argument representing the base. So to achieve the same thing in Denali, you simply use the following expression:

SELECT LOG(256, 2);

Now life is good; of course, it could be made event better with support for TRY_THIS, ENDOFTIME and BEGINNINGOFTIME. :)

Cheers,

BG