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




Related Articles :

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 |