Pre-requisites

To get the most out of the Microsoft SQL Server 2005 training course, you should have a solid understanding of relational databases. No particular programming experience is required, but the course is taught from a developer's perspective.

In this SQL Server 2005 training course, you’ll learn about the features that are available in SQL Server 2005; how to design and create a database; and how to build basic queries using Transact-SQL, the language of SQL Server 2005. Then, you'll learn how to build effective views, stored procedures, triggers, and user-defined functions, using Transact-SQL. You'll learn about the new enhancements to the Transact-SQL programming language including improved support for error handling and hierarchical queries, and programmers can now use .NET languages like C# and Visual Basic to build database objects.

SQL Server 2005 includes a rich set of tools that go beyond the basics of querying and manipulating data. You'll learn how to take advantage of the new, user-friendly management console that integrates both authoring and administrative tasks. You'll learn how to take advantage of SQL Server's tools for analyzing and tuning your databases. You'll also learn about integration services, implementing security, and Microsoft's new Business Intelligence (BI) suite.

This SQL Server 2005 training course provides delegates with the knowledge and skills required to install, configure, administer , troubleshoot and develop for Microsoft SQL 2005 Server. The course provides extensive hands-on practical experience to build and develop competence and abilities in tasks such as writing stored procedures and triggers, using .NET code in SQL Server 2005, tuning and monitoring perfomance, security, reporting and analysis services and more.

• Install and configure Microsoft SQL Server 2005
• Understand basic database design principles and how to implement them in SQL Server 2005
• Learn the fundamentals of writing Transact-SQL queries that retrieve or modify data
• Understand the differences between view, stored procedures, triggers, and user-defined functions
• Perform calculations with Visual Basic and Visual C# code rather than T-SQL
• Use the new Common Table Expressions for creating hierarchical queries
• Understand the SQL Server 2005 security model
• Configure and tune SQL Server 2005, and monitor database activity
• Support distributed users by implementing SQL Server 2005 Replication
• Publish data to the Web, and take advantage of SQL Server’s rich support for XML
• Understand Business Intelligence to add reporting and analysis capabilities

Course Content

A Tour of SQL Server 2005
SQL Server 2005 Editions, Components, and Tools | Using SQL Server Management Studio (SSMS) | Working with Tables and Queries | New Transact-SQL Features | Business Intelligence Services

Installing SQL Server 2005
Preparing for Installation | Upgrading an Earlier Version | Installation Steps | Configuring the Server

Designing and Creating a Database
Relational Database Design Principles | Implementing the Design

Data Selection Queries
Understanding Transact-SQL | The SELECT Statement | The WHERE Clause | Using ORDER BY to Sort Data | The GROUP BY Clause | Joining Tables

Modifying Data
Modifying Data | Inserting Data | Updating Data | Deleting Data | Understanding Transaction Isolation

Working with SQL Server Management Studio
Getting Started with SSMS | Exploring the Object Explorer | Working with the Query Editor | Using SQL Server Books Online

Transact-SQL Programming
Overview of Transact-SQL | Using Built-In Functions | Controlling Flow | Ranking Results

Transactions and Error Handling
Transaction Concepts | Applications and Transactions | Creating Explicit Transactions | Using TRY/CATCH Error Handling

Creating Views
What Is a View? | Creating Views | Updating Data Using a View | Using Computed Columns | Indexed Views | Partitioned Views

Creating Stored Procedures and Triggers
Creating Stored Procedures | Creating Triggers

Creating User-Defined Functions
User-Defined Function Overview | Scalar Functions | Inline Table-Valued Functions | Multi-Statement Table-Valued Functions | Using Functions, Views, and Stored Procedures

Using .NET Code in SQL Server 2005
Writing SQLCLR Code | SQLCLR Code Modules | Managing Code Modules | SQLCLR Security | T-SQL vs. .NET Code

Advanced Query Techniques
Full-Text Search | Generating XML with FOR XML | Using APPLY | Creating Recursive Queries | Creating Pivot Queries | Executing Dynamic SQL

Understanding and Implementing Security
Security Overview | Authentication | Authorization | Permissions | Data Encryption | Security Epilog

Analyzing and Tuning Performance
Evaluating Performance | Monitoring with SQL Server Profiler | Tuning Queries | Indexes and Partitions | Using the Database Engine Tuning Advisor | Understanding SQL Server Performance Problems

Automating Administrative Tasks
SQL Server Agent | Maintenance Plans | SQL Management Objects (SMO)

Programming Replication
Overview of SQL Server Replication | Replication Programming Interfaces | Configuring Replication | Synchronizing Data

Using Integration Services
Importing and Exporting Data | Integration Services Tools | Building a Package | Troubleshooting a Package

Analysis Services
Understanding Analysis Services | Creating a Unified Dimensional Model | Data Mining

Introduction to Reporting Services
Understanding Reporting Services | Configuring Reporting Services | Building a Simple Report | Creating, Publishing, and Viewing Reports | Using and Managing Published Reports