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
This article we will focus on the T-SQL Enhancements in SQL Server
The main new TSQL features in SQL Server 2012 are:
- WITH RESULT SETS
- OFFSET AND FETCH
- THROW in Error handling
WITH RESULT SETS
This is a good feature provided with the
execution of a stored procedure.
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.
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
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
WITH RESULT SETS
( No int,
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.