click here
to
go back to SCHEDULE!
Course 2073—Five days—Instructor-led
Programming a Microsoft SQL Server 2000 Database + course 2071
option
Introduction
This course provides students with the technical skills
required to program a database solution by using Microsoft®
SQL ServerTM 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:
- 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).
For students who do not meet this prerequisite,
the following course provides students with the necessary knowledge and
skills:
- Knowledge of basic
Transact-SQL syntax (SELECT, UPDATE, and INSERT statements).
For students who do not meet this prerequisite,
the following course provides students with the necessary knowledge and
skills:
- Familiarity with the role of
the database administrator.
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.
Microsoft Certified Professional Exams
This course will help the student prepare for the
following Microsoft Certified Professional exam:
Student Materials
The course materials are yours to keep.
The student kit includes a comprehensive workbook and other necessary
materials for this class.
The following software is provided for use in the classroom:
- Microsoft SQL Server 2000,
Enterprise Edition
Course Outline
Module 1: SQL Server Overview
The following topics are covered in this module:
- What Is SQL Server?
- SQL Server Integration
- SQL Server Databases
- SQL Server Security
- Working with SQL Server
The following lab is covered in this module:
At the end of this module, you will be able to:
- 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
The following topics are covered in this module:
- 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
The following lab is covered in this module:
At the end of this module, you will be able to:
- 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
The following topics are covered in this module:
- Creating Databases
- Creating Filegroups
- Managing Databases
- Introduction to Data
Structures
The following lab is covered in this module:
- Creating and Managing
Databases
At the end of this module, you will be able to:
- Create a database.
- Create a filegroup.
- Manage a database.
- Describe data structures.
Module 4: Creating Data Types and Tables
The following topics are covered in this module:
- Creating Data Types
- Creating Tables
- Generating Column Values
- Generating Scripts
The following lab is covered in this module:
- Creating Data Types and
Tables
At the end of this module, you will be able to:
- 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
The following lab is covered in this module:
- Implementing Data Integrity
At the end of this module, you will be able to:
- 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.
- Disable constraints.
- Describe and use defaults
and rules.
- Determine which data
integrity enforcement methods to use.
Module 6: Planning Indexes
The following topics are covered in this module:
- Introduction to Indexes
- Index Architecture
- How SQL Server Retrieves
Stored Data
- How SQL Server Maintains
Index and Heap Structures
- Deciding Which Columns to
Index
The following lab is covered in this module:
- Determining the Indexes of a
Table
At the end of this module, you will be able to:
- 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.
Module 7: Creating and Maintaining Indexes
The following topics are covered in this module:
- Creating Indexes
- Creating Index Options
- Maintaining Indexes
- Introduction to Statistics
- Querying the sysindexes
Table
- Setting Up Indexes Using the
Index Tuning Wizard
- Performance Considerations
The following labs are covered in this module:
- Creating and Maintaining
Indexes
- Viewing Index Statistics
At the end of this module, you will be able to:
- 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
The following topics are covered in this module:
- Introduction to Views
- Advantages of Views
- Defining Views
- Modifying Data Through Views
- Optimizing Performance by
Using Views
- Performance Considerations
The following lab is covered in this module:
At the end of this module, you will be able to:
- 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
The following topics are covered in this module:
- 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
The following labs are covered in this module:
- Creating Stored Procedures
- Creating Stored Procedures
Using Parameters
At the end of this module, you will be able to:
- 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.
Module 10: Implementing User-defined Functions
The following topics are covered in this module:
- What Is a User-defined
Function?
- Defining User-defined
Functions
- Examples of User-defined
Functions
The following lab is covered in this module:
- Creating User-defined
Functions
At the end of this module, you will be able to:
- 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
The following topics are covered in this module:
- Introduction to Triggers
- Defining Triggers
- How Triggers Work
- Examples of Triggers
- Performance Considerations
The following lab is covered in this module:
At the end of this module, you will be able to:
- 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
The following topics are covered in this module:
- 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
The following lab is covered in this module:
At the end of this module, you will be able to:
- 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.
Module 13: Optimizing Query Performance
The following topics are covered in this module:
- Introduction to the Query
Optimizer
- Obtaining Execution Plan
Information
- Using an Index to Cover a
Query
- Indexing Strategies
- Overriding the Query
Optimizer
The following lab is covered in this module:
- Optimizing Query Performance
At the end of this module, you will be able to:
- 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
The following topics are covered in this module:
- Queries That Use the AND
Operator
- Queries That Use the OR
Operator
- Queries That Use Join
Operations
The following labs are covered in this module:
- Analyzing Queries That Use
the AND and OR Operators
- Analyzing Queries That Use
Different Join Strategies
At the end of this module, you will be able to:
- 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
The following topics are covered in this module:
- Introduction to Transactions
and Locks
- Managing Transactions
- SQL Server Locking
- Managing Locks
The following lab is covered in this module:
- Managing Transactions and
Locks
At the end of this module, you will be able to:
- 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.