T-SQL Enhancements in SQL Server 2011 (CTP1)

/0 Comments/in /by

SQL Server 2011 (code named Denali) CTP1 was announced in November 2010 during the SQL PASS Summit in Seattle. While a bit disappointing not to see the much anticipated full implementation of the window functions (hope we will still see that in a future CTP version), it offers some interesting new programmability features. These new enhancements address specific problems that we see very often in business applications. Here is a quick look at the key new features in T-SQL.

Feature: OFFSET
Application use: paging
Comments: provides simplified syntax and efficient method for data paging solutions

Listing 1: OFFSET example
CREATE TABLE Customers (
customer_nbr INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(35) NOT NULL);

INSERT INTO Customers
VALUES
(1, 'Joe'),
(2, 'John'),
(3, 'Jane'),
(4, 'Peter'),
(5, 'Mary'),
(6, 'Jose'),
(7, 'Daniel'),
(8, 'Adam'),
(9, 'Chris'),
(10, 'Tom'),
(11, 'Evan'),
(12, 'Lora');

SELECT customer_name
FROM Customers
ORDER BY customer_name;

/*

customer_name
---------------
Adam
Chris
Daniel
Evan
Jane
Joe
John
Jose
Lora
Mary
Peter
Tom

*/

DECLARE @page_nbr INT = 1, @page_size INT = 5;

-- first page
SELECT customer_name
FROM Customers
ORDER BY customer_name
OFFSET (@page_nbr - 1) * @page_size ROWS FETCH NEXT @page_size ROWS ONLY;

/*

customer_name
---------------
Adam
Chris
Daniel
Evan
Jane

*/

-- second page
SET @page_nbr = 2;

SELECT customer_name
FROM Customers
ORDER BY customer_name
OFFSET (@page_nbr - 1) * @page_size ROWS FETCH NEXT @page_size ROWS ONLY;

/*

customer_name
---------------
Joe
John
Jose
Lora
Mary

*/

-- last page
SELECT customer_name
FROM Customers
ORDER BY customer_name
OFFSET ((SELECT COUNT(*) FROM Customers) / @page_size) * @page_size
ROWS FETCH NEXT @page_size ROWS ONLY;

/*

customer_name
---------------
Peter
Tom

*/

-- any 3 customers
SELECT customer_name
FROM Customers
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

/*

customer_name
---------------
Joe
John
Jane

*/

DROP TABLE Customers;

Feature: THROW
Application use: error handling
Comments: allow to re-throw the original error

Listing 2: THROW example
BEGIN TRY    

  -- inside code THROW is similar to RAISERROR with limitations
  THROW 51000, 'User error.', 1;

END TRY

BEGIN CATCH    

   -- inside CATCH rethrow the error
   THROW;
  
END CATCH

Feature: SEQUENCE
Application use:  replacement for IDENTITY
Comments: ANSI standard method for sequences, improves on shortcomings of IDENTITY

Listing 3: SEQUENCE example
CREATE TABLE Customers (
customer_nbr INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(35) NOT NULL);

-- create sequence starting with value 1, minimum value 1,
-- no maximum value, and increment by 1
CREATE SEQUENCE CustomerNbr AS INT    
MINVALUE 1    
NO MAXVALUE    
START WITH 1
INCREMENT BY 1;

-- generate customer numbers based on the sequence
-- using the NEXT VALUE FOR function
INSERT INTO Customers (customer_nbr, customer_name)
SELECT NEXT VALUE FOR CustomerNbr, 'Joe' UNION ALL
SELECT NEXT VALUE FOR CustomerNbr, 'John' UNION ALL
SELECT NEXT VALUE FOR CustomerNbr, 'Jane' UNION ALL
SELECT NEXT VALUE FOR CustomerNbr, 'Peter' UNION ALL
SELECT NEXT VALUE FOR CustomerNbr, 'Mary';

SELECT customer_nbr, customer_name
FROM Customers
ORDER BY customer_nbr;

/*

customer_nbr customer_name
------------ ---------------
1            Joe
2            John
3            Jane
4            Peter
5            Mary

*/

-- use OVER to generate next set of sequence numbers
-- based on ordering by customer name
INSERT INTO Customers (customer_nbr, customer_name)
SELECT NEXT VALUE FOR CustomerNbr OVER(ORDER BY customer_name), customer_name
FROM (
VALUES ('Jose'), ('Daniel'), ('Adam'), ('Chris'), ('Tom')) AS T(customer_name);

SELECT customer_nbr, customer_name
FROM Customers
ORDER BY customer_nbr;

/*

customer_nbr customer_name
------------ ---------------
1            Joe
2            John
3            Jane
4            Peter
5            Mary
6            Adam
7            Chris
8            Daniel
9            Jose
10           Tom

*/

-- alter sequence to set next number to 20
-- and increment by 10
ALTER SEQUENCE CustomerNbr    
RESTART WITH 20    
INCREMENT BY 10;

