Microsoft SQL Server: Transact-SQL Programming

Course Overview

Transact-SQL is integral to the power of SQL Server. Transact-SQL features allow developers to create scalable, distributed applications to meet the demanding requirements of modern organisations. This course provides extensive experience creating stored procedures and triggers, and developing T-SQL that utilises SQL Server to the fullest.

Delegates that attend a Microsoft training course are twice as motivated than those that don't?

Related Courses

• Microsoft SQL Server 2005: Updating your Administration Skills to Microsoft SQL Server 2005
• Microsoft SQL Server 2005: Designing Microsoft SQL Server 2005 Infrastructure and Services
• Microsoft SQL Server 2005: Designing Security for Microsoft SQL Server 2005
• Microsoft SQL Server 2005: Designing a Reporting Solution Archicture using MS SQL Server 2005
• Microsoft SQL Server 2005: Implementing a SQL Server Database (5 day)
• Microsoft SQL Server 2005: MCITP Developer: Designing Server -side Solutions
• Microsoft SQL Server 2005: MCITP Developer: Designing Databases
• Microsoft SQL Server 2005: MCITP Developer: Tuning and Optimising Queries
• Microsoft SQL Server 2005: MCITP Administrator: Designing High Availability Solutions
• Microsoft SQL Server 2005: MCITP Administrator: Administering and Automating Databases and Services

Audience

Database developers, administrators and analysts who want to expand their knowledge of SQL Server 2005 or 2008. Experience with SQL Server at the level of Course 131, "SQL Server 2005 Comprehensive Introduction", or Course 137, "SQL Server 2008 Comprehensive Introduction", is assumed.

Skills Gained

You will learn to:

• Develop database applications for SQL Server databases with Transact-SQL
• Create modular code using stored procedures
• Develop reusable code with scalar and table-valued functions
• Handle Transact-SQL runtime errors to create robust software
• Audit data changes using AFTER triggers
• Manage concurrency using transactions and locking options

Course Outline

SQL Server Architecture

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

Managing Tables with DDL

Creating schemas

• Managing schemas
• Referencing schemas versus using the default schema
• Hiding schemas with synonyms

Building 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

Batch and stored procedure processing

• Minimising network traffic using batches and procedures
• Stored procedure compilation and execution
• Using scalar and aggregate functions

Selecting data

• Developing stored procedures that extract data from multiple servers
• Executing dynamic queries using OPENROWSET and OPENQUERY
• Developing recursive queries with common table expressions (CTE)
• Executing remote procedures
• 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 and distributed transactions
• Managing concurrency with isolation levels
• SQL Server locking fundamentals
• Avoiding blocking problems with read-committed snapshot isolation
• Managing locks using hints

Programming procedural statements

• Implementing conditions with IF...ELSE
• Looping with WHILE and GOTO
• Creating code blocks with BEGIN...END
• Debugging with PRINT
• Returning data using RETURN
• Debugging T-SQL in Visual Studio

Handling errors

• Communicating problems to the client with RAISERROR
• Intercepting errors with TRY...CATCH
• Dealing with open transactions when an exception occurs

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

Storing queries on the server

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

Creating user-defined functions

• Calculating values with scalar functions
• Processing multiple rows returned from a table-valued function
• Taking advantage of schema binding

Formulating triggers

• INSTEAD OF vs. AFTER triggers
• Detecting row changes using the inserted and deleted tables
• Tracing metadata changes with DDL triggers
• Auditing user access using a LOGON trigger
• Tracking data changes with the OUTPUT clause