Programming a Microsoft SQL Server 2000 Database

This course gives course participants a thorough grounding in Microsoft SQL Server 2000 and shows them how to design a SQL Server enterprise application architecture, use Transact-SQL, and create and manage databases and their related components. Course participants are also shown other advanced features such as implementing data integrity, creating and maintaining indexes and views, implementing user-defined functions, and programming across multiple servers using distributed queries. This knowledge is further enhanced with the ability to optimise query performance, analyse queries, and manage transactions and locks to ensure data concurrency and recoverability.Course contentWhat IsSQLServer?

• SQLServer Integration
• SQLServer Databases
• SQLServer Security
• Working withSQLServer

Overview of ProgrammingSQLServer

• Designing Enterprise Application Architecture
• SQLServer Programming Tools
• The Transact-SQL Programming Language
• Elements of Transact-SQL
• Additional Language Elements
• Ways to Execute Transact-SQL Statement

Creating and Managing Databases

• Creating Databases
• Creating Filegroups
• Managing Databases
• Introduction to Data Structures

Creating Data Types and Tables

• Creating Data Types
• Creating Tables
• Generating Column Values
• Generating Scripts

Implementing Data Integrity

• Types of Data Integrity
• Enforcing Data Integrity
• Defining Constraints
• Types of Constraints
• Disabling Constraints
• Using Defaults and Rules
• Deciding Which Enforcement Method to Use

Planning Indexes

• Introduction to Indexes
• Index Architecture
• HowSQLServer Retrieves Stored Data
• HowSQLServer Maintains Index and Heap Structures
• Deciding Which Columns to Index

Creating and Maintaining Indexes

• Creating Indexes
• Creating Index Options
• Maintaining Indexes
• Introduction to Statistics
• Querying the sysindexes Table
• Setting Up Indexes Using the Index Tuning Wizard
• Performance Considerations

Implementing Views

• Introduction to Views
• Advantages of Views
• Defining Views
• Modifying Data Through Views
• Optimising Performance by Using Views
• Performance Considerations

Implementing Stored Procedures

• Introduction to Stored Procedures
• Creating, Executing, Modifying, and Dropping Stored Procedures
• Using Parameters in Stored Procedures
• Executing Extended Stored Procedures
• Handling Error Messages
• Performance Considerations

Implementing User-defined Functions

• What Is a User-defined Function?
• Defining User-defined Functions
• Examples of User-defined Functions

Implementing Triggers

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

Programming Across Multiple Servers

• Introduction to Distributed Queries
• Executing an Ad Hoc Query on a Remote Data Source
• Setting Up a Linked Server Environment
• Executing a Query on a Linked Server
• Managing Distributed Transactions
• Modifying Data on a Linked Server
• Using Partitioned Views

Optimising Query Performance

• Introduction to the Query Optimiser
• Obtaining Execution Plan Information
• Using an Index to Cover a Query
• Indexing Strategies
• Overriding the Query Optimiser

Analysing Queries

• Queries That Use theANDOperator
• Queries That Use the OR Operator
• Queries That Use Join Operations

Managing Transactions and Locks

• Introduction to Transactions and Locks
• Managing Transactions
• SQLServer Locking
• Managing Locks