Microsoft® SQL Server® 2012 Bible

409

Chapter 16: Programming with T-SQL

16

What’s New in T-SQL for 2012

New features and enhancements in SQL Server 2012 are highlighted throughout this book.

However, there are a few items that deserve a mention within this chapter.

Debugging Enhancements

Debugging within SQL Server Management Studio has historically been an exercise in frus-

tration. With SQL Server 2012, you now have the ability to set conditional breakpoints, hit

count thresholds, and import and export the breakpoints to an XML fi le for portability. In

addition, you may now fully customize the keyboard for debug mode.

Metadata Discovery

Two new enhancements, delivered via system stored procedures and functionally equivalent

dynamic management objects (DMO), provide metadata about what the result set will be

when a T-SQL batch is executed.

Sys.dm_exec_describe_first_result_set

The DMO sys.dm_exec_describe_first_result_set returns metadata for the fi rst

possible result set of the Transact-SQL batch. The metadata provided is similar to what you

might see if you query sys.objects. The huge advantage is that it evaluates all columns

in the query, consolidating object metadata into one result set. The system stored proce-

dure sp_describe_first_result_set provides the same information. Simply pass the

T-SQL batch to the DMO, along with any parameters and their data types, as well as the

browse_information_mode parameter.

SELECT *

FROM sys.dm_exec_describe_first_result_set

(N'TSQL', N'Parameters', 0);

The following example returns three rows of metadata from ProductCategory,

ProductSubCategory, and Product, respectively.

USE AdventureWorks2012;

GO

SELECT *

FROM sys.dm_exec_describe_first_result_set

(N'SELECT c.Name, s.Name, p.Name

FROM Production.ProductCategory c

JOIN Production.ProductSubcategory s

ON c.ProductCategoryID = s.ProductCategoryID

JOIN Production.Product p

ON s.ProductSubcategoryID = p.ProductSubcategoryID

c16.indd 409c16.indd 409 7/30/2012 5:38:09 PM7/30/2012 5:38:09 PM

http://www.it-ebooks.info