SQL Server Querying and Programming

THE COURSE
The course will allow delegates to understand and use Microsoft SQL (Transact SQL) for both querying and programming in a Microsoft SQL Server environment. The course is based on SQL Server 2008 but is also suitable for Microsoft SQL Server 2005 (and 2000). The topics covered will include data types, joins, subqueries, indexes, views and table-valued parameters. The course will include lectures, demonstrations and exercises to consolidate the topics covered. Each delegate will have their own Microsoft SQL Server 2008 server for the course.

WHO SHOULD ATTEND

Database developers and support personnel who need to be able to query and programme Microsoft SQL Server databases using Transact SQL.

PRE-REQUISITES

Delegates must have a good working knowledge of relational databases. This can be obtained by attending our Microsoft SQL Server Essentials course. Delegates must have a basic knowledge of Microsoft Windows

COURSE TOPICS
INTRODUCTION TO DATABASES
Database Basics
Normalization
Data Types
Using SQL Server Management Studio
Using Enterprise Manager & Query Analyzer
SQL Batches and the GO Command
Database Diagrams
Designing a Database

QUERYING AND UPDATING THE DATABASE
Selecting Data
Selecting Columns from a Table
Inner, Outer and Cross Joins
Union, Intersect and Except
Updating, Inserting and Deleting Data
Merging Data in SQL Server 2008
Capturing & Tracking Changes in SQL Server 2008

CREATING A DATABASE
Creating & Altering Tables
Creating and Updating a Table with Transact SQL
Dropping Tables

PROGRAMMING WITH TRANSACT SQL
Datatypes and Variables
Stored Procedures
Controlling Flow of Code
User Defined Functions

ADVANCED QUERYING
Subqueries
Grouping and Summaries
Views
Common Table Expressions
Pivot Tables
The Output Clause

OPTIMISATION
Designing Indexes
Creating an Index
Creating an Index on a View
Viewing the Execution Plan
Putting the Performance Tools to Use

ADVANCED PROGRAMMING WITH TRANSACT SQL
Temporary Tables
Declaring a Variable with the Table Datatype
Dynamic SQL
Cursors

INTRODUCTION TO SQL SERVER SUPPORT FOR XML
Introduction to XML
XML Casting
XML Data Type Methods (SQL Server 2008 and 2005)

DATA INTEGRITY
Error Handling
Try... Catch Block
Transactions and Locks
Triggers
Triggers in Management Studio Triggers in Enterprise Manager

REAL WORLD DEVELOPMENT
Writing SQL for Reporting Tools
Troubleshooting
Other SQL Server 2005 Technologies
Best Practices