SQL Server 2011 Archives

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/>

Tag Archive for: SQL Server 2011

T-SQL Enhancements in SQL Server 2011 (CTP1)

SITE MAP

SEARCH