1 T-SQL Improvement in SQL 2012

This article is summarize what I have learn from 2 Virtual Lab from Microsoft .

• VL: What's new for T-SQL in SQL Server 2012
• Sequences
• PAGING windows in T-SQL
• Exception Handling in SQL Server 2012
• New Functions in SQL Server 2012
• VL: SQL Server 2012: Exploring Transact-SQL Improvements in SQL Server 2012
• Creating and Using Sequence Numbers
• Querying Data Using the Offset/Fetch syntax
• Raising Exceptions Using the Throw Statement
• Discovering Stored Procedure Metadata
• Simplifying T-SQL Queries using OVER Clause Windowing

SEQUENCE

• SEQUENCE cross multiple tables
• SEQUENCE with cycle
• SEQUENCE in the result set
• RESET SEQUENCE

SEQUENCE cross multiple tables

--- SEQUENCE 

CREATE DATABASE TSQLDB;

CREATE TABLE Employees ( EmployeeId INT NOT NULL PRIMARY KEY,     Name NVARCHAR(255) NULL ); 

CREATE TABLE Contractors ( ContractorId INT NOT NULL PRIMARY KEY, Name NVARCHAR(255) NULL ); 

USE TSQLDB; 

CREATE SEQUENCE IdSequence AS INT     START WITH 10000    INCREMENT BY 1; 

INSERT INTO Employees (EmployeeId, Name)    VALUES (NEXT VALUE FOR IdSequence, 'Jane'); 

INSERT INTO Contractors (ContractorId, Name)    VALUES (NEXT VALUE FOR IdSequence, 'John'); 

SELECT * FROM Employees; 

SELECT * FROM Contractors; 

--- Reset sequence

ALTER SEQUENCE IdSequence

RESTART WITH 1 ;

select NEXT VALUE FOR IdSequence

SEQUENCE with cycle

CREATE SEQUENCE CountBy5 AS tinyint  START with 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 CYCLE;

SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects 

SEQUENCE in the result set

USE [AdventureWorks2014] ;

CREATE SCHEMA Samples ;

CREATE SEQUENCE Samples.IDLabel AS tinyint START WITH 1 INCREMENT BY 1 ;

SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber 

FROM Production.Product WHERE Name LIKE '%nut%' ;

-- RESET The Lab

DROP SEQUENCE  Samples.IDLabel;

DROP SCHEMA Samples;

Paging window

This is new feature in T-SQL, according to Wikipedia , Pagination is the way to dividend the content. Before Offset/Fetch, we have to write the complicate query to do the same thing. Now it much simpler.

• Offset: How many rows from beginning, it needs to skip
• Fetch: How many rows it needs to retrieve. 

Use [AdventureWorks2014]

DECLARE @current_offset INT = 5; 

DECLARE @offset_increment INT = 10; 

SELECT 

    ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS 'ROW NUMBER',

    BusinessEntityID, 

    PersonType, 

    FirstName 

FROM  Person.Person

ORDER BY BusinessEntityID 

OFFSET @current_offset ROWS 

FETCH NEXT @offset_increment ROWS ONLY;

Exception Handling in SQL Server 2012

CREATE DATABASE TSQLDB;

USE TSQLDB;

create table dbo.testThrow (

    ID int PRIMARY KEY

);

-- Use CATCH 

BEGIN TRY    

    INSERT dbo.testThrow(ID) VALUES(1); 

-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.   

    INSERT dbo.testThrow(ID) VALUES(1); 

END TRY 

BEGIN CATCH     

    PRINT 'Catch Block.'; 

END CATCH; 

-- Throw

BEGIN TRY    

    INSERT dbo.testThrow(ID) VALUES(1); 

-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.   

    INSERT dbo.testThrow(ID) VALUES(1); 

END TRY 

BEGIN CATCH     

    PRINT 'Catch Block.'; 

    THROW;

END CATCH; 

The first statement will catch the error but it wont display the actually error message because it has been catch.

After we add the THROW, it will also display the actual error message

Msg 2627, Level 14, State 1, Line 18
Violation of PRIMARY KEY constraint 'PK__testThro__3214EC2789EB1AE6'. Cannot insert duplicate key in object 'dbo.testThrow'. The duplicate key value is (1).

Discovering Stored Procedure Metadata: sys.dm_exec_describe_first_result_set_for_object

sys.dm_exec_describe_first_result_set_for_object display the metadata for the store procedure .

use [AdventureWorks2014]

GO

select * from 

sys.dm_exec_describe_first_result_set_for_object (object_id('[dbo].[uspGetManagerEmployees]','P'),1)

Simplifying T-SQL Queries using OVER Clause Windowing

The FirstOder column display the date for specific customer first time to buy the product ( OrderDate).

USE [AdventureWorks2014] 

GO

SELECT CustomerID, 

    OrderDate,

    SalesOrderNumber,

    FIRST_VALUE(OrderDate) 

        OVER (PARTITION BY CustomerID ORDER BY OrderDate ASC

        ROWS UNBOUNDED PRECEDING) AS FirstOrder      

FROM Sales.SalesOrderHeader         

ORDER BY CustomerID, OrderDate, SalesOrderNumber

Reference

• http://www.sqlshack.com/sql-server-2012-introduction-pagination/
• sys.dm_exec_describe_first_result_set_for_object:: http://msdn.microsoft.com/en-us/library/ff878236.aspx
• OVER Clause: http://msdn.microsoft.com/en-us/library/ms189461.aspx