Home > Articles, SQL Server 2008, T-SQL > New T-SQL Features in SQL Server 2011

New T-SQL Features in SQL Server 2011


SQL Server 2011 (or Denali) CTP is now available and can be downloaded at http://www.microsoft.com

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:

  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.

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' AS Type, 'WithResultSet' AS Feature 
    UNION ALL
   SELECT 2 AS No, 'Tsql' AS Type, 'Throw' AS Feature 
    UNION ALL
   SELECT 3 AS No, 'Tsql' AS Type, 'Offset' AS Feature 
    UNION ALL
   SELECT 4 as No, 'Tsql' AS 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

Identity Sequence
Table specific Table-independent
The new value of Identity cannot be obtained before using it in INSERT statement. Therefore it cannot be during UPDATE The new value can be obtained any time, as well as during UPDATE
It is not possible to add or remove the Identity property from an existing column It is possible to alter the properties of a Sequence object.
Minimum and maximum values cannot be defined and even cycling is not possible Minimum and maximum values can be defined and even with cycling
It is not possible to obtain a whole range of new identity values in one shot, letting the application assign the individual values It is possible to obtain a whole range of new sequence values in one shot using the stored procedure sp_sequence_get_range, letting the application assign the individual values for increased performance

Let’s look at an example of how to create a Sequence object.

USE AdventureWorks
GO

CREATE SEQUENCE dbo.Seq AS INT
START WITH 1
INCREMENT BY 1
GO

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
)
GO

CREATE TABLE dbo.Examp2 (
   Seq INT NOT NULL,
   Name VARCHAR(50) NOT NULL
)
GO

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
GO

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.

 

Source: Divya Agrawal (http://www.sql-server-performance.com)

Reference: Aleksey Fomchenko (https://sqlconsulting.wordpress.com)

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: