Using SQL Server 2012 T-SQL New Features

Using SQL Server 2012 T-SQL New Features

In this article, I will show some of SQL Server 2012 T-SQL new features and compare them with alternative SQL script in previous version SQL Server.

Introduction

SQL Server 2012 “Denali” is the next major release of Microsoft database server. There are some new features that 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 entering all the 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:

Hide   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.

Hide   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:

Hide   Copy Code

BEGIN TRAN

SELECT NEXT VALUE FOR dbo.DemoSequence

ROLLBACK TRAN

You can see even the 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. Earlier, the programmer usually used the paging feature of DataGrid to handle this situation. Therefore, by choosing a different page number, different set of data are displayed on the screen. However, how to retrieve data from database is multiplicity. A 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.

Hide   Copy Code

SELECT *

FROM Customers

ORDER BY CustomerID

OFFSET 10 ROWS

FETCH NEXT 10 ROWS ONLY;

The OFFSET keyword and FETCH NEXT keyword allow the 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.

Hide   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 catching it. There is no way to simply re-throw it.

A sample of T-SQL script with exception handling in SQL Server 2005:

Hide   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:

Hide   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: 

Hide   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.

Hide   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

}

The way to use the new added options is like this:

Hide   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 needed to write a complicated 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.

Hide   Copy Code

sp_describe_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.