You won’t find any .Net coding this month. Instead, I will introduce you to my favorite new features of Microsoft SQL Server 2012 from developer’s perspective (because this is what I am after all). There are way too many new features and enhancements to list here. I will also limit myself to talk about the new T-SQL features I prefer.

What you need?

To be able to test the queries you will find in this article (you can also download the file attached to this article), you will need an instance of Microsoft SQL Server 2012 and an access to SSMS (SQL Server Management Studio) 2012.

What’s new about Sequence?

One of the most announced new features of SQL 2012 is its new Sequence feature. This feature already exists on Oracle.

In short, it offers you a central Identity mechanism (instead of per table Identity mechanism). Because you can cache some values, it can perform better the old Identity feature.

Almost any numerical data types can be used with Sequence (tinyint, smallint, int, bigint, decimal, numeric) depending on the range of values you need.

You should have a look at http://msdn.microsoft.com/en-us/library/ff878091.aspx for all the details on the syntax of the Create Sequence feature.

For example, if you want to create a new Sequence in your database, the minimum query will look like this:

CREATE SEQUENCE MySequence

As shown in MSDN, there are much more options to this CREATE SEQUENCE but this is the least you need to do (give your sequence a name). By default, the data type used is a bigint and it starts with the minimum value the data type can contains (-9,223,372,036,854,775,808 for a bigint).

Now, every time you need a new value, you need to ask for one. You do it by calling NEXT VALUE FOR followed by the name of the Sequence. For example, the following query outputs the first 100 values:

SELECT NEXT VALUE FOR MySequence, GETDATE()
GO 100

When you create a new sequence, you can specify its data type, the start value, its increment. You can also specify the minimum and maximum values if you would like something different than what’s offered by the selected data type. You can also specify what happens when the limit is reached. By default, the Next Value statement returns an error. But you can ask the sequence object to cycle (restart with the minimum value).

You are not limited to a single Sequence object in your database.

In SSMS, you will find this object under Programmability and then Sequences.

Figure 1: Sequence object properties

You can also query sys.Sequences to find the existence/state of the sequence object defined in your database.

What’s new about paging?

A long time request to the SQL team is the ability to page the results of query. For example, you have a query returning a million rows but only want to retrieve the first 100, and a bit later, retrieve the next 100. All kind of methods were created through the years to circumvent this specific problem.

You will be happy that it is now built-in the SQL 2012 engine.

This is simply done by adding 2 new clauses at the end of your query:

SELECT * 
FROM dbo.Test
ORDER BY ID
OFFSET 48 rows
FETCH NEXT 12 ROWS ONLY

Notice that the ORDER BY clause is mandatory if you want to use the paging feature. OFFSET indicates the number of rows to skip or exclude from the top of resultset. The FETCH NEXT x ROWS ONLY tells how many rows you want to be returned once the top has been skipped (if you don’t specify the FETCH NEXT clause, the remaining rows after the OFFSET are returned).

What’s new about selection of data (Lag and Lead)?

Many new features fall under this category!

For example, can you tell what this query is supposed to return (more on DateFromParts later)?

SELECT SC.*, SP.Month AS 'Previous month', SP.Sales AS 'Previous Sales' 
FROM dbo.Sales AS SC
LEFT JOIN dbo.Sales AS SP
ON DATEFROMPARTS(SP.Year, SP.Month, 1) = DATEADD(MONTH, -1, DATEFROMPARTS(SC.Year, SC.Month, 1))

It is the kind of query you need to execute in order to find the values of the month previous to the current row processed by doing a JOIN. You have done it many times for sure. What if you also need the values of the following month? You would need another LEFT JOIN!

SQL 2012 now offers LAG and LEAD to handle that need. Look at this query:

