New features in T-SQL 2012

1. OFFSET and FETCH

Microsoft has decided in SQL Server 2012, that they will modify the ORDER BY clause and do what MySQL has been doing for a long time – providing simple functions for paging result sets.

This comes in the form of OFFSET and FETCH.

Now, I’m not saying that this was previously not possible in SQL Server. There are solutions to this problem in other versions of the product in the form of temp tables, ROW_NUMBER() and TOP but I prefer OFFSET and FETCH to the others.

I am using SQL Server 2012 Express and the AdventureWorks 2012 database for this demonstration.

So lets look at some data and I have decided to query some of the fields from the TransactionHistory table under the Production schema.

1	SELECT TOP 10

2	TransactionID

3	, ProductID

4	, TransactionDate

5	, Quantity

6	, ActualCost

7

FROM

8	Production.TransactionHistory;

TransactionID ProductID   TransactionDate         Quantity    ActualCost
------------- ----------- ----------------------- ----------- ---------------------
100000        784         2007-09-01 00:00:00.000 2           0.00
100001        794         2007-09-01 00:00:00.000 1           0.00
100002        797         2007-09-01 00:00:00.000 1           0.00
100003        798         2007-09-01 00:00:00.000 1           0.00
100004        799         2007-09-01 00:00:00.000 1           0.00
100005        800         2007-09-01 00:00:00.000 1           0.00
100006        801         2007-09-01 00:00:00.000 1           0.00
100007        954         2007-09-01 00:00:00.000 1           0.00
100008        955         2007-09-01 00:00:00.000 1           0.00
100009        966         2007-09-01 00:00:00.000 1           0.00

(10 row(s) affected)

This table contains approximately 133,500 rows – not a massive amount in today’s world but for the purposes of this article, lets say I wanted to write queries to page through this data sorted by newest transactions and I wanted to display 20 rows per page.

Using OFFSET and FETCH in SQL Server 2012

So here is an example. Note that OFFSET and FETCH are added after the ORDER BY clause.

01

SELECT

02	TransactionID

03	, ProductID

04	, TransactionDate

05	, Quantity

06	, ActualCost

07

FROM

08	Production.TransactionHistory

09	ORDER BY TransactionDate DESC

10	OFFSET 0 ROWS

11	FETCH NEXT 20 ROWS ONLY;

OFFSET provides a starting row from which to display the result set. FETCH instructs the query to display the number of rows you want in your result set from the OFFSET point.

Note that NEXT or FIRST can be supplied for FETCH and are synonyms for ANSI compatibility. You can also type ROW or ROWS, again they are synonyms for ANSI compatibility.

So this is nice and easy, for the next page of results, the OFFSET value would be changed to 20 and then 40 etc.

OFFSET and FETCH can accept variables so for example:

01	DECLARE @OffsetRows tinyint = 0

02	, @FetchRows tinyint = 20;

03

SELECT

04	TransactionID

05	, ProductID

06	, TransactionDate

07	, Quantity

08	, ActualCost

09

FROM

10	Production.TransactionHistory

11	ORDER BY TransactionDate DESC

12	OFFSET @OffsetRows ROWS

13	FETCH NEXT @FetchRows ROWS ONLY;

You can use expressions in OFFSET and FETCH:

1	ORDER BY TransactionDate DESC

2	OFFSET @OffsetRows - 0 ROWS

3	FETCH NEXT @FetchRows - @OffsetRows + 1 ROWS ONLY;

And I really like this – plugging in a scalar sub queries:

01

SELECT

02	TransactionID

03	, ProductID

04	, TransactionDate

05	, Quantity

06	, ActualCost

07

FROM

08	Production.TransactionHistory

09	ORDER BY TransactionDate DESC

10	OFFSET @OffsetRows ROWS

11	FETCH NEXT (SELECT 20) ROWS ONLY;

So imagine that (SELECT 20) was in fact reading a table somewhere in your system (SELECT PageSize FROM PageSetting WHERE SiteID = 5) which controlled the number of rows to be displayed for each query.

