Monday, September 29, 2014

1 T-SQL Improvement in SQL 2012

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

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

image

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 

image

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;

image

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;
image

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.

image

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

image

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)

image

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

image

Reference

1 comments:

  1. Is there any new competitions in which I can take a part? I missed these ones that you told us about and I want to find out more about upcoming. Send me every single information you have. Look for me on http://www.camsloveaholics.com/latina if I don't answer or just go with me.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates