Developing in SQL Server 2005

SQL Server 2005 Overview

SQL Server 2005 Components and Architecture
SQL Server 2005 Developer Tools
SQL Server 2005 Security Implementation

Language Features

SQL Server Programming Tools
Elements of Transact-SQL
SQL Server Object Names
Additional Language Elements:
1. Local Variables
2. Operators
3. Functions
Ways to Execute Transact-SQL Statements
New Transact-SQL (T-SQL) Features in 2005

Design A Database

Define Entities
Apply Normalisation
Database Objects
System Tables
Defining Databases
Setting Database Options
Managing Data and Log File Growth

Implement Tables

Create Tables
Adding and Dropping a Column
Generating Column Values
Adding Constraints

Access and Modify Data

Selecting Data
Using Sub Queries
Select Queries With Joins
Modifying Data
Importing Data To Tables
Writing And Reading XML

Implement View

Introduction to Views
Advantages of Views
Defining Views
Modifying Data Through Views
Optimizing Performance by Using Views

Implement Functions

What Is a User-defined Function?
Defining User-defined Functions
Types of User-defined Functions:
1. Scalar
2. Inline
3. Multi-statement

Implement Triggers

Introduction to Triggers
Defining Triggers
How Triggers Work
Examples of Triggers
Performance Considerations

Implementing Stored Procedures

What Is A Procedure?
Types Of Stored Procedure
Creating Stored Procedures
Declaring Parameters
Set Return Value In A Stored Procedure

Indexing Tables to Improve Performance

Introduction to Indexes
Index Architecture
How SQL Server Retrieves Stored Data
How SQL Server Maintains Index and Heap Structures
Deciding Which Columns to Index

Managing Transactions and Locks

Introduction to Transactions and Locks
Managing Transactions
SQL Server Locking
Managing Locks

Design and Administer Security Levels

Design security plan
Administer authentication
Administer authorisation
Administer permissions
Administer users, groups and roles

Data Access from Applications

ADO.NET architecture
Creating Windows-based applications that use ADO.NET
Connecting to a database
Creating a query
Using a DataSet object to manage data
Binding a DataGrid object to a data source

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
Storing and Processing XML
Using APPLY
Creating Recursive Queries
Creating Pivot Queries
Executing Dynamic SQL
The XML data type

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

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

Introduction to Reporting Services

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

Using XML Web Services

Web Service architecture
Web Service protocols
Consuming an XML Web Service
Building an XML Web Service