SQL Server 2011 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 2011 are:
WITH RESULT SETS
OFFSET AND FETCH
THROW in Error handling
SEQUENCE
WITH RESULT SETS
--Create Sequence
CREATE SSEQUENCE [Seq]
AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000
--- Use
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
-- Alter
ALTER SEQUENCE [Seq]
RESTART WITH 1
-- Clean Up
DROP SEQUENCE [Seq]
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)
)
)
--- Importing concept of paging/Batching
SELECT ProductID, Name
FROM AdventureWorks.Production.Product
ORDER BY NAME
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