OFFSET and FETCH versus ROW_NUMBER()

I’m not going to go into detail about all the methods of paging which have been employed in previous versions of SQL Server and start drawing comparisons and conclusions over performance (perhaps I will in a future post) but the one which immediately springs to mind as an alternative to OFFSET and FETCH is ROW_NUMBER()

So a quick comparison between the two methods shows the following:

Using OFFSET and FETCH

01

SELECT

02	TransactionID

03	, ProductID

04	, TransactionDate

05	, Quantity

06	, ActualCost

07

FROM

08	Production.TransactionHistory

09	ORDER BY TransactionDate DESC

10	OFFSET 0 ROWS

11	FETCH NEXT 20 ROWS ONLY

Using ROW_NUMBER() with CTE

01	WITH Paging_CTE AS

02

(

03

SELECT

04	TransactionID

05	, ProductID

06	, TransactionDate

07	, Quantity

08	, ActualCost

09	, ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNumber

10

FROM

11	Production.TransactionHistory

12

)

13

SELECT

14	TransactionID

15	, ProductID

16	, TransactionDate

17	, Quantity

18	, ActualCost

19

FROM

20	Paging_CTE

21	WHERE RowNumber > 0 AND RowNumber <= 20

So what do you think? It’s certainly easier to write a query using OFFSET and FETCH as there is less involved. There is one less column too because “RowNumber” is not needed for the OFFSET and FETCH version.

If I were a betting man, I would say that the execution plans are different between the two queries. So lets take a look.

Using OFFSET and FETCH…

Using ROW_NUMBER()…

There is certainly more going on in the second one right? As I wrote above, I’m not intending to draw any conclusions on this because I am not doing any thorough testing here but it’s possible that OFFSET and FETCH would perform better than ROW_NUMBER() as a paging solution.

Finally, if you want to guarantee stable results in your OFFSET and FETCH paging solution there are two things that you must implement.

1. You should ensure that the underlying data does not change and that involves running the queries inside a transaction using either snapshot or serializable transaction isolation.
2. The ORDER BY clause needs to contain a column or combination of columns that are guaranteed to be unique.

2. WITH RESULT SETS

New in SQL Server 2012 comes an extension to EXECUTE in the form of WITH RESULT SETS.WITH RESULT SETS allows column names and their data types to be changed in the result set of a stored procedure.

My first thought with this improvement is that it could be very useful to have when building SSIS packages which read from a stored procedure as a data source. We’ll take a look at that in a future post but in addition to that, it helps to replace some of the following…

Before Server 2012 WITH RESULT SETS you had to…

If you wanted to change the column names and their data types after executing a stored procedure, then in previous versions there were a few things to try.

1. Create a copy of the stored procedure and change it to return different column names or data types.
2. Execute the stored procedure and insert the results into a temporary table, then read from the temporary table.
3. Edit the same stored procedure and change its output.
4. Create a UDF and select from that.

Some of the issues which spring to mind with those points are code duplication, sub-optimal performance because of the use of temp tables and just hassle in writing lengthy code. I’m going to try and look at some of these in a future post.

So lets check WITH RESULT SETS syntax

To get it working you supply WITH RESULT SETS and parentheses followed by the column names and data types as follows:

1	EXEC Your_Stored_Procedure

2	WITH RESULT SETS

3

(

4

(

5	Col1_Renamed INT,

6	Col2_Renamed VARCHAR(50)

7

)

8

)

If your stored procedure is returning multiple result sets then it is possible to manipulate all of them. For example:

01	EXEC Your_Other_Stored_Procedure

02	WITH RESULT SETS

03

(

04

(

05	Col1_Renamed INT,

06	Col2_Renamed VARCHAR(50))

07

,

08

(

09	Col3_Renamed TINYINT,

10	Col4_Renamed NVARCHAR(100)

11

)

12

)

Please note that the number of columns being returned in the result set cannot be changed.

