SQL Server 2005 Architecture

• SQL Server 2005 edition overview
• Introducing the new tools
• SQL Server Management Studio

Managing Tables with DDL

Creating tables

• Selecting appropriate SQL Server data types
• Constructing tables with CREATE TABLE

Adding constraints

• Enforcing uniqueness using PRIMARY KEY and UNIQUE constraints
• Validating relationships using FOREIGN KEY

Retrieving Data with Transact-SQL Stored Procedures

Selecting data

• Developing stored procedures that extract data from multiple servers
• Executing remote procedures
• Developing recursive queries with common table expressions (CTE)
• Producing cross tabulations using PIVOT

Declaring variables and parameters

• Creating and utilising local variables
• Passing input and output parameters
• Interrogating global variables

Calling built-in scalar functions

• Converting data using CAST and CONVERT
• Ordering data with ranking functions

Maintaining Data

Modifying data

• Inserting, updating and deleting data
• Ensuring data consistency with transactions
• Managing concurrency with isolation levels
• SQL Server locking fundamentals
• Avoiding blocking problems

Programming procedural statements

• Implementing conditions with IF...ELSE
• Looping with WHILE and GOTO
• Creating code blocks with BEGIN...END
• Debugging with PRINT

Handling errors

• Communicating problems to the client with RAISERROR
• Intercepting errors with TRY...CATCH

Producing server-side result sets

• Building and using temporary tables
• Processing rows on the server with a cursor
• Taking advantage of table variables

Developing Views, Functions and Triggers with Transact-SQL

Storing queries on the server

• Concealing complexity with views
• Solving business problems using multistatement table-valued functions

Defining computations

• Calculating values with scalar functions
• Calling functions from SQL

Formulating triggers

• INSTEAD OF vs. AFTER triggers
• Detecting row changes using the inserted and deleted table
• Tracing metadata changes with DDL triggers

Querying and Storing XML

Exchanging XML data

• Extending queries with FOR XML
• Using PATH and TYPE to create nested XML

Leveraging XQuery capabilities

• Filtering rows based on XML elements
• Extracting data from an XML column

Integrating .NET CLR with SQL Server

• Developing procedural code in .NET
• Contrasting Transact-SQL with the rich .NET programming model
• Building Common Language Runtime procedures, functions and triggers

Performance Tuning

• Viewing query plans
• Determining appropriate indexes
• Utilising optimiser hints