Transact-SQL Programming
Course Level: Beginner, Intermediate
Duration: 40 Hours
Covers: SQL Server 2005, 2000, 7.0
Benefits:
- Develop complex and robust SQL
queries for SQL Server 7 and 2000
- Develop database applications
for SQL Server 7 and 2000 with Transact-SQL
- Query multiple tables with
inner joins, outer joins and self joins
- Transform data with built-in
functions
- Summarize data using
aggregation and grouping
- Build simple and correlated
subqueries
- Determine the most efficient
SQL solution for application requirements
- Solve real-world business
problems effectively with Transact-SQL
- Query and modify data with
Transact-SQL
- Create and debug stored
procedures
- Take advantage of AFTER and
INSTEAD OF triggers
- Manage concurrency using
transactions and locking options
- Exchange data effectively
using Transact-SQL's support for XML
- Tune database queries for
optimal performance
- Build federated databases
using partitions
Prerequsites:
- Some Knowledge with Databases
Course Outline:
RELATIONAL DATABASE
FUNDAMENTALS
- Database
- Database Objects including
Tables, Rows, Columns, Views, Rules, Defaults, Index, Stored
Procedures, and Trigger etc.
ARCHITECTURAL OVERVIEW OF SQL
SERVER
SQL Server Components
- SQL Server
- SQL Server Agent
- Distributed Transaction
Coordinator
- Microsoft Search
Tools
- SQL Server Enterprise Manager
- SQL Query Analyzer
- SQL Profiler
INTRODUCTION TO SQL
- SEQUEL
- Data Definition Language (DDL)
& Data Manipulation Language (DML)
- ANSI-SQL
- Transact-SQL
GETTING STARTED WITH T-SQL
T-SQL Basics
- Retrieving data with SELECT
- Expressions
- Literals
- Handling Nulls Properly
Retrieving Data
- Querying Tables
- Retrieving Data from a Single
Table
- Transact-SQL conditions and
Expressions
- Dealing with collation
sequences
- Using Scalar & Aggregate
functions
Executing Queries
- Analyzing Query Plans
- Enhancing Query Performance
- Testing Queries
- Selecting best alternatives
- Avoiding Errors and common
pitfalls
QUERYING MULTIPLE TABLES
Implementing Various Types of Joins
- Inner Joins
- Cross Joins
- Left, Right and full outer
Joins
- Equijoins vs. theta joins
- The performance implications
of joins
- Writing Self joins
- Joining a table to itself
- Combining queries with UNION
- Eliminating duplicate results
- UNION ALL
SCALAR AND AGGREGATE FUNCTIONS
- Taking advantage of built-in
functions
- Converting Data Types
- Handling dates
- Manipulating strings
- Choosing the right function
for the job
Summarizing data with aggregate
functions
- COUNT
- SUM
- AVG
- MIN
- MAX
- Managing Nulls
- Suppressing duplicates
Grouping Data
- GROUP BY and GROUP BY ALL
- Applying conditions with
HAVING
- WHERE vs. HAVING
- Nesting grouped aggregates
- Joins and grouping
- COMPUTE and COMPUTE BY
- Introducing subtotals with
CUBE and ROLL UP
BUILDING SUBQUERIES
Simple Subqueries
- Subqueries in conditions and
column expressions
- Creating multilevel subqueries
- Avoiding problems when
subqueries return Nulls
- Handling multi-row subquery
results
- Correlated subqueries
- Accessing values from the
outer query
- EXISTS and IN
DECOMPOSING QUERIES INTO
MANAGEABLE PIECES
- The objective of decomposition
- Creating reusable code
- Improving modularity
- Working around SQL Limitations
- Alternative Strategies
- Derived Table
- Views
- Temporary Tables
- Table Variables
- Table-valued user-defined
functions
SOLVING BUSINESS PROBLEMS WITH
SQL
- Traversing hierarchies
- Pivoting Tables
- Ranking Results
- Calculating moving averages
- Solving time-interval problems
- Finding gaps
- Identifying duplicates
QUERYING HETEROGENEOUS DATA
SOURCES
- Creating Linked Servers
- Querying across database and
instances
- Querying across heterogeneous
data sources
- Performing dynamic distributed
queries
EXECUTING FULL-TEXT SEARCHES
- Creating a full-text index
- Using full-text functions in
SQL
- Taking advantage of proximity
and weighting
MANAGING DATA AND CONCURRENCY
- Modifying records
- Working with INSERT, UPDATE
and DELETE
- Configuring transaction
isolation levels
- Controlling locks with hints
- Working with Transactions
- Concept of a Transaction
- ROLLBACK
- Save Points
DEVELOPING SERVER-SIDE CODE
Programming in Transact-SQL
- Transact-SQL procedural
extensions
- Creating and using local
variables
- Referencing global variables
- Raising and managing errors
- Writing stored procedures
- Passing input and output
parameters
- Examining return values
- Executing remote stored
procedures
- Debugging a stored procedure
Advanced Transact-SQL constructs
- Building and using temporary
tables
- Processing rows on the server
with a cursor
- LOCAL vs. GLOBAL cursors
- Creating user-defined
functions
- Calculating values with scalar
functions
- Solving complex problems with
multiple-statement table-valued functions
- Calling functions from SQL
- Deterministic vs.
non-deterministic
- Binding a function to the
schema
Developing triggers
- INSTEAD OF vs. AFTER triggers
- Detecting row changes using
the inserted and deleted tables
- Coding advanced validation
- Generating an audit trail
- Exploring nested and recursive
triggers
PERFORMANCE TUNING
- Monitoring queries
- Profiling client applications
- Viewing query plans
- Optimizing queries
- Determining appropriate
indexes
- Managing distribution
statistics
- Utilizing optimizer hints
- Improving performance with
partitioned views
INTEGRATING XML WITH
TRANSACT-SQL
- Retrieving XML using HTTP
- Submitting SQL as part of a
URL
- Formatting results with XSL
