2.1 TSQL

1
2.1 TSQL ?????
2
Overview

• Data Definition Language Enhancements
• Data Manipulation Language Enhancements
• Structured Exception Handling

3
Data Definition Language Enhancements

• Enhanced Data Definition Language Statements
• Enhanced Data Types
• What Is a Partitioned Table?
• How to Partition a Table
• Enhanced Indexing Features

4
Data Definition Language Enhancements

• Database DDL statements
• Database mirroring, Database snapshot
• Table DDL statements
• Enhanced data type, persisted computed column
• .NET Framework programming
• Create Stored procedure, trigger, function, type
  in managed .NET assemblies
• Partitioned tables and indexes
• Indexing enhancements
• Alter Index Disable/Rebuild/Reorganize

5
Data Definition Language Enhancements

• Security DDL statements
• Create Login, Create Role, Create User
• Service Broker statements
• DDL triggers and event notifications

6
Enhanced Data Types

• Large value data types
• varchar(max)
• nvarchar(max)
• varbinary(max)
• xml stores XML data with optional schema

7
What Is a Partitioned Table?

• Table data distributed across multiple file
  groups
• Improved performance on multiprocessor/multidrive
  systems
• Easier management of large tables

8
How to Partition a Table
9
Lab Partitioning Data

• Create a partition function
• Create a partition scheme
• Create a partitioned table
• Retrieve partition information

10
Data Manipulation Language Enhancements

• How to Use the OUTPUT Keyword
• What Are Common Table Expressions?
• How to Use a Common Table Expression
• How to Perform Recursive Queries with Common
  Table Expressions
• What Are the PIVOT and UNPIVOT Operators?
• What Is the APPLY Operator?
• What Are Ranking Functions?

11
How to Use the OUTPUT Keyword
DECLARE _at_InsertDetails TABLE (ProductID int,
InsertedBy sysname) INSERT INTO
Stock.ProductList OUTPUT inserted.ProductID,
suser_name() INTO _at_InsertDetails VALUES ('Racing
Bike', 412.99) SELECT FROM _at_InsertDetails
12
Lab Using the OUTPUT Keyword

• In this practice, you will use the OUTPUT keyword
  to retrieve information about a data modification
  into a table-valued variable

13
What Are Common Table Expressions?

• Result set can be used in SELECT, INSERT, UPDATE,
  or DELETE
• Advantages of common table expressions
• Queries with derived tables become more readable
• Provide traversal of recursive hierarchies

WITH TopSales (SalesPersonID, NumSales) AS (
SELECT SalesPersonID, Count() FROM
Sales.SalesOrderHeader GROUP BY SalesPersonId
) SELECT FROM TopSales WHERE SalesPersonID IS
NOT NULL ORDER BY NumSales DESC
14
How to Use a Common Table Expression
15
Lab Using a Common Table Expression

• In this practice, you will create a common table
  expression

16
How to Perform Recursive Queries with Common
Table Expressions
Modify CTE SELECT query when creating CTE
17
Lab Common Table Expressions

• In this demonstration, you will see how to create
  a recursive common table expression

18
What Are the PIVOT and UNPIVOT Operators?

• PIVOT converts values to columns

SELECT FROM Sales.Order PIVOT (SUM(Qty) FOR
Prod IN (Bike,Chain)) PVT

• UNPIVOT converts columns to values

SELECT Cust, Prod, Qty FROM Sales.PivotedOrder
UNPIVOT (Qty FOR Prod IN (Bike,Chain)) UnPVT
19
Pivot

• SELECT FROM
• (SELECT Year(OrderDate) as OrderYear,
  Month(OrderDate) as OrderMonth, OrderQty FROM
  Sales.SalesOrderHeader as H Join
• Sales.SalesOrderDetail as D
• on H.SalesOrderIDD.SalesOrderID) AS ORD
• PIVOT
• ( SUM(orderqty) FOR OrderMonth IN (1, 2,
  3, 4, 5, 6, 7, 8, 9, 10, 11,
  12) )AS PVT
• ORDER BY OrderYear

20
Unpivot

• SELECT OrderYear, OrderMonth, orderqty
• FROM Sales.PivotedOrder
• UNPIVOT (OrderQty FOR OrderMonth IN
  (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,
  Sep,Oct,Nov,Dec)) AS UnPVT

21
What Is the APPLY Operator?

• Invokes a table-valued function once per row
• CROSS APPLY only rows with matching function
  results
• OUTER APPLY all rows, regardless of matching
  function results

SELECT Name AS Customer, MR. FROM Sales.Store
CROSS APPLY Sales.MostRecentOrders(CustID) AS MR
CREATE FUNCTION Sales.MostRecentOrders (_at_CustID
AS int) RETURNS TABLE AS RETURN SELECT TOP(3)
SalesOrderID, OrderDate FROM Sales.SalesOrderHea
der WHERE CustID _at_CustID ORDER BY OrderDate
DESC
22
Lab Using the APPLY Operator

• In this practice, you will use APPLY in a query

23
What Are Ranking Functions?
24
Duplicates and ties
SELECT orderid, customerid, ROW_NUMBER()
OVER(ORDER BY customerid) AS num, RANK()
OVER(ORDER BY customerid) AS rank,
DENSE_RANK() OVER(ORDER BY customerid) AS
denserank, NTILE(5) OVER(ORDER BY
customerid) AS ntile5 FROM orders WHERE orderid
lt 10400 AND customerid lt 'BN'
orderid customerid num rank denserank
tile5 ----------- ---------- ------ ------
--------- ------ 10308 ANATR 1 1
1 1 10365 ANTON 2 2
2 1 10355 AROUT 3 3
3 2 10383 AROUT 4 3
3 2 10278 BERGS 5 5
4 3 10280 BERGS 6 5
4 3 10384 BERGS 7 5
4 4 10265 BLONP 8 8
5 4 10297 BLONP 9 8
5 5 10360 BLONP 10 8
5 5
25
Lab Using Ranking Functions

• In this practice you will
• Use the RANK function
• Use the DENSE_RANK function
• Use the ROW_NUMBER function
• Use the NTILE function

26
Structured Exception Handling

• What Is Structured Exception Handling?
• How to Use TRYCATCH Constructs

27
What Is Structured Exception Handling?

• TRYCATCH blocks provide the structure
• TRY block contains protected transactions
• CATCH block handles errors
• Eliminates need for "IF _at__at_error" code
• Requires XACT_ABORT ON
• Transaction remains in "doomed" state until
  explicitly rolled back
• _at__at_error available as first statement in CATCH
  block
• Use "RAISERROR WITH TRAN_ABORT" to explicitly
  doom transaction

28
How to Use TRYCATCH Constructs
29
Lab Using Structured Exception Handling

• In this practice, you will use structured
  exception handling in a stored procedure