Lets look at a demo of it in action against the AdventureWorks2012 database. First, I will create a new stored procedure…

1	CREATE PROCEDURE GetCustomerOrders @CustomerID INT

2

AS

3

4

SELECT

5	SalesOrderID

6	, OrderDate

7	FROM Sales.SalesOrderHeader

8	WHERE CustomerID = @CustomerID

Now, I’m going to execute it and use the WITH RESULT SETS to change the output.

1	EXEC GetCustomerOrders @CustomerID = 11000

2	WITH RESULT SETS

3

(

4

(

5	OrderID INT,

6	DateOfOrder VARCHAR(20)

7

)

8

)

Which returns…

OrderID     DateOfOrder
----------- --------------------
43793       Jul 22 2005 12:00AM
51522       Jul 22 2007 12:00AM
57418       Nov  4 2007 12:00AM

(3 row(s) affected)

Without the WITH RESULT SETS it looks like this

SalesOrderID OrderDate
------------ -----------------------
43793        2005-07-22 00:00:00.000
51522        2007-07-22 00:00:00.000
57418        2007-11-04 00:00:00.000

(3 row(s) affected)

3. Conversion Functions

SQL Server 2012 (“Denali”) is nearly here and with all new releases, there are always new features to know about.

This first post will cover the new conversion functions in SQL Server 2012.

TRY_CONVERT()

Should the function fail then NULL is returned. The following all return the same output bar the last statement which fails and returns NULL.

1	SET NOCOUNT ON;

2	SELECT TRY_CONVERT(datetime, '2012/01/31');

3	SELECT TRY_CONVERT(datetime, '2012-01-31');

4	SELECT TRY_CONVERT(datetime, '20120131');

5	SELECT TRY_CONVERT(datetime, '31-12-2012');

Returns

-----------------------
2012-01-31 00:00:00.000

There is an optional integer expression called “style” which is like CONVERT and actually shares the same integer values for styling the output.

1	SET NOCOUNT ON;

2	SELECT TRY_CONVERT(datetime, '20120131', 103);

Returns

-----------------------
2012-01-31 00:00:00.000

PARSE()

Accepts a nvarchar input up to 4000 chars and attempts to evaluate the input based on the value and datatype specified for the parse. The following examples return the same output.

1	SET NOCOUNT ON;

2	SELECT PARSE('Tuesday, 31 January 2012, 21:00' AS datetime USING 'en-GB');

3	SELECT PARSE('Tuesday, 31 January 2012, 9PM' AS datetime USING 'en-GB');

4	SELECT PARSE('Tuesday 31 January 2012 9PM' AS datetime USING 'en-GB');

5	SELECT PARSE('Tuesday 31 Jan 2012 9PM' AS datetime USING 'en-GB');

6	SELECT PARSE('Tuesday 31 Jan 12 9PM' AS datetime USING 'en-GB');

Returns

-----------------------
2012-01-31 21:00:00.000

The part which reads USING ‘en-GB’ is the culture specified. If no culture is specified then the language of the current session gets used.

Should the parse fail, then an error is produced.

TRY_PARSE()

Just like TRY_CONVERT(), TRY_PARSE() will return NULL if the TRY fails to parse the input. So trying to parse a string as a date would fail and return NULL. Again, you can specify USING to try a specific culture. The following returns NULL.

1	SET NOCOUNT ON;

2	SELECT TRY_PARSE('This is a date' AS datetime USING 'en-GB');

Just like TRY_CONVERT(), it’s useful for testing output, for example:

1	SET NOCOUNT ON;

2	SELECT CASE WHEN TRY_PARSE('This is a date' AS datetime USING 'en-GB')IS NULL THEN

3	'Parse failed'

4

ELSE

5	'Parse succeeded'

6

END;

Returns

---------------
Parse failed

4. CREATE SEQUENCE

What is CREATE SEQUENCE for?

A common feature in table design is to place an auto incremental number on a field in the form of an IDENTITY column. So this is an easy way of maintaining a sequential number on a table.

