NEWS

Wednesday, October 12, 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/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en

SQL Server 2011 has several major enhancements including a newlook for SSMS. SSMS is now   similar to Visual Studio   with greatly improvedIntellisense support.

This article we will focus on the T-SQL Enhancements in SQL Server2011.

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’ 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
IdentitySequence
Table specificTable-independent
The new value of Identity cannot be obtained before using it in INSERT statement. Therefore it cannot be during UPDATEThe 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 columnIt is possible to alter the properties of a Sequence object.
Minimum and maximum values cannot be defined and even cycling is not possibleMinimum 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 valuesIt 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;
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.