Advanced Programming and Query Tuning in SQL Server

Basic Querying Techniques

• Query Logical Order
• Joining Multiple Tables
• Grouping and Sorting
• Using Sub-Queries
• System Functions (String Functions, Date Functions, etc.)
• UNION, INTERSECT, and EXCEPT

Database Design

• Data Types Best Practices
• Row Identifiers
• Table Constraints
• Temporary Tables vs. Table Variables
• Common Table Expressions
• Partitioning
• Memory-Optimized Tables

Understanding Indexes

• Rowstore vs. Columnstore Indexes
• How to Design Indexes
• Composite Indexes
• Filtered Indexes
• Covering Indexes
• Missing Indexes and Redundant Indexes
• Guidelines for Writing Efficient Queries

Understanding Statistics

• Getting to Know Statistics
• What is the Cardinality Estimator?
• Multi-Column Statistics
• Filtered Statistics
• Scans, Seeks and SARGability

Query Processor Internals

• The Plan Cache
• Compilation-Execution Sequence
• How Execution Plans are Made?
• Recompilations
• Parameterization
  • Simple vs. Forced
  • Skewed Data Distribution
  • Parameters vs. Local Variables
  • Changing Parameter Values

Fundamentals of Query Tuning

• How to Read and Analyze Execution Plans?
• Problems in Execution Plans
• Computed Columns
• Implicit Conversions
• Memory Grants
• Query Parallelism
• Live Query Statistics

Advanced Programming Techniques

• Ranking Functions and Window Functions
• Implementing Query Paging
• Pivoting and Unpivoting Techniques
• Grouping Sets
• The MERGE Statement
• Error Handling
• Working with Hierarchies

Programming Objects Best Practices

• Views
• User-Defined Functions
• Stored Procedures
• Triggers
• Dynamic SQL

Transactions and Locks

• Transactions Overview
• Lock Types
• Concurrency Issues
• Transaction Isolation Levels
• Locking Hints
• Deadlocks
• Nested Transactions

Students should be familiar with basic programming and with the syntax of Transact-SQL.