Advanced SQL for Microsoft SQL Server

Advanced SQL for Microsoft SQL Server Length: 5 Days Prerequisites: SQL syntax: SELECT, INSERT, UPDATE, DELETE: Recommended: " Introduction to Microsoft SQL Server" PRESENTER: Jeffrey Garbus, Author of over 17 books Email: [email protected] Overview Soaring Eagle presents "Advanced SQL Optimization for Microsoft SQL Server," to DBAs, System Administrators and Client Application Developers. Attendees will learn how MS SQL Server processes queries. Learn to identify problematic statements and queries in an application design before they are implemented, saving time and money. Students will estimate query performance on paper and use those estimates to identify implementation issues with queries. This is a performance and tuning class for developers. Key Benefits: When you complete this course you learn how to write efficient SQL Statements, understand when and how to rewrite a query. You will be able to recognize performance issues and understand how stored procedures function.

Audience: Developers and Database Administrators who will be writing queries to be used inside applications.

Outline Chapter 1. Fundamentals of Query Processing Query Processing Steps Possible Bottlenecks Define The Basic Steps in Query Processing Chapter 2. Query Troubleshooting Showplan_All & Showplan_text Interpreting Showplan Logical and Physical Operators Set Statistics IO Set Statistics Time The Query Analyzer Chapter 3. SQL Statement Processing General Statement Processing Steps Query Optimization Steps SQL Statements Query Tracking Select with Aggregate Where Clause Processing Order by Processing Group By Aggregate Function Processing Bookmark Lookup Clustered Index Seek

Understand Worktables Chapter 4. Problem Queries Scenarios and Resolutions Chapter 5. Single -Table Optimization SQL Server Search Techniques Table Scans Index Selection Optimizer Selection Criteria SARG Matching Index Types Clustered & Non Clustered Index Mechanism Using Indexes Index Limitations Primary Key vs Clustering and Nonclustering Key/index Features Optimizer Selection Criteria OR Indexing OR Query Plan Index Covering Composite Indexes When and How to Force Index Selection Index Statistics Chapter 6. Join Processing Joins Optimization Optimizer Tips Join Resolution Nested Loops Query Plan for Nested Loops Nested Iteration Costs Sample Cost Calculation Hash Joins Merge Joins Force Plan Self Joins Using Temporary Tables Outer Joins Problems with TSQL Outer Joins Chapter 7. Subqueries Categorization Expression Quantified Predicate Correlated Subqueries Processing & Flattening Subqueries vs Joins Use with ANY, ALL, IN, NOT IN, EXISTS & NOT EXISTS Chapter 8. Datatype Conversion & Conflicts Table Design Issues Data Hierarchy

Implicit & Explicit Datatype Float & Real Conversion Styles Money Datatypes Conversion Styles Chapter 9. Views Performance Materialization Group By Views Uses of Views including Views for Security Modifying Data Through a View View with Check Option Calculated Columns Indexed Views Chapter 10. Union Queries Standard Union Query Processing Union Features Union Restrictions Merge Join (Union) Operator Concatenation Operator, Why Use Union Chapter 11. Cursors Row vs Set Processing Performance Issues Locking TSQL Cursor Types Declaring Cursors Nested Cursors Open Cursors Fetch Multi-table Cursors Acquiring Locks Chapter 12. Stored Procedures Coding Standards & Conventions Optimization Returning Procedure Status Error Handling Recompiling Alternatives to Recompile Executing with Parameters Return Status & Parameter Techniques Calling Stored Procedures from Transactions Called or Stand Alone Procedure in Template Using Temp Tables in Stored Procedures Indexes on Temporary Tables Chapter 13. BLOBs Text and Image Data READTEXT, WRITETEXT & UPDATE Text Pointers Drawbacks of BLOBs

Alternatives to BLOBs VARCHAR (MAX) Chapter 14. Full Text Search What is a Full Text Search Configuring A Database for a Full Text Search Registering Tables for Full Text Search Activation of the Full-Text Index Verifying Full Text Index Full Text Query Proximity Search Variation Search Weighted Search Freetext Chapter 15. Session Configuration Session Configuration Set Statement Set Datefirst & Set DateFormat Set Deadlock Priority Set Lock_Timeout Set Concat_Null_Yields Set Cursor_Close_On_Commit Set Fips_Flagger Set Identity_Insert Set Language Set Arithabort Set Fmtonly SetNocount Set ANsi_Defaults Set Showplan Set Forceplan Set Statistics Default Configuration Options View User Configuration Options Chapter 16. Nulls What is a Null Null Truth Tables Issues with Nulls Four Value Logic Chapter 17. Advanced Select Options CASE CUBE ROLLUP TOP Chapter 18. Dynamic Queries Pros & Cons More Complex Queries Tables Getting Started

Select List WHERE & FROM Clauses Chapter 19. Common Table Expressions Write Recursive Table Expressions to walk join and self-join Hierarchies Chapter 20. Specific SQL Problems, Solutions & Issues Writing Cross-Tab Reports Get Grouping Working Determinant Functions Vector Data Distributing Data & Datatypes Fast Random Population techniques