The SQL server 2011 has come up with new T SQl feature. Here is a brief detail.
The main new TSQL features in SQL Server 2011 are:
- WITH RESULT SETS
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 2001, the new ‘WithResultsSet’ feature avoids the requirement to change the stored procedure in order to change the column names in a resultset.
- OFFSET AND FETCH
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. 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.
- THROW in Error handling
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. 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.
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.
Contents are compiled from different resources. Respective copyrights are acknowledged.
Posts
