SQL Server 2005: Transact-SQL Programming

Course Overview

The development features offered in SQL Server 2005 make this database management system one that fully supports the requirements of small-scale or large-scale organisations. Developers using Transact-SQL, .NET and XML have the potential to build truly flexible, scalable and robust applications that meet today's business demands.

This course provides experience creating procedural code in T-SQL and .NET. You also gain the skills needed to develop XML-based messaging applications.

Audience

This course is valuable for those working with or moving to SQL Server 2005. Course 131, " SQL Server 2005 Introduction ", or equivalent experience is assumed. Those with a developer background in SQL Server 7 or 2000 should take Course 130, " SQL Server 2005 Programming: Skills Upgrade ".

Skills Gained

• Develop database applications for SQL Server 2005 with Transact-SQL and .NET
• Create and alter database table definitions using DDL
• Create stored procedures in Transact-SQL
• Handle Transact-SQL runtime errors
• Access data with user-defined functions and views
• Retrieve and manipulate XML data
• Develop functions in .NET CLR

Course Outline

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
• Capturing the return value
• Interrogating global variables

Calling built-in scalar functions

• Converting data using CAST and CONVERT
• Manipulating dates using DATEPART
• Ordering data with ranking functions
• Encrypting and decrypting data

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
• Applying best practices

Extending SQL Server with .NET

• Developing aggregates for enhanced functionality
• Augmenting SQL Server with .NET data types

SQL Server 2005: Transact-SQL Programming / SQ05-TP