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 TRYBEGIN 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
ASSELECT 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);GOEXECUTE 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
ASSELECT 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 */ GODROP 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/> |
Leave a Reply
Want to join the discussion?Feel free to contribute!