SQL Server 2005: Transact-SQL Programming

Show more Microsoft SQL Server Training Courses

About this class

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.

Who will the lesson benefit?

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 ".

What delegates will learn

• 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

Contents of this class

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

(C) AquinasTraining.co.uk - Contact Us