SQL Server Denali CTP3 adds seve
SQL Server 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. You can
download the source code for the sample database
here.
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 entitled:
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 1
Error 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 28th,
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,
empid
FROM
Sales.Orders
WHERE
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,
empid
FROM
Sales.Orders
WHERE
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
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 location
FROM
Sales.Customers
WHERE
custid > 85;
custid
city region country location
-----------
--------------- --------------- --------------- ---------------------
86
Stuttgart NULL Germany Stuttgart, Germany
87
Oulu NULL Finland Oulu, Finland
88
Resende SP Brazil Resende, SP, Brazil
89
Seattle WA USA Seattle, WA, USA
90
Helsinki NULL Finland Helsinki, Finland
91
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,
productname
FROM
Production.Products;
strproductid
productname
------------- --------------
0000000058 Product ACRVI
0000000009 Product AOZBW
0000000051 Product APITJ
0000000045 Product AQOKR
0000000033 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