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 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
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
This article we will focus on the T-SQL Enhancements in SQL Server 2011.
The main new TSQL features in SQL Server 2012 are:
- WITH RESULT SETS
- OFFSET AND FETCH
- THROW in Error handling
- SEQUENCE
WITH RESULT SETS
This is a good feature provided with the execution of a stored procedure.
Legacy method
In earlier versions of SQL server when we wished to change a column
name or datatype in the resultset of a stored procedure, all the references
needed to be changed. There was no simple way to dump the output of a stored
procedure without worrying about the column names and data types.
2012 Method
With SQL Server 2012, the new WithResultsSet feature avoids the requirement
to change the stored procedure in order to change the column names in a
resultset.
For example :
CREATE PROCEDURE Denali_WithResultSet
AS
BEGIN
SELECT 1 as No,’Tsql’ Type,
‘WithResultSet’ AS Feature UNION ALL
SELECT 2 as No,’Tsql’ Type, ‘Throw’ AS
Feature UNION ALL
SELECT 3 as No,’Tsql’ Type, ‘Offset’ AS
Feature UNION ALL
SELECT 4 as No,’Tsql’ Type, ‘Sequence’
AS Feature
END
GO
EXEC Denali_WithResultSet
WITH RESULT SETS
(
( No int,
FeatureType varchar(50),
FeatureName varchar(50)
)
)
The WithResultsSet option after the Exec statement conatins the resultset in
(…) brackets. Here, we can change the column name and datatype according to our
needs, independent of what is column name returned in the resultset. In
the above example ‘Type’ is changed to ‘FeatureType’ and ‘Feature’ is changed
to ‘FeatureName’. This can be helpful for using an appropriate datatype while
showing the resultset.
This feature will be especially helpful when executing a stored procedure in
SSIS tasks.
While executing any stored procedure in OLEDB
Source, it will be now possible to execute the procedure with the required
column names and datatypes.
Continues…
