| -SQL Server Overview :
provides a high-level overview of Microsoft SQL Server™ 2000 platforms,
architecture, components, and security. It also identifies and defines key
SQL Server terminology and concepts. This module discusses how well SQL
Server integrates with Windows 2000 and other Microsoft server applications.
It concludes with an overview of SQL Server administration and SQL Server
database implementation, as well as application design options.
-Overview of Programming SQL Server :
provides students with an overview of enterprise-level application
architecture and Transact-SQL as a programming language. Transact-SQL is a
data definition, manipulation, and control language. Students are assumed to
be familiar with ANSI-SQL and basic programming concepts, such as functions,
operators, variables, and control-of-flow statements. Students will also
learn the different ways to execute Transact-SQL.
-Creating and Managing Databases :
describes how to create a database, set
database options, create filegroups, and manage a database and the
transaction log. It reviews disk space allocation and how the transaction
log records data modifications.
-Creating Data Types and Tables :
describes how to create data types and
tables and generate Transact-SQL scripts containing statements that create a
database and its objects.
-Implementing Data Integrity :
shows how centrally managed data integrity
is a benefit of relational databases. This module begins with an
introduction to data integrity concepts, including the methods available for
enforcing data integrity. The module then introduces a section on
constraints. Constraints are the key method for ensuring data integrity. The
creation, implementation, and disabling of constraints are discussed. This
module also discusses how defaults and rules are an alternate way to enforce
data integrity. The module concludes with a comparison of the different data
integrity methods.
-Planning Indexes :
provides students with an overview of
planning indexes. It explains how database performance can be improved with
indexes. It discusses how clustered and nonclustered indexes are stored in
SQL Server and how SQL Server retrieves rows by using indexes. It also
explores how SQL Server maintains indexes. The module concludes with
guidelines for deciding which columns to index.
-Creating and Maintaining Indexes :
provides students with an overview of
creating and maintaining indexes by using the CREATE INDEX options. It
describes how maintenance procedures physically change the indexes. The
module discusses maintenance tools and describes the use of statistics in
SQL Server. It also describes ways to verify that indexes are used and
explains how to tell whether they perform optimally. The module concludes
with a discussion of when to use the Index Tuning Wizard.
|
-Implementing Views :
shows how views provide the ability to store a predefined query as an object
in the database for later use. They offer a convenient way to hide sensitive
data or the complexities of a database design and to provide a set of
information without requiring the user to write or execute Transact-SQL
statements. The module also defines views and their advantages. The module
then describes creating views and provides examples of projections and
joins. These examples illustrate how to include computed columns and
built-in functions in the view definitions. The module then covers
restrictions on modifying data through views. The last section discusses how
views can improve performance.
-Implementing Stored Procedures :
describes how to use stored procedures to improve application design and
performance by encapsulating business rules. It discusses ways to process
common queries and data modifications. The module provides numerous examples
and demonstrations of stored procedures.
-Implementing User-defined Functions :
discusses the implementation of
user-defined functions. It explains the three types of user-defined
functions and the general syntax for creating and altering them, and
provides an example of each type.
-Implementing Triggers :
shows that triggers are useful tools for
database implementers who want to have certain actions performed whenever
data is inserted, updated, or deleted from a specific table. They are
especially useful tools to cascade changes throughout other tables in the
database, while preserving complex referential integrity.
-Programming Across Multiple Servers :
provides students with information on how
to design security for a multi-server environment. It also explains the
construction of distributed queries, distributed transactions, and
partitioned views.
-Optimizing Query Performance :
provides students with an in-depth look at
how the query optimizer works, how to obtain query plan information, and how
to implement indexing strategies.
-Analyzing Queries : describes how
the query optimizer evaluates and processes queries that contain the AND
operator, the OR operator, and join operations.
-Managing Transactions and Locks :
introduces how transactions and locks ensure transaction integrity while
allowing for concurrent use. The module continues with a discussion of how
transactions are executed and rolled back. A short animation helps to convey
how transaction processing works. The module next describes how SQL Server
locks maintain data consistency and concurrency. The module then introduces
resources that can be locked, the different types of locks, and lock
compatibility. A discussion follows on SQL Server dynamic locking based on
schema and query. The final section describes some locking options,
discusses deadlocks, and explains how to display information on active
locks. |
