T-SQL received a lot of improvements in SQL Server 2012 including support
for the ANSI FIRST_VALUE and LAST_VALUE functions, declarative data paging
using FETCH and OFFSET, and .NET’s parsing and formatting functions.
Fetch
and Offset
Currently SQL Server developers wishing to implement server-side paging
tend to use imperative techniques such as loading a result set into a temp
table, numbering the rows, and then selecting out the range they were actually
interested in. Others use the more modern ROW_NUMBER and OVER pattern,
while some stick to cursors. These techniques are not hard, but they can be
time consuming and error prone. Moreover they are inconsistent, as each
developer has their own favorite implementation.
SQL Server 2012 addresses this by adding true declarative support for
data paging. T do this, developers have been given the ability to add an OFFSET
and FETCH NEXT option to T-SQL’s ORDER BY clause. Currently these are not
performance optimizations; SQL Server is still doing what you would have done
manually.
Over
Clause Windowing
Sometime developers need to write queries based on the difference between
rows. For example, you may be interested in the amount of time that has passed
between the current and previous row’s timestamp. This is easy to do with
cursors, but that is largely frowned upon for both stylistic and performance
reasons. One can also use a sub query that is executed row by row, but that can
be an incredibly expensive way to get the results. Finally one can just punt
the issue to the client, though that only works if the client is a programming
language and not just a reporting tool.
Now you can directly access the previous row using the LAG function.
Since you are explicitly declaring that’s what you are trying to accomplish,
the query analyzer will retain that previous row in memory so that a sub-query
is not needed, which in turn should result a profound performance boost. While
LAG defaults to the previous row, an offset can be provided if you need to
reach back further.
LAG, and its twin LEAD, are part of the ANSI standard and this is a feature developers
have been asking for since Microsoft partially implemented the OVER clause
in SQL Server 2005.
FIRST_VALUE and LAST_VALUE are also supported in this release.
Reflection
Previously developers wanting to determine what a query or stored
procedure is going to return used the SET FMTONLY command. This
allowed them to preview the columns coming back without actually executing the
query. Unfortunately the information returned is limited to just the column
definitions that you would have gotten back if you had executed the query.
With the new sp_describe_first_result_set procedure developers get detailed information
on the first result set the query or stored procedure will return. Information
includes data types and scales, source tables/columns, whether the column can
be updated or is computed, and a wealth of other information. This is also
available as the dynamic management views sys.dm_exec_describe_first_result and sys.dm_exec_describe_first_result_set_for_object.
Defensive
Coding
Traditionally developers were at the mercy of their colleagues when it
comes to calling stored procedures. With no compile time guarantees as to what
would be returned, accidental breaking changes are a huge concern. While T-SQL
doesn’t offer anything to prevent these mistakes, it can minimize them with the
RESULT SETS option.
When specified, the RESULT SETS option allows developers to
require the stored procedure return a specific data structure. If the result
sets from the procedure differ in any way from what was requested, the batch
terminates with an error. Since this is a run time error, we recommend
developers using this option have a full set of unit tests to ensure the error
is triggered before the code hits production.
Error
Handling
T-SQL has had support for TRY-CATCH since 2005, but strangely THROW was
missing until now. Without arguments, THROW works like it does in C# or VB inside a catch
block. That is to say, it re-throws an exception without losing any of the
information captured at the time. This is useful for logging or adding items to
a re-try queue while still informing the application that something went wrong.
When used with arguments, THROW is similar to RAISERROR except that it supports
error numbers not in sys.messages and the severity is always 16. Also unlike RAISERROR, uncaught
THROW errors are always batch terminating.
Parsing
and Conversions
T-SQL now supports a PARSE function the includes the option to specify a
culture. The culture must be one supported by the .NET framework, suggesting
how its implemented, and if available in a TRY_PARSE version as well.
Likewise, there is a new TRY_CONVERT function. Both this and the try
parse function return a null if the conversion fails.
Going the other direction, the FORMAT function uses the .NET formatting
strings. This is slower that native functions such as STR, but more
flexible.
Date/Time
Functions
While still woefully inadequate, T-SQL is slightly better at date/time
processing. The EOMONTH function returns the last day of the month, a useful
feature for reporting. The xxxFROMPARTS set of functions allow one to construct
dates and times using a set of parameters instead of a single string. This
includes support for the data types Date, DateTime, DateTime2, DateTimeOffset,
SmallDate, and Time.
Misc.
Functions
The Choose function from Access and Visual Basic has made its
way into T-SQL. Under some circumstances this can be used as a less verbose
version of CASE. Another function cribbed from those languages is IIF.
CONCAT can be used for string concatenation. Besides making it easier to
port code from other database languages, this has different null handling that
the + operator.
