2073 - Programming a Microsoft SQL Server 2000 Database
Duration: 5 Days.
This course maps to Exam#70-229
Prerequisites:
Before attending this course, students must have:
- Experience using the Microsoft Windows 2000 operating system to:
- Connect clients running Windows 2000 to networks and the Internet
- Configure the Windows 2000 environment
- Create and manage user accounts
- Manage access to resources by using groups
- Configure and manage disks and partitions, including disk striping and mirroring
- Manage data by using the NTFS file system
- Implement Windows 2000 security
-
Optimize performance in Windows 2000.
For students who do not meet this prerequisite, the following courses provide students with the necessary knowledge and skills - Course 2151,Microsoft Windows 2000 Network and Operating System Essentials.
- Course 2152,Implementing Microsoft Windows 2000 Professional and Server.
Course Description:
This course provides students with the technical skills required to program a database solution by using Microsoft SQL Server 2000
Objectives:
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
Lesson 1: SQL Server Overview
- What Is SQL Server
- SQL Server Integration
- SQL Server Databases
- SQL Server Security
- Working with SQL Server
Lesson 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
Lesson 3: Creating and Managing Databases
- Creating Databases
- Creating Filegroups
- Managing Databases
- Introduction to Data Structures
Lesson 4: Creating Data Types and Tables
- Creating Data Types
- Creating Tables
- Generating Column Values
- Generating Scripts
Lesson 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
Lesson 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
Lesson 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
Lesson 8: Implementing Views
- Introduction to Views
- Advantages of Views
- Defining Views
- Modifying Data Through Views
- Optimizing Performance by Using Views
- Performance Considerations
Lesson 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