New T-SQL Features in SQL Server 2012

SQL Server 2012 (or Denali) CTP is now available and
can be downloaded at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en

SQL Server 2012 has several major enhancements including a new
look for SSMS. SSMS is now   similar to Visual Studio   with greatly improved
Intellisense support.

This article we will focus on the T-SQL Enhancements in SQL Server
2011.

The main new TSQL features in SQL Server 2012 are:

  1. WITH RESULT SETS
  2. OFFSET AND FETCH
  3. THROW in Error handling
  4. SEQUENCE

WITH RESULT SETS

This is a good feature provided with the
execution of a stored procedure.

Legacy method

In earlier versions of SQL server when we
wished to change a  column name or datatype in the resultset of a stored
procedure, all the references needed to be changed. There was no simple way to
dump the output of a stored procedure without worrying about the column names
and data types.

 2012 Method

With SQL Server 2012, the new WithResultsSet
feature avoids the requirement to change the stored procedure in order to
change the column names in a resultset.

For example :

CREATE PROCEDURE Denali_WithResultSet

AS

BEGIN

       SELECT 1 as No,’Tsql’ Type, ‘WithResultSet’ AS Feature UNION ALL

       SELECT 2 as No,’Tsql’ Type, ‘Throw’ AS Feature UNION ALL

       SELECT 3 as No,’Tsql’ Type, ‘Offset’ AS Feature UNION ALL

       SELECT 4 as No,’Tsql’ Type, ‘Sequence’ AS Feature

END

GO

EXEC Denali_WithResultSet

WITH RESULT SETS

(

       (      No int,

              FeatureType
varchar(50),

              FeatureName
varchar(50)

       ) 

)

The WithResultsSet option after the Exec
statement conatins the resultset in (…) brackets. Here, we can change the
column name and datatype according to our needs,  independent of what is column
name returned in the resultset. In the above example ‘Type’ is changed to
‘FeatureType’ and ‘Feature’ is changed to ‘FeatureName’. This can be helpful for
using an appropriate datatype while showing the resultset.

This feature will be especially helpful when
executing a stored procedure in SSIS tasks. While executing any stored
procedure in OLEDB Source, it will be now possible to execute the procedure
with the required column names and datatypes.

Continues…

Pages: 1 2 3 4




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |