Programming a Microsoft SQL Server 2000 Database

This course provides students with the technical skills required to program a database solution by using Microsoft^® SQL Server^TM 2000.

At Course Completion

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.

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

• An understanding of basic relational database concepts, including:

• Logical and physical database design.

• Data integrity concepts.

• Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many).

• How data is stored in tables (rows and columns).

The course materials, lectures, and lab exercises are in English. To benefit fully from the instruction, students need an understanding of the English language and completion of the prerequisites.

Day 1

Module 1: SQL Server Overview

• What Is SQL Server?
• SQL Server Integration
• SQL Server Databases
• SQL Server Security
• Working with SQL Server

• SQL Server Overview

• Describe SQL Server 2000 and its supported operating system platforms.
• Describe SQL Server integration with Microsoft Windows 2000 and other server applications.
• Describe SQL Server databases.
• Describe SQL Server security.
• Describe SQL Server administration and implementation activities, as well as SQL Server application design options.

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

• Overview of Transact-SQL

• Describe the concepts of enterprise-level application architecture.
• Describe the primary SQL Server programming tools.
• Explain the difference between the two primary programming tools in SQL Server.
• Describe the basic elements of Transact-SQL.
• Describe the use of local variables, operators, functions, control of flow statements, and comments.
• Describe the various ways to execute Transact-SQL statements

Module 3: Creating and Managing Databases

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

• Creating and Managing Databases

• Create a database.
• Create a filegroup.
• Manage a database.
• Describe data structures.

Day 2

Module 4: Creating Data Types and Tables

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

• Creating Data Types and Tables

• Create and drop user-defined data types.
• Create and drop user tables.
• Generate column values.
• Generate a script.

Module 5: Implementing Data Integrity

The following topics are covered in this module:

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

• Implementing Data Integrity

• Describe the types of data integrity.
• Describe the methods to enforce data integrity.
• Determine which constraint to use and create constraints.
• Define and use DEFAULT, CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.

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

• Determining the Indexes of a Table

• Describe why and when to use an index.
• Describe how SQL Server uses clustered and nonclustered indexes.
• Describe how SQL Server index architecture facilitates the retrieval of data.
• Describe how SQL Server maintains indexes and heaps.
• Describe the importance of selectivity, density, and distribution of data when deciding which columns to index.

Day 3

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

• Creating and Maintaining Indexes
• Viewing Index Statistics

• Create indexes and indexed views with unique or composite characteristics.
• Use the CREATE INDEX options.
• Describe how to maintain indexes over time.
• Describe how the query optimizer creates, stores, maintains, and uses statistics to optimize queries.
• Query the sysindexes table.
• Describe how the Index Tuning Wizard works and when to use it.
• Describe performance considerations that affect creating and maintaining indexes.

Module 8: Implementing Views

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

• Implementing Views

• Describe the concept of a view.
• List the advantages of views.
• Define a view with the CREATE VIEW statement.
• Modify data through views.
• Optimize performance by using 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

• Creating Stored Procedures
• Creating Stored Procedures Using Parameters

• Describe how a stored procedure is processed.
• Create, execute, modify, and drop a stored procedure.
• Create stored procedures that accept parameters.
• Execute extended stored procedures.
• Create custom error messages.

Day 4

Module 10: Implementing User-defined Functions

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

• Creating User-defined Functions

• Describe the three types of user-defined functions.
• Create and alter user-defined functions.
• Create each of the three types of user-defined functions.

Module 11: Implementing Triggers

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

• Creating Triggers

• Create a trigger.
• Drop a trigger.
• Alter a trigger.
• Describe how various triggers work.
• Evaluate the performance considerations that affect using 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

• Using Distributed Data

• Describe distributed queries.
• Write ad hoc queries that access data that is stored in a remote SQL Server 2000 or in an OLE DB data source.
• Set up a linked server environment to access data that is stored in a remote SQL Server 2000 or in an OLE DB data source.
• Write queries that access data from a linked server.
• Execute stored procedures on a remote server or linked server.

Day 5

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

• Optimizing Query Performance

• Explain the role of the query optimizer and how it works to ensure that queries are optimized.
• Use various methods for obtaining execution plan information so that they can determine how the query optimizer processed a query and validate that the most efficient query plan was generated.
• Create indexes that cover queries
• Identify indexing strategies that reduce page reads.
• Evaluate when to override the query optimizer.

Module 14: Analyzing Queries

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

• Analyzing Queries That Use the AND and OR Operators
• Analyzing Queries That Use Different Join Strategies

• Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the AND logical operator.
• Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the OR logical operator.
• Evaluate how the query optimizer uses different join strategies for query optimization.

Module 15: Managing Transactions and Locks

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

• Managing Transactions and Locks

• Describe transaction processing.
• Execute, cancel, or roll back a transaction.
• Identify locking concurrency issues.
• Identify resource items that can be locked and the types of locks.
• Describe lock compatibility.
• Describe how SQL Server 2000 uses dynamic locking.
• Set locking options and display locking information.