7a advanced tsql
•Download as PPT, PDF•
1 like•453 views
This document outlines the agenda for an Advanced TSQL training course. It covers enhancing data manipulation language functionality with output clauses and merge statements, managing transactions both implicitly and explicitly, exception handling with try/catch blocks, and extending SQL Server functionality with common table expressions, XML, CLR, FileStream, spatial data, full text search, and service broker. Additional topics include apply operators, pivoting and unpivoting data, and ranking functions.
![Managing Transaction
ï‚—Implicit Transaction
ï‚—Setting mode for implicit transaction
 Manual Commit – Set Implicit_Transactions OFF
 Auto commit – Set Implicit_Transactions ON
ï‚—Explicit Transaction
ï‚—Begin Transaction [Trans Name]
ï‚—Commit Transaction [Trans Name]
ï‚—Rollback Transaction [Trans Name]
Savepoint – Save Transaction [Savepoint name]](https://image.slidesharecdn.com/7a-advancedtsql-130128181440-phpapp02/85/7a-advanced-tsql-9-320.jpg)
![Exception Handling (Try..Catch)
ï‚—Captures Error severity higher that 10
Try…Catch TSQL
Begin Try
[SQL Statements]
End Try
Begin Catch
[SQL Statements]
End Catch](https://image.slidesharecdn.com/7a-advancedtsql-130128181440-phpapp02/85/7a-advanced-tsql-10-320.jpg)
7a advanced tsql
- 1. Advanced TSQL SQL Server 2008
- 2. Agenda Enhancing DML Functionality Output Clause Merge Statement Managing Transactions Implicit Transaction Explicit Transaction Exception Handling Try…Catch
- 3. Agenda ï‚—Extending SQL Server Functionality ï‚—CTE ï‚—XML ï‚—CLR ï‚—FileStream ï‚—Spatial ï‚—Full Text Search ï‚—Service Broker
- 4. Agenda ï‚—Apply Operator ï‚—Cross Apply ï‚—Outer Apply ï‚—Pivoting Data ï‚—PIVOT ï‚—UNPIVOT ï‚—Ranking Functions ï‚—ROW_NUMBER ï‚—RANK ï‚—DENSE_RANK ï‚—NTILE
- 5. Output Clause ï‚—Provides ability to access INSERTED and DELETED tables during DML statement execution as we can access in Triggers.
- 7. Merge Statement ï‚—Provides you the ability to compare rows between source and target tables, and then performs DML action accordingly only on target Table.
- 9. Managing Transaction Implicit Transaction Setting mode for implicit transaction  Manual Commit – Set Implicit_Transactions OFF  Auto commit – Set Implicit_Transactions ON Explicit Transaction Begin Transaction [Trans Name] Commit Transaction [Trans Name] Rollback Transaction [Trans Name] Savepoint – Save Transaction [Savepoint name]
- 10. Exception Handling (Try..Catch) Captures Error severity higher that 10 Try…Catch TSQL Begin Try [SQL Statements] End Try Begin Catch [SQL Statements] End Catch
- 11. Retrieving Error Information ï‚— ERROR_NUMBER() returns the number of the error. ï‚— ERROR_SEVERITY() returns the severity. ï‚— ERROR_STATE() returns the error state number. ï‚— ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred. ï‚— ERROR_LINE() returns the line number inside the routine that caused the error. ï‚— ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
- 13. CTE ï‚— Like derived tables ï‚— Iterates results sets ï‚— Recursive ï‚— CTE Parts ï‚— With Clause ï‚— CTE Name ï‚— Column Names ï‚— AS keyword ï‚— Anchor Query ï‚— Union All ï‚— Second Query ï‚— Outer Query
- 15. PIVOTING DATA ï‚—Moving rows for a column as columns for aggregate values, reversing that combination as well through unpivot ï‚—Parts ï‚—Source Table ï‚—PIVOT/UNPIVOT Operator ï‚—Aggregation ï‚—Filter Clause (FOR) ï‚—Pivot Table
- 16. Pivot Syntax From (Subquery) AS Source_Table PIVOT ( Aggregate_Function(Column_ST) FOR Column_ST IN (Values in Column_ST) ) AS Pivot_Table
- 17. Apply Operator ï‚—Outer Apply ï‚—Cross Apply