MS2073 - Programming a Microsoft SQL Server 2000 Database
| Categories |
MCDBA Training, MCSD Training, MCAD Training, MCSE Training, Microsoft Certification Training, SQL Training, Database Training, Microsoft SQL Server Training |
| Duration |
5 days (35 hours) |
| Pre-requisites |
DMD - Database Modeling and Design, MS2071 - Querying Microsoft SQL Server 2000 with Transact-SQL, MS2151 - Microsoft Windows 2000 Network and Operating System Essentials or MS2152 - Implementing Microsoft Windows 2000 Professional and Server, or equivalent knowledge and skills and experience configuring a Microsoft Windows® 2000 operating system and
administering a SQL database
|
|
|
|
| Courseware |
MS2073-MS (300 pages) |
| Price |
$1975 |
| Notes |
This course covers topics students need to know to prepare for the following Microsoft exam:
Exam 70-229: Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition (MCDBA core; MCAD, MCSD, MCSE 2003 and MCSE, elective)
|
This course provides students with the technical skills required to program a database solution by using
Microsoft SQL Server™ 2000. At the end of the course, students will be able to describe the
elements of SQL Server 2000; design a SQL Server enterprise application architecture; describe the
conceptual basis of programming in Transact-SQL; create and manage databases and their related components;
implement data integrity by using the IDENTITY column property, constraints, defaults, rules, and unique
identifiers; plan for the use of indexes; create and maintain indexes; create, use, and maintain data views; implement user-defined functions; design, create, and use stored procedures; create and implement triggers; program across multiple servers by using distributed queries, distributed transactions, and partitioned views; optimize query performance; analyze queries; and manage transactions and locks to ensure data concurrency and recoverability.
Module 1: SQL Server Overview
What Is SQL Server?
SQL Server Integration
SQL Server Databases
SQL Server Security
Lab: Working with SQL Server
Module 2: Overview of Programming SQL Server
Designing Enterprise Application Architecture
SQL Server Programming Tools
The Transact-SQL Programming Language
Elements of Transact-SQL
Additional Language Elements
Ways to Execute Transact-SQL Statement
Lab: Overview of Transact-SQL
Module 3: Creating and Managing Databases
Creating Databases
Creating Filegroups
Managing Databases
Introduction to Data Structures
Lab: Creating and Managing Databases
Module 4: Creating Data Types and Tables
Creating Data Types
Creating Tables
Generating Column Values
Generating Scripts
Lab: Creating Data Types and Tables
Module 5: 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
Lab: Implementing Data Integrity
Module 6: Planning Indexes
Introduction to Indexes
Index Architecture
How SQL Server Retrieves Stored Data
How SQL Server Maintains Index and Heap Structures
Deciding Which Columns to Index
Lab: Determining the Indexes of a Table
Module 7: 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
Labs:
Creating and Maintaining Indexes
Viewing Index Statistics
Module 8: Implementing Views
Introduction to Views
Advantages of Views
Defining Views
Modifying Data Through Views
Optimizing Performance by Using Views
Performance Considerations
Lab: Implementing Views
Module 9: 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
Labs:
Creating Stored Procedures
Creating Stored Procedures Using Parameters
Module 10: Implementing User-defined Functions
What Is a User-defined Function?
Defining User-defined Functions
Examples of User-defined Functions
Lab: Creating User-defined Functions
Module 11: Implementing Triggers
Introduction to Triggers
Defining Triggers
How Triggers Work
Examples of Triggers
Performance Considerations
Lab: Creating Triggers
Module 12: 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
Lab: Using Distributed Data
Module 13: Optimizing Query Performance
Introduction to the Query Optimizer
Obtaining Execution Plan Information
Using an Index to Cover a Query
Indexing Strategies
Overriding the Query Optimizer
Lab: Optimizing Query Performance
Module 14: Analyzing Queries
Queries That Use the AND Operator
Queries That Use the OR Operator
Queries That Use Join Operations
Labs:
Analyzing Queries That Use the AND and OR Operators
Analyzing Queries That Use Different Join Strategies
Module 15: Managing Transactions and Locks
Introduction to Transactions and Locks
Managing Transactions
SQL Server Locking
Managing Locks
Lab: Managing Transactions and Locks