SELECT Year, Month, Sales
, LAG(Sales, 1, null) OVER(ORDER BY Year, Month) as 'Previous Month Sales'
, LAG(Sales, 3, null) OVER(ORDER BY Year, Month) as 'Previous Quarter Sales'
, LEAD(Sales, 1, null) OVER(ORDER BY Year, Month) as 'Following Month Sales'
FROM Sales

Without any JOIN, I can retrieve the previous month, the previous quarter, and the following month sales.

Both LAG (for previous values) and LEAD (for future values), have 3 parameters:

• The name of the field to retrieve
• The offset (number of rows to skip)
• The default value to return in case no data is found

Also, both LAG and LEAD requires an OVER(ORDER BY ) clause to indicates what is considered to be the previous or future row related to the current row.

What’s new about selection of data (First_Value and Last_Value)?

Much like Lag and Lead, the SQL 2012 engine now supports First_Value and Last_Value to give you an easy access to the first and last value of a group.

Have a look at this query:

SELECT *
, FIRST_VALUE(Sales) OVER (PARTITION BY [Year] ORDER BY [Year], [Month]) AS FirstValue
FROM dbo.Sales
ORDER BY [Year], [Month]

Would you be able to guess that it will return the first Sales value of the current year when the result set is sorted by year and month? This is exactly what it does.

Can you guess what this query returns?

SELECT *
, LAST_VALUE(Sales) OVER  (PARTITION BY [Year] ORDER BY [Year], [Month]) AS LastValue
FROM dbo.Sales
ORDER BY [Year], [Month]

You would expect that Last_Value would work exactly like First_Value but it doesn’t. This exact query will return the current value instead of the last value of the current year. The reason is that we need to tell the engine to look further than the current row to find the last value. How to do that? Look at this query:

SELECT *
, LAST_VALUE(Sales) OVER  (PARTITION BY [Year] ORDER BY [Year], [Month] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LastValue
FROM dbo.Sales
ORDER BY [Year], [Month]

The “Rows between Current Row and Unbounded Following” is the answer. Why is it like that? I can’t say for sure but I would guess it is related to performance.

What’s new about selection of data (IIF and Choose)?

Every time you want to compare data against values to return a different one, you need to use a CASE statement. This very often leads to ugly/unreadable queries.

Look at this one for example which simply want to find if we are on even or odd rows:

SELECT *
, CASE WHEN (ID % 2) = 0 
    THEN 'even' 
    ELSE 'odd' 
END AS MyColumn 
FROM dbo.Sales

SQL 2012 now has the IIF function which can be used if the returned value is one of two:

SELECT *, IIF((ID % 2) = 0, 'even', 'odd') AS MyColumn 
FROM dbo.Sales

Much like the IIF, another new function has been implemented. This one, CHOOSE, will compare a numeric value against an array of values and returned the corresponding value. Look at this example:

SELECT *, CHOOSE([Month], 'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
FROM dbo.Sales

What’s new for formatting and concatenation?

Formatting data of a result set was another frequent request to the SQL team. They finally implemented the FORMAT function right into the SQL engine.

The Format function works like its .Net equivalent. It will format a data type against a pattern returning a string.

For example, you may want to format the date and time to a very specific format like this:

SELECT FORMAT(GETDATE(), 'yyyy.MM.dd HH:mm:ss')

Notice that you can also specify a culture when formatting. Look at this query which will return different currency symbol and different group separator):

DECLARE @Sales MONEY = 32182000.85
SELECT FORMAT(@Sales,'c','en-us')
    , FORMAT(@Sales,'c','fr-CA')
    , FORMAT(@Sales,'c','fr-FR')
    , FORMAT(@Sales,'c','ru-RU')

Because the Format function uses the .Net method, the CLR requires to be installed on the server (but does not require to be enabled). Because a CLR function is called, this Format function can slow down your query.

About string concatenation now, another new function has been implemented called CONCAT. Of course, you have always been able to add a + sign between 2 variables to concatenate them but they all needed to already be in a string format (other it fails). Also, if you happen to have a NULL value in one of your variables, the returned string concatenated with + would also be NULL.

CONCAT now fix these problems. It automatically converts any data types to string, any NULL to empty string before appending together up to 255 of them.

You can play with this query to quickly find the great value of the new CONCAT function:

Declare @a Varchar(100)='Microsoft SQL Server is now'
Declare @b int=22
Declare @c varchar(200)='years old'
DECLARE @d VARCHAR(20) --='.'
DECLARE @e DATE = GETDATE()

-- old method (need to convert to string and NULL are not converted)
Select @a + ' ' + Convert(varchar, @b)  + ' ' + @c  + ' ' + @d + ' ' + CONVERT(VARCHAR, @e)

--new method of concatenation
Select CONCAT (@a, '  ', @b, ' ', @c, ' ', @d, ' ', @e)

What’s new for date and time?

Many new functions have been added to let you create dates from parts.

For example, you can use the DateFromParts function to build a full date (notice that it will return an error if the parts are not valid):

SELECT DATEFROMPARTS(2012, 12, 25)

The other Date/Time constructors, which should be self-explanatory, are:

• DateFromParts
• TimeFromParts
• DateTimeFromParts
• SmallDateTimeFromParts
• DateTime2FromParts
• DateTimeOffsetFromParts

Another great little function implemented by the team is called EOMonth. From a date passed in parameters, it will return the last date of the month:

SELECT EOMONTH(GETDATE())

What’s new for Parsing and Conversion?

The CONVERT function is an old one that existed for years in SQL Server. One of the biggest problems this function has is to raise an error when the conversion fails. For example, this query will return an error:

SELECT CONVERT(datetime, '2012/01/31')
, CONVERT(datetime, '2012-01-31')
, CONVERT(datetime, '2012-01-32')
, CONVERT(datetime, '20120131')
, CONVERT(datetime, '12-31-2012')
, CONVERT(datetime, '31-12-2012')
, CONVERT(datetime, '31-12-2012', 103)

SQL 2012 now offers Try_Convert to work around this problem by returning NULL values for values that cannot be converted. So if just change the CONVERT for TRY_CONVERT from the previous query, you would get this query which will return a row of values including 2 NULLs:

SELECT TRY_CONVERT(datetime, '2012/01/31')
, TRY_CONVERT(datetime, '2012-01-31')
, TRY_CONVERT(datetime, '2012-01-32')
, TRY_CONVERT(datetime, '20120131')
, TRY_CONVERT(datetime, '12-31-2012')
, TRY_CONVERT(datetime, '31-12-2012')
, TRY_CONVERT(datetime, '31-12-2012', 103)

Similar to CONVERT and TRY_CONVERT, we now have the PARSE and TRY_PARSE functions. PARSE and TRY_PARSE should only be used to convert from a string to a date or number data type. PARSE and TRY_PARSE also accepts cultures to do the conversion.

PARSE and TRY_PARSE use the CLR (.Net) to do the conversion. So for performance, you should always use CONVERT/TRY_CONVERT whenever you can.

Here are some examples of PARSE and TRY_PARSE:

SELECT PARSE('Tuesday, 31 January 2012, 21:00' AS datetime USING 'en-US');
SELECT PARSE('Tuesday 31 Jan 12 9PM' AS datetime USING 'fr-CA');
SELECT TRY_PARSE('Tuesday 31 Jan 12 9PM' AS datetime USING 'fr-CA');
SELECT TRY_PARSE('Tuesday 31 Jan 12 9PM' AS datetime USING 'en-US');
SELECT TRY_PARSE('1.234' AS int USING 'en-US')
SELECT TRY_PARSE('1.234' AS money USING 'en-US')

Conclusion

I don’t consider this list to be exhaustive. These are my personal favorite’s new/enhanced features to SQL Server 2012 focusing T-SQL.

This is only the tip of the iceberg. SQL Server 2012 is a great product that you surely want to put your hands on!