Introduction
The course focuses on writing advanced queries and tuning code in SQL Server. In this course you will learn the details and capabilities of TSQL including Data types issues Advance Select Queries Querying Multiple Tables Using Joins And Sub Queries Aggregating Data Temporary Objects Views Triggers Stored Procedures Functions Cursors and Dynamic Execution Data Manipulation CLR Integration Tuning SQL Server Security issues and Advance Administration.
Along the course you will learn how to use TSQL to solve practical problems such as Relational Division Time series TOP Problems Handling Sequences Identifying Groups Missing Numbers Trends Ranking Paging Pivoting Sampling Custom Aggregations CTE Regions Histogram maintain and query hierarchies in SQL Server.
Audience
TSQL Programmers Architects Analysts and DBAs those that need to write or review TSQL code in SQL Server.
Prerequisites
The course assumes at least one year of programming experience inSQL Server mainly in basic to moderate TSQL querying and codewriting.
Course Outline
Module 1 TSQL Data types and Storage Options
Characters
Varchar vs. Char Unicode vs. NonUnicode
Set Options
String Functions
Collations
Numerics
Integers Exact numbers Round numbers Monetary
Identity Property
Datetime
Internal Structure
Datetime Functions
Binary
Binary Varbinary
Bit Bitmaps
Lob Large Objects
VarcharMax NVarcharMax VarBinaryMax
FileStream
Spatial Data
Geography
Geometry
Timestamp Rowversion
Sqlvariant
Guid UniqueIdentifier
Table Variable UserDefined Table Type
XML
Sparse Columns and Column Sets
Compressed Tables and Indexes
Module 2 Select Statement
Compound Operators
Sub queries
Sub query as an Expression
Sub query to Correlate Data
Derived Table CTE
Relational Division
Top n
Between
Exists In
Ranking
Sampling Paging
Case
Sorting
Union Union all
Intersect Except
Null Issues
Module 3 Joins
Inner Outer Cross
Semi joins
Self join
Join more then 2 tables
Physical Implementations and tuning
Nested
Merge
Hash
Join Hints
Module 4 Aggregations
Rollup Cube
Grouping Sets
Grouping GroupingId Functions
Pivot Unpivot
Clipping
Cumulative aggregation Sliding aggregation
Histograms
Module 5 Data manipulation
Insert
Insert select
Insert exec
Row Constructors
Identity insert
Select into
Insert and duplicate Rows
Insert performance tips
Update
Update with Case
Update using join
Swap Columns
Update and variables
Delete
Delete using join
Delete Duplicate rows
Truncate
Data manipulation with TOP
Merge
OUTPUT Clause
Module 6 Views
Overview
Restrictions
Updateable views
Dynamic Views
Index Views
Partitioned Views
View Options
InformationSchema Views
Module 7 Stored Procedures
Overview
Creation Execution Compilation
Environment Considerations
Parameters Output Parameters Return Code
TableValued Parameters
Debugging Error handling
Startup Procedures
Nesting Recursion
System Extended Special Procedures
Miscellaneous Security Issues
Module 8 Triggers
Overview
Inserted And deleted Virtual Tables
After Trigger
Update issues
Determine which statement fires the trigger
Determine number of affected rows
Instead of trigger
Trigger on View
DDL Triggers Login Triggers
Module 9 Functions
Overview
Function Restrictions
Scalar Function
Inline Function
Multi statement Function
CROSS OUTER APPLY
Function Tricks
Module 10 CLR Integration
Overview
Assemblies
CLR Routines
Module 11 Transactions Locks Deadlocks
Overview
Type of transactions
Save point
Isolation levels
Type of locks
Deadlocks Issues
Lock Hints
Locks related Dynamic management View Functions
Module 12 Hierarchies
CTE Common Table Expression
hierarchyid Data Type
Simple hierarchies
Multilevel hierarchies
Listing Leaf node only
Sorting hierarchies
Module 13 Tuning
Database design tips
General performance tips
Hardware design
Indexes architecture considerations and tricks
Clustered
NonClustered
Include
Missing
Filter
DML performance tips
SARGs search arguments
Demoralization
Join optimization
Grouping optimization
Stored procedures
Partitioning
Query analysis
Set options
Graphical execution plans
Database tuning advisor
Query hints
Profiler
Caching Plan Guides
Resource Governor
Module 14 Cursors Dynamic execution and Temporary objects
Cursor Overview
Type of cursors
Cursor Options
Extract values from cursor
Cursor scope Global vs. local cursor
Updating cursors
Exec command
Spexecutesql
Spmsforeachdb
Spmsforeactable
Tempdb Database
Local vs. global temporary objects
Temporary tables vs. table variable
Performance considerations
Module 15 Security and advance administration if time permits
SQL Injections
Scripting with Stored procedures
Tracing Using Stored Procedures
Undocumented DBCC system functions Stored Procedures catalog views dynamic management function