INSERT INTO Customers (customer_nbr, customer_name)
SELECT NEXT VALUE FOR CustomerNbr, 'Evan' UNION ALL
SELECT NEXT VALUE FOR CustomerNbr, 'Lora';

SELECT customer_nbr, customer_name
FROM Customers
ORDER BY customer_nbr;

/*

customer_nbr customer_name
------------ ---------------
1            Joe
2            John
3            Jane
4            Peter
5            Mary
6            Adam
7            Chris
8            Daniel
9            Jose
10           Tom
20           Evan
30           Lora

*/

-- reset sequence to 1
ALTER SEQUENCE CustomerNbr    
RESTART WITH 1    
INCREMENT BY 1;

DECLARE @first_value SQL_VARIANT, @last_value SQL_VARIANT;

-- get a range of 5 sequence values: 1, 2, 3, 4, 5
-- next available value is 6
EXEC sp_sequence_get_range
  @sequence_name = N'CustomerNbr',
  @range_size = 5,
  @range_first_value = @first_value OUTPUT,
  @range_last_value = @last_value OUTPUT;

SELECT @first_value AS range_first_value,
       @last_value AS range_last_value;

/*

range_first_value   range_last_value
------------------- ------------------
1                   5

*/

SELECT NEXT VALUE FOR CustomerNbr AS next_value;

/*

next_value
-----------
6

*/

DROP TABLE Customers;
DROP SEQUENCE CustomerNbr;

Feature: EXECUTE WITH RESULT SETS
Application use: manipulate stored procedure output result set
Comments: capabilities to rename output result set columns without changing the original stored procedure; no options to remove/add columns or remove a result set when multiple result sets are returned

Listing 4: EXECUTE WITH RESULT SETS example
CREATE PROCEDURE CalculateSales
AS

SELECT sale_month, sale_amount
FROM (VALUES('2010-01', 120.50),
             ('2010-02', 214.00),
             ('2010-03', 109.10)) AS T(sale_month, sale_amount);

SELECT SUM(sale_amount) AS total_sales
FROM (VALUES('2010-01', 120.50),
             ('2010-02', 214.00),
             ('2010-03', 109.10)) AS T(sale_month, sale_amount);

GO

EXECUTE CalculateSales;

/*

sale_month sale_amount
---------- -------------
2010-01    120.50
2010-02    214.00
2010-03    109.10

(3 row(s) affected)

total_sales
-------------
443.60

(1 row(s) affected)

*/

EXECUTE CalculateSales
WITH RESULT SETS
(
    (month CHAR(7), amount DECIMAL(10, 2)),

    (total DECIMAL(10, 2))
);

/*

month   amount
------- --------
2010-01 120.50
2010-02 214.00
2010-03 109.10

(3 row(s) affected)

total
--------
443.60

(1 row(s) affected)

*/

DROP PROCEDURE CalculateSales; </br/>

Feature: describe result sets
Application use: determining the format of a response without actually running the query
Comments: replaces SET FMTONLY

Listing 5: Describe result sets example
CREATE PROCEDURE CalculateSales
AS

SELECT sale_month, sale_amount
FROM (VALUES('2010-01', 120.50),
             ('2010-02', 214.00),
             ('2010-03', 109.10)) AS T(sale_month, sale_amount);

SELECT SUM(sale_amount) AS total_sales
FROM (VALUES('2010-01', 120.50),
             ('2010-02', 214.00),
             ('2010-03', 109.10)) AS T(sale_month, sale_amount);

GO

-- replacement for SET FMTONLY
EXECUTE sp_describe_first_result_set N'CalculateSales';

/*

abbreviated results

is_hidden column_ordinal name         system_type_name  
--------- -------------- ------------ -----------------
0         1              sale_month   varchar(7)            
0         2              sale_amount  numeric(5,2)          

*/

-- use DMV
SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set(N'CalculateSales', 1, 1);

CREATE TABLE Foo (
keycol INT NOT NULL PRIMARY KEY,
datacol CHAR(10));

EXEC sp_describe_undeclared_parameters N'SELECT datacol FROM Foo WHERE keycol = @x';

/*

abbreviated results

parameter_ordinal name  suggested_system_type_name  
----------------- ----- ----------------------------
1                 @x    int                          

*/

GO

DROP TABLE Foo;

DROP PROCEDURE CalculateSales; </br/>

Bonus feature (maybe): FORMATMESSAGE
Application use: format messages (C/C++ sprint style)
Comments: undocumented feature allows to format message that is not in sys.messages

Listing 6: FORMATMESSAGE example
SELECT FORMATMESSAGE('There are %d products in department %s.', 10, 'remodeling');

/*

There are 10 products in department remodeling.

*/ </br/>

You might also like
SQL Server 2005 Service Pack 2
Row Number in SQL Server
SQL Server 2005 Compact Edition
Aggregate Window Functions
Dates and Date Ranges in SQL Server
Prevent Table Drop
SQL Injection
Delighters in SQL Server 2008
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.