Using SQL Server 2012 T-SQL New Features

Introduction

SQL Server 2012 “Denali” is the next major release of Microsoft database server. There are some new features are added to T-SQL to make common tasks much easier. I will show how to use some of the new features in this article.

Sequence

Generating a sequence number, a.k.a. auto number, is a common task in an enterprise application. For a single table, you can specify identity field. But, if you want to have database wide sequential number, then you must devise something by yourself before SQL Server 2012. One solution to this problem is to create a table that has a numeric field can be used to store sequential number, then use SQL to increase it every time used one. In SQL Server 2012, we have a new solution - use Sequence.

Create Sequence

To create a Sequence in SQL Server 2012 is very simple. You can create it with SQL Server Management Studio or T-SQL.
1. Create Sequence with SQL Server Management Studio
In Object Explorer window of SQL Server Management Studio, there is a Sequences node under Database -> [Database Name] -> Programmability. You can right click on it to bring up context menu, and then choose New Sequence… to open the New Sequence window. In New Sequence window, you can define the new Sequence, like Sequence Name, Sequence schema, Data type, Precision, Start value, Increment by, etc. After entered all required information, click OK to save it. The new Sequence will show up in Sequences node.
2. Create Sequence with T-SQL
The following T-SQL script is used to create a new Sequence

 Collapse | Copy Code

CREATE SEQUENCE DemoSequence
START WITH 1
INCREMENT BY 1;

Use Sequence

The new NEXT VALUE FOR T-SQL keyword is used to get the next sequential number from a Sequence.

 Collapse | Copy Code

SELECT VALUE FOR DemoSequence

One thing I want to mention in here is Sequence doesn’t support transaction, if you run this script

 Collapse | Copy Code

BEGIN TRAN
SELECT NEXT VALUE FOR dbo.DemoSequence
ROLLBACK TRAN

You can see even transaction is rolled back at the end. The NEXT VALUE FOR will still return the next sequential number. This behavior is consistent with identity field.

Page Data

A common situation for displaying page is how to display large amount of data in DataGrid. Before, programmer usually uses the paging feature of DataGrid to handle this situation. Therefore, by choosing different page number, different set of data are displayed on the screen. However, how to retrieve data from database is multiplicity. Developer could
1. Retrieve all data from database, and then let DataGrid to only display the current page data.
2. Retrieve the current page data from database by using temp table.
3. Retrieve the current page data from database by using ROW_NUMBER() function.
The SQL Server 2012 provided a new way to retrieve current page data from database,

 Collapse | Copy Code

SELECT *
FROM Customers
ORDER BY CustomerID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

The OFFSET keyword and FETCH NEXT keyword allow developer to only retrieve certain range data from database. If you compare this script with ROW_NUMBER() function introduced in SQL Server 2008, you can see this script is shorter and more intuitive.

 Collapse | Copy Code

SELECT *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) AS sequencenumber, *
FROM Customers) AS TempTable
WHERE sequencenumber > 10 and sequencenumber <= 20

Exception Handling

SQL Server 2005 introduced TRY CATCH block to handle exception in T-SQL. The TRY CATCH block is similar to whatever in C# language except you need always raise a new exception after catches it. There is no way to simply re-throw it.
A sample of T-SQL script with exception handling in SQL Server 2005

 Collapse | Copy Code

BEGIN TRY
 BEGIN TRANSACTION – Start the transaction

 -- Delete the Customer
 DELETE FROM Customers
 WHERE EmployeeID = ‘CACTU’

 -- Commit the change
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 -- There is an error
 IF @@TRANCOUNT > 0
  ROLLBACK TRANSACTION

 -- Raise an error with the details of the exception
 DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
 SELECT @ErrMsg = ERROR_MESSAGE(),
  @ErrSeverity = ERROR_SEVERITY()

 RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

In SQL Server 2012, by using Throw keyword, the above script will be changed to this

 Collapse | Copy Code

BEGIN TRY
 BEGIN TRANSACTION -- Start the transaction

 -- Delete the Customer
 DELETE FROM Customers
 WHERE EmployeeID = ‘CACTU’

 -- Commit the change
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 -- There is an error
 ROLLBACK TRANSACTION

 -- Re throw the exception
 THROW
END CATCH

Also, you can use Throw to replace RAISERROR function

 Collapse | Copy Code

THROW 51000, ‘The record does not exist.’, 1;

Enhanced EXECUTE keyword

The EXECUTE keyword is used to execute a command string. The previous version SQL Server only has WITH RECOMPILE option to force new plan to be re-compiled. The SQL Server 2012 dramatically improved this part. The option part is like this right now

 Collapse | Copy Code

[ WITH <execute_option> [ ,…n ] ]

<execute_option>::=
{
 RECOMPILE
 | { RESULT SETS UNDEFINED }
 | { RESULT SETS NONE }
 | { RESULT SETS ( <result_sets_definition> [,…n] ) }
}

<result_sets_definition> ::=
{
 (
  { column_name
    data_type
  [ COLLATE collation_name ]
  [ NULL | NOT NULL ] }
  [,…n ]
 )
 | AS OBJECT
  [ db_name . [ schema_name ] . | schema_name . ]
  {table_name | view_name | table_valued_function_name }
 | AS TYPE [ schema_name.]table_type_name
 | AS FOR XML
}
</result_sets_definition></result_sets_definition></execute_option></execute_option>

The way to use the new added options is like this

 Collapse | Copy Code

EXEC CustOrderDetail ‘2’
WITH RESULT SETS
(
 (
 ProductName1 varchar(100),
 Unitprice1 varchar(100),
 Quantity1 varchar(100),
 Discount1 varchar(100),
 ExtendedPrice1 varchar(100)
 )
);

Get Metadata

Application sometimes needs more insight of the SQL script result set. In the past, you need to write a complicate script to query system tables or views, e.g. sys.objects, to get all the information. In SQL Server 2012, the new system stored procedure sp_describe_first_set makes the work trivial.

 Collapse | Copy Code

sp_describ_first_result_set @tsql = N’SELECT * FROM customers’

Summary

There are more T-SQL new features in the upcoming SQL Server 2012. Majority of them are designed to improve development efficiency and reduce development effort.