Click here to Skip to main content
15,860,844 members
Articles / Programming Languages / SQL

Using SQL Server 2012 T-SQL New Features

Rate me:
Please Sign up or sign in to vote.
4.68/5 (32 votes)
5 Dec 2013CPOL4 min read 125.1K   60   22
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:
    SQL
    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.

SQL
SELECT VALUE FOR DemoSequence

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

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

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

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

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

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

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

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

SQL
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)


Written By
Software Developer (Senior)
United States United States
Senior Software Developer from New Jersey, USA

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

Comments and Discussions

 
GeneralMy vote of 5 Pin
Ryan Pringnitz16-Dec-13 16:41
Ryan Pringnitz16-Dec-13 16:41 
Generalgood work Pin
Menon Santosh7-Dec-13 21:44
professionalMenon Santosh7-Dec-13 21:44 
QuestionVery nice article but one little error Pin
Roman Ranzmaier5-Dec-13 2:20
Roman Ranzmaier5-Dec-13 2:20 
AnswerRe: Very nice article but one little error Pin
Henry He6-Dec-13 14:19
Henry He6-Dec-13 14:19 
GeneralMy vote of 5 Pin
Sudhakar Shinde24-Apr-13 3:21
Sudhakar Shinde24-Apr-13 3:21 
GeneralMy vote of 5 Pin
S.P.Tiwari8-Jul-12 22:35
professionalS.P.Tiwari8-Jul-12 22:35 
Questionsequence replication Pin
cengelbrecht14-Jun-12 16:55
professionalcengelbrecht14-Jun-12 16:55 
AnswerRe: sequence replication Pin
Henry He14-Jun-12 17:36
Henry He14-Jun-12 17:36 
GeneralRe: sequence replication Pin
cengelbrecht17-Jun-12 13:28
professionalcengelbrecht17-Jun-12 13:28 
GeneralMy vote of 3 Pin
Member 883399719-Apr-12 4:02
Member 883399719-Apr-12 4:02 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey17-Apr-12 2:30
professionalManoj Kumar Choubey17-Apr-12 2:30 
Questiongood info Pin
Arav Pradeep Gupta10-Apr-12 21:17
Arav Pradeep Gupta10-Apr-12 21:17 
GeneralMy vote of 5 Pin
Anurag Gandhi10-Apr-12 5:05
professionalAnurag Gandhi10-Apr-12 5:05 
QuestionSequence Pin
PIEBALDconsult14-Oct-11 4:30
mvePIEBALDconsult14-Oct-11 4:30 
AnswerRe: Sequence Pin
Henry He14-Oct-11 13:42
Henry He14-Oct-11 13:42 
GeneralRe: Sequence Pin
PIEBALDconsult14-Oct-11 14:01
mvePIEBALDconsult14-Oct-11 14:01 
AnswerRe: Sequence Pin
Richard Deeming17-Oct-11 5:00
mveRichard Deeming17-Oct-11 5:00 
GeneralMy vote of 5 Pin
Mario Majčica13-Oct-11 23:53
professionalMario Majčica13-Oct-11 23:53 
GeneralMy vote of 5 Pin
Dr. Song Li13-Oct-11 16:27
Dr. Song Li13-Oct-11 16:27 
QuestionThanks for the information Pin
Dr. Song Li13-Oct-11 16:26
Dr. Song Li13-Oct-11 16:26 
QuestionSQL Server "Denali" will be SQL Server 2012 Pin
Marc Scheuner13-Oct-11 3:11
professionalMarc Scheuner13-Oct-11 3:11 
AnswerRe: SQL Server "Denali" will be SQL Server 2012 Pin
Henry He13-Oct-11 16:02
Henry He13-Oct-11 16:02 

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

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