What if you wanted to create a database wide identity? Prior to SQL 2012, you might choose to do it by having a table sitting there in the middle of it all with a numeric field which gets updated by some function every time a new identity is created. CREATE SEQUENCE takes away this overhead.

You can create a sequence either using SQL Server Management Studio or using T-SQL. In Management Studio, find the “Sequences” folder under “Programmability” beneath the database you want to add a sequence for.

Here you can configure the sequence according to your design.

To do the same thing in T-SQL, the syntax is:

1	CREATE SEQUENCE MyDemoSequence

2	START WITH 1

3	INCREMENT BY 1;

If you choose to do this using T-SQL then by default a sequence is created as a BIGINT datatype unless you specify otherwise, for example:

1	CREATE SEQUENCE MyDemoSequence AS SMALLINT

2	START WITH 1

3	INCREMENT BY 1;

Note that START, INCREMENT, MINVALUE and MAXVALUE must be configured within the boundaries of the data type. For example, you couldn’t specify a negative START value for a TINYINT data type.

To use CREATE SEQUENCE

To get the next value in the sequence use the NEXT VALUE FOR

1	SELECT NEXT VALUE FOR MyDemoSequence;

If you were inserting to a table, it would like something like this:

1	INSERT INTO YourTable(ID, Name)

2	VALUES(NEXT VALUE FOR MyDemoSequence, 'Your name');

So what else is there to know about CREATE SEQUENCE?

CYCLE and NO CYCLE tells the sequence to cycle back round to the minimum value when the last value has been allocated in the sequence. By default, this is set to NO CYCLE.

CACHE or NO CACHE, designed for performance benefits in reducing IO requests for new numbers out of a sequence. A cache value can be specified and the numbers up to the maximum cache value are loaded up into memory until the cache is exceeded and a new set of numbers is required.

For example, you might create a sequence with a cache of 20. When a value is needed from the sequence, the minimum value in the sequence up to and including the CACHE value are loaded into memory. The CACHE value of 20 is written to the system table sys.sequences and when 20 gets used and 21 is requested, then a fresh set of numbers is allocated to the cache (21-40) with 40 being written to the sys.sequences table as the CACHE value.

Here’s an example of a sequence created using CYCLE and CACHE

1	CREATE SEQUENCE MyDemoSequence AS INT

2	START WITH 1

3	INCREMENT BY 1

4

CACHE 20

5

CYCLE;

Lets have a quick look at the sys.sequences table

1	SELECT cache_size, is_cached, current_value

2	FROM sys.sequences

3	WHERE name = 'MyDemoSequence';

Returns

cache_size  is_cached current_value
----------- --------- -------------
20          1         1

(1 row(s) affected)

5. THROW

New in SQL Server 2012 as part of exception handling in T-SQL is THROW

Exception handling in previous versions of SQL Server

THROW will raise an exception inside of the catch block and there are already ways to do this in previous versions of SQL Server but where this is an improvement over previous methods is that it simplifies writing the code to raise them.

So lets compare the old and the new.

SQL Server 2005/SQL2008

01

BEGIN TRY

02	DECLARE @VarToTest INT

03	SET @VarToTest = 'C'

04

END TRY

05	BEGIN CATCH

06

DECLARE

07	@ErrorMessage nvarchar(4000)

08	, @ErrorSeverity int

09

10	SET @ErrorMessage = ERROR_MESSAGE()

11	SET @ErrorSeverity = ERROR_SEVERITY()

12

13	RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )

14

END CATCH

SQL Server 2012

1

BEGIN TRY

2	DECLARE @VarToTest INT

3	SET @VarToTest = 'C'

4

END TRY

5	BEGIN CATCH

6

THROW

7

END CATCH

Msg 245, Level 16, State 1, Line 4 Conversion failed when converting the varchar value 'C' to data ty

Source: http://www.dbadiaries.com/new-t-sql-features-in-sql-server-2012-conversion-functions/