New T-SQL Features in SQL Server 2011
Post date: Apr 23, 2011 9:43:40 AM
SQL Server 2011 (or Denali) CTP is now available and can be downloaded at
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
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.
2011 Method
With SQL Server 2001, 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.
OFFSET and FETCH
SQL Server Denali has introduced a new feature to make paging more efficient.
Legacy Method
In previous versions when we needed to code the paging of results or for example, get the second highest salary from a payroll table, we need to write a complex code having NOT IN which is a low performance code.
2011 Method
SQL Server 2011 introduces the OFFSET command for paging or selecting for example the highest salary from a table.
The OFFSET with FETCH commands can limit the number of rows sent to the client. Instead of using the TOP command for paging, these new commands can be used to reduce complexity. These new commands will normally be accompanied with ORDER BY.
For example:
--Leave first 10 rows and Fetch next 5 rows
SELECT ProductID, Name
FROM AdventureWorks.Production.Product
ORDER BY NAME
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
OFFSET in the above query will hide the first 10 rows and FETCH NEXT will show next 5 rows, which are ordered on Name.
Let’s look at how OFFSET can be helpful in Paging, by comparing both methods. In this example, we need to select the third page with each page having 5 records.
This can be achieved by a combination of TOP, ORDER and NOT IN.
--Legacy method
SELECT TOP(5) ProductID, Name
FROM AdventureWorks.Production.Product
WHERE ProductID NOT IN(SELECT TOP(10) ProductID FROM AdventureWorks.Production.Product ORDER BY NAME)
ORDER BY NAME
Using OFFSET and FETCH NEXT it is easy to achieve the above result:
--2011 method
SELECT ProductID, Name
FROM AdventureWorks.Production.Product
ORDER BY NAME
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY
THROW in Error Handling
Error handling is now easier with the introduction of the THROW command in SQL Server 2011.
Legacy method (SQL 2005 onwards)
In previous versions, RAISERROR was used to show an error message. RAISERROR requires a proper message number to be shown when raising any error. The message number should exist in sys.messages. RAISERROR cannot be used to re-throw an exception raised in a TRY..CATCH block.
2011 method
Unlike RAISERROR, THROW does not require that an error number to exist in sys.messages (although it has to be between 50000 and 2147483647). All exceptions being raised by THROW have a severity of 16.
You can throw an error using Throw as below:
THROW 50001, 'Error message', 1;
This will return an error message:
Msg 50001, Level 16, State 1, Line 1 Error message
THROW even allows for re-throwing an exception caught in a TRY..CATCH block, which RAISERROR was not able to handle:
BEGIN TRY
SELECT 'Using Throw'
SELECT 1 / 0
END TRY
BEGIN CATCH
--Throw error
THROW
END CATCH
The above code snippet produces this output:
(1 row(s) affected)
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.
THROW has now made the developer’s life much easier, and developers can now code independent of the Tester’s input on the exception message.
NOTE: The current version of 2011 Books Online indicates that RAISERROR has been deprecated. Due to this, the use of RAISERROR should be minimized.
SEQUENCE
In SQL Server 2011, Sequence is an object in each database and is similar to IDENTITY in its functionality. Sequence is an object that has start value, increment value and an end value defined in it. It can be added to a column whenever required rather than defining an identity column individually for tables.
In SQL Server, Sequence is a much anticipated feature which was available in Oracle for many years. In previous versions of SQL Server, the Identity property is used in a specific table as a Primary key having a non-repeatable value. However, there are several limitations of using the Identity property which can be overcome by the introduction of this new object ‘SEQUENCE’.
Differences between Sequence and Identity
Let’s look at an example of how to create a Sequence object.
USE AdventureWorks;
CREATE SEQUENCE dbo.Seq AS INT
START WITH 1
INCREMENT BY 1;
To generate a new sequence of values, you can use NEXT VALUE FOR.
SELECT NEXT VALUE FOR dbo.Seq;
SELECT NEXT VALUE FOR dbo.Seq;
SELECT NEXT VALUE FOR dbo.Seq;
To assign the result into an INSERT statement, let us create two tables:
CREATE TABLE dbo.Examp1
(
Seq INT NOT NULL,
Name VARCHAR(50) NOT NULL
);
CREATE TABLE dbo.Examp2
(
Seq INT NOT NULL,
Name VARCHAR(50) NOT NULL
);
Insert one row into each table. Unlike Identity, Sequence does not guarantee uniqueness. A unique PK constraint must be enforced to the column to guarantee uniqueness.
INSERT INTO dbo.Examp1(Seq, Name) VALUES(NEXT VALUE FOR dbo.Seq, 'Tom');
INSERT INTO dbo.Examp2(Seq, Name) VALUES(NEXT VALUE FOR dbo.Seq, 'Jerry');
SELECT * FROM Examp1
SELECT * FROM Examp2
Notice that the Sequence value is set as 4 and 5, which means that the previous three select statements have incremented the value till 3.
Now, let’s look at an example of using Sequence with OVER ordering on any item.
INSERT INTO dbo.Examp1(Seq,Name)
SELECT NEXT VALUE FOR dbo.Seq OVER(ORDER BY name ASC), 'List'
FROM (SELECT name
FROM sys.objects
ORDER BY object_id DESC
OFFSET 10 ROWS FETCH FIRST 3 ROWS ONLY) Temp;
Here, the list is generated from 6 to 8 and inserted into the table, ordered by Name.
To restart the Sequence, you can alter the object to start with the required value.
ALTER SEQUENCE seq
RESTART WITH 1
This will restart Seq with 1 and follow the same increment as defined earlier.
Now, let us take an example of obtaining a long range in one shot.
CREATE SEQUENCE SeqRange
AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 25
DECLARE @first_value sql_variant,
@last_value sql_variant
EXEC sp_sequence_get_range
@sequence_name = N'SeqRange',
@range_size = 4,
@range_first_value = @first_value OUTPUT,
@range_last_value = @last_value OUTPUT;
SELECT @first_value AS FirstNumber, @last_value as LastNumber
This will increment the Seqeunce object till 4 and the values from 1 to 4 will remain unused anywhere. You can code these unused values as per your new logic anywhere in the table. This cannot be achieved by using the Identity property.
To summarize, the Sequence object has many advantages over the Identity property and its flexibility will definitely help in solving complex T-sql queries.
CONCLUSION
All the new enhancements listed in this article will help developers writing T-SQL faster with less code and higher performance
-Courtesy Divya Agrawal