Click here to Skip to main content
Click here to Skip to main content
Go to top

Using SQL Server 2012 T-SQL New Features

, 5 Dec 2013
Rate this:
Please Sign up or sign in to vote.
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:
    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.

SELECT VALUE FOR DemoSequence

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

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

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:

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:

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: 

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.

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

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.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Henry He
Software Developer (Senior)
United States United States
Senior Software Developer from New Jersey, USA

Have 12+ years experience on enterprise application development with various technologies.

Comments and Discussions

 
GeneralMy vote of 5 PinmemberRyan Pringnitz16-Dec-13 16:41 
Generalgood work PinprofessionalMenon Santosh7-Dec-13 21:44 
QuestionVery nice article but one little error PinmemberRoman Ranzmaier5-Dec-13 2:20 
AnswerRe: Very nice article but one little error PinmemberHenry He6-Dec-13 14:19 
GeneralMy vote of 5 PinmemberSudhakar Shinde24-Apr-13 3:21 
GeneralMy vote of 5 PinmemberS.P. Tiwari8-Jul-12 22:35 
Questionsequence replication Pinmembercengelbrecht14-Jun-12 16:55 
AnswerRe: sequence replication PinmemberHenry He14-Jun-12 17:36 
GeneralRe: sequence replication Pinmembercengelbrecht17-Jun-12 13:28 
GeneralMy vote of 3 PinmemberMember 883399719-Apr-12 4:02 
GeneralMy vote of 5 Pinmembermanoj kumar choubey17-Apr-12 2:30 
Questiongood info Pinmemberampere.pradeep10-Apr-12 21:17 
GeneralMy vote of 5 PinmemberAnurag Gandhi10-Apr-12 5:05 
QuestionSequence PinmemberPIEBALDconsult14-Oct-11 4:30 
AnswerRe: Sequence PinmemberHenry He14-Oct-11 13:42 
GeneralRe: Sequence PinmemberPIEBALDconsult14-Oct-11 14:01 
AnswerRe: Sequence PinmemberRichard Deeming17-Oct-11 5:00 
GeneralMy vote of 5 PinmemberMario Majcica13-Oct-11 23:53 
GeneralMy vote of 5 PinmemberDr. Song Li13-Oct-11 16:27 
QuestionThanks for the information PinmemberDr. Song Li13-Oct-11 16:26 
Nice!
QuestionSQL Server "Denali" will be SQL Server 2012 PinmemberMarc Scheuner13-Oct-11 3:11 
AnswerRe: SQL Server "Denali" will be SQL Server 2012 PinmemberHenry He13-Oct-11 16:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 5 Dec 2013
Article Copyright 2011 by Henry He
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid