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