Table Of ContentA01T619603.fm Page i Monday, April 7, 2003 1:36 PM
PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2003 by Microsoft Corporation
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or
by any means without the written permission of the publisher.
Library of Congress Cataloging-in-Publication Data pending.
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWT 8 7 6 5 4 3
Distributed in Canada by H.B. Fenn and Company Ltd.
A CIP catalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide. For further informa-
tion about international editions, contact your local Microsoft Corporation office or contact Microsoft
Press International directly at fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send
comments to [email protected].
Active Directory, Microsoft, Microsoft Press, Windows, and Windows NT are either registered trademarks
or trademarks of Microsoft Corporation in the United States and/or other countries. Other product and
company names mentioned herein may be the trademarks of their respective owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people,
places, and events depicted herein are fictitious. No association with any real company, organiza-
tion, product, domain name, e-mail address, logo, person, place, or event is intended or should be
inferred.
Acquisitions Editor: Kathy Harding
Project Editor: Valerie Woolley
Body Part No. X09-59427
A04A619603.fm Page xxv Monday, April 7, 2003 1:37 PM
xxv
About This Book
Welcome to MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL
Server 2000 Database Design and Implementation, Exam 70-229, Second Edition.
This training kit introduces you to SQL Server 2000 and provides detailed informa-
tion about how to design and implement a SQL Server database. The training kit
takes you through the steps of how to plan and implement a database, create and
maintain database objects, and implement data integrity. You will also be intro-
duced to Transact-SQL, and you will learn how to use Transact-SQL to query a
SQL Server database and manage and manipulate data stored in that database.
Finally, the training kit describes how to manage SQL Server security and how to
maintain and optimize a SQL Server database.
Note For more information on becoming a Microsoft Certified Systems Engineer,
see the section titled “The Microsoft Certified Professional Program” later in this
chapter.
Before You Begin Part 1
Each chapter in Part 1 is divided into lessons. Most lessons include hands-on pro-
cedures that allow you to practice or demonstrate a particular concept or skill. Each
lesson ends with a short summary of the information presented in that lesson, and
each chapter ends with a set of review questions to test your knowledge of the
chapter material.
The “Getting Started” section of this chapter provides important setup instructions
that describe the hardware and software requirements to complete the procedures
in this course. Read through this section thoroughly before you start the lessons.
Intended Audience
This book was developed for information technology (IT) professionals who need
to design, plan, implement, and support Microsoft SQL Server 2000 or who plan to
take the related Microsoft Certified Professional exam 70-229: Designing and
Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition.
A04A619603.fm Page xxvi Monday, April 7, 2003 1:37 PM
xxvi Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229
Prerequisites
This course requires that students meet the following prerequisites:
■ Working knowledge of the Windows interface is required.
■ Understanding of basic Microsoft network functions and terminology is
required.
■ One year of experience with relational databases is recommended. You should
have supported or designed a relational database and you should understand the
fundamental concepts of relational database design.
■ Three to six months of SQL Server experience is recommended. You should
have installed SQL Server and worked with SQL Server client tools.
Reference Materials
You might find the following reference materials useful:
■ Designing Relational Database Systems, Rebecca M. Riordan, Microsoft
Press, 1999
■ Inside SQL Server 2000, Kalen Delaney (based on the first edition by Ron
Soukup), Microsoft Press, 2000
■ Microsoft TechNet, available online at http://www.microsoft.com/technet/
■ MSDN Online, available at http://msdn.microsoft.com/sqlserver/
■ SQL Server Books Online, available on the product CD-ROM
■ SQL Server Magazine (Information about the magazine is available at
http://www.sqlmag.com/.)
■ Technical and product information available online at http://www.microsoft.com
/sql/
About the CD-ROM
This book contains both a Supplemental Course Materials CD-ROM and a 120-day
Evaluation Edition of Microsoft SQL Server 2000.
The Supplemental Course Materials CD-ROM contains files required to perform the
hands-on procedures, as well as information designed to supplement the lesson
material. These files can be used directly from the CD-ROM or copied onto your
hard disk by using the setup program. For more information regarding the contents
of this CD-ROM, see the section titled “Getting Started” later in this introduction.
A complete version of this book is also available online with a variety of viewing
options available. For information about using the online book, see the section,
“About the eBook,” later in this introduction.
A04A619603.fm Page xxvii Monday, April 7, 2003 1:37 PM
About This Book xxvii
Features of This Book
Each chapter opens with a “Before You Begin” section, which prepares you for
completing the chapter.
(cid:1) The chapters are then broken into lessons. Whenever possible, lessons contain
practices that give you an opportunity to use the skills being presented or to
explore the part of the application being described. All practices offer step-by-step
procedures that are identified with a bullet symbol like the one to the left of this
paragraph.
The “Review” section at the end of the chapter allows you to test what you have
learned in the chapter’s lessons.
The Appendix, “Questions and Answers,” contains all of the book’s questions and
corresponding answers.
Notes
Several types of Notes appear throughout the lessons.
■ Notes marked Tip contain explanations of possible results or alternative
methods.
■ Notes marked Important contain information that is essential to completing a
task.
■ Notes marked Note contain supplemental information.
■ Notes marked Caution contain warnings about possible loss of data.
Conventions
The following conventions are used throughout this book.
Notational Conventions
■ Characters or commands that you type appear in bold lowercase type.
■ Italic in syntax statements indicates placeholders for variable information.
Italic is also used for book titles.
■ Names of files and folders appear in Title Caps, except when you are to type
them directly. Unless otherwise indicated, you can use all lowercase letters
when you type a filename in a dialog box or at a command prompt.
■ Filename extensions appear in all lowercase.
■ Acronyms appear in all uppercase.
■ Monospace type represents code samples, examples of screen text, or entries
that you might type at a command prompt or in initialization files.
A04A619603.fm Page xxviii Monday, April 7, 2003 1:37 PM
xxviii Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229
■ Square brackets ([ ]) are used in syntax statements to enclose optional items.
For example, [filename] in command syntax indicates that you can choose to
type a filename with the command. Type only the information within the brack-
ets, not the brackets themselves.
■ Braces ({ }) are used in syntax statements to enclose required items. Type only
the information within the braces, not the braces themselves.
■ Icons represent specific sections in the book, as follows:
Icon Represents
A hands-on exercise. You should perform the exercise to give your-
self an opportunity to use the skills being presented in the lesson.
practic.eps
Chapter review questions. These questions at the end of each chapter
allow you to test what you have learned in the lessons. You will find
the answers to the review questions in the Appendix, “Questions and
Answers,” at the end of the book.
quest.eps
Keyboard Conventions
■ A plus sign (+) between two key names means that you must press those keys at
the same time. For example, “Press ALT+TAB” means that you hold down ALT
while you press TAB.
■ A comma ( , ) between two or more key names means that you must press each
of the keys consecutively, not together. For example, “Press ALT, F, X” means
that you press and release each key in sequence. “Press ALT+W, L” means that
you first press ALT and W together, and then release them and press L.
■ You can choose menu commands with the keyboard. Press the ALT key to acti-
vate the menu bar, and then sequentially press the keys that correspond to the
highlighted or underlined letter of the menu name and the command name. For
some commands, you can also press a key combination listed in the menu.
■ You can select or clear check boxes or option buttons in dialog boxes with the
keyboard. Press the ALT key, and then press the key that corresponds to the
underlined letter of the option name. Or you can press TAB until the option is
highlighted, and then press the spacebar to select or clear the check box or
option button.
■ You can cancel the display of a dialog box by pressing the ESC key.
Chapter and Appendix Overview
This self-paced training course combines notes, hands-on procedures, and review
questions to teach you how to design and implement databases with SQL Server
2000. It is designed to be completed from beginning to end, but in some cases you
can choose a customized track and complete only the sections that interest you.
(See the next section, “Finding the Best Starting Point for You,” for more informa-
tion.) If you choose the customized track option, see the “Before You Begin”
A04A619603.fm Page xxix Monday, April 7, 2003 1:37 PM
About This Book xxix
section in each chapter. Any hands-on procedures that require preliminary work
from preceding chapters refer to the appropriate chapters.
Part 1 is divided into the following sections and chapters:
■ The “About This Book” section contains a self-paced training overview and
introduces the components of this training. Read this section thoroughly to get
the greatest educational value from this self-paced training and to plan which
lessons you will complete.
■ Chapter 1, “Introduction to Microsoft SQL Server 2000,” introduces you to
SQL Server 2000 and explains what SQL Server is. The chapter provides a
cohesive overview of SQL Server so that you can understand how all the pieces
fit together.
■ Chapter 2, “Using Transact-SQL on a SQL Server Database,” introduces you
to Transact-SQL and provides details about how to create and execute Transact-
SQL statements in order to manage a SQL Server database and its data. The
chapter also introduces you to the SQL Server programming tools that allow
you to use Transact-SQL to interface with the database.
■ Chapter 3, “Designing a SQL Server Database,” introduces you to the process
of creating a SQL Server database. It describes the basic concepts of database
design and provides information about planning a database, identifying system
requirements, and developing a logical data model.
■ Chapter 4, “Implementing SQL Server Databases and Tables,” explains how to
create and manage a SQL Server database. It then discusses data types and how
to identify which ones to use when creating a table. The chapter also describes
how to create these tables—using the data type information—and how to mod-
ify the tables after they have been created.
■ Chapter 5, “Implementing Data Integrity,” provides an overview of the various
methods that you can use to maintain data integrity and a description of the types
of data integrity that you will find in a SQL Server database. The chapter also
provides detailed information about the various types of integrity constraints that
you can use to enforce data integrity and how to implement them in a database.
■ Chapter 6, “Accessing and Modifying Data,” provides detailed information
about four Transact-SQL statements (SELECT, INSERT, UPDATE, and
DELETE) and describes how each statement is used in Query Analyzer to
retrieve and modify data. This chapter also introduces you to other methods for
adding, modifying, and deleting data.
■ Chapter 7, “Managing and Manipulating Data,” describes more techniques for
managing and manipulating data, including how to import and export data, how
to manipulate heterogeneous data, how to use Transact-SQL cursors, and how
to extract data in XML format.
■ Chapter 8, “Implementing Stored Procedures,” introduces you to the types of
stored procedures available in SQL Server 2000 and how to create, execute, and
alter them. You are also introduced to programming stored procedures.
A04A619603.fm Page xxx Monday, April 7, 2003 1:37 PM
xxx Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229
■ Chapter 9, “Implementing Triggers,” introduces you to triggers and how to use
them to extend data integrity and implement complex business logic. You will
learn when it is appropriate to implement triggers and when basic constraints
will suffice. You will also learn how to program triggers and how to use system
commands and functions commonly used in trigger programming.
■ Chapter 10, “Implementing Views,” introduces you to views and the various
functionality that they support. You will learn how to use views to insert,
update, and modify data.
■ Chapter 11, “Implementing Indexes,” introduces you to the structure and pur-
pose of indexes and the types and characteristics of indexes. You will learn how
to determine when an index is appropriate, the type of index to create, and how
to create it.
■ Chapter 12, “Managing SQL Server Transactions and Locks,” introduces you to
the fundamentals of transactions and locks and describes how transactions and
locks are used to process data modifications.
■ Chapter 13, “Designing and Administering SQL Server 2000 Security,” intro-
duces you to SQL Server security. You will learn how to design SQL Server
security to accommodate user requirements and protect the database from
unauthorized access.
■ Chapter 14, “SQL Server Monitoring and Tuning,” examines how to use SQL
Profiler to monitor a database system and explores methods of improving data-
base performance through partitioning and index tuning.
Following Part 2 you will find:
■ The Appendix, “Questions and Answers,” lists all of the review questions from
the book and the suggested answers.
■ The Glossary provides definitions for SQL Server terminology.
Finding the Best Starting Point for You
Because this book is self-paced, you can skip some lessons and revisit them later.
But note that you must complete the procedures in certain chapters before you can
complete the procedures in other chapters:
■ The exercises in Chapter 3, “Designing a SQL Server Database,” must be com-
pleted before you can complete the exercises in Chapter 4, “Implementing SQL
Server Databases and Tables.” The exercises in Chapter 4 must be completed
before you can complete the exercises in Chapter 5, “Implementing Data
Integrity.”
■ The exercises in Chapter 3, “Designing a SQL Server Database;” Chapter 4,
“Implementing SQL Server Databases and Tables;” and Chapter 5, “Imple-
menting Data Integrity,” must be completed before you can complete the exer-
cises in Chapter 6, “Accessing and Modifying Data,” and Chapter 7, “Managing
and Manipulating Data.”
A04A619603.fm Page xxxi Monday, April 7, 2003 1:37 PM
About This Book xxxi
■ The exercises in Chapter 3, “Designing a SQL Server Database;” Chapter 4,
“Implementing SQL Server Databases and Tables;” Chapter 5, “Implementing
Data Integrity;” and Chapter 7, “Managing and Manipulating Data,” must be
completed before you can complete all of the exercises in the chapters that fol-
low Chapter 7.
Use the following table to find the best starting point for you.
If you Follow this learning path
Are preparing to take the Microsoft Read the “Getting Started” section. Then work
Certified Professional exam 70-229, from Chapter 1, “Introduction to Microsoft SQL
Designing and Implementing Data- Server2000,” through Chapter 7, “Managing and
bases with Microsoft SQL Server Manipulating Data.” Work through the remaining
2000 Enterprise Edition chapters in any order.
Want to review information about Use the “Where to Find Specific Skills in This
specific topics from the exam Book” section that follows this table.
Where to Find Specific Skills in This Book
The following tables provide a list of the skills measured on certification exam
70-229, Designing and Implementing Databases with Microsoft SQL Server 2000
Enterprise Edition. The table provides the skill and where in this book you will find
the lesson relating to that skill.
Note Exam skills are subject to change without prior notice and at the sole discre-
tion of Microsoft.
Skill Being Measured Location in Book
Developing a Logical Data Model
1.1Define entities. Chapter 3, Lessons 1 and 2
1.2Design entity keys. Chapter 5, Lessons 1 and 2
1.3Design attribute domain integrity. Chapter 4, Lessons 2 and 3
Chapter 5, Lessons 1 and 2
Implementing the Physical Database
2.1Create and alter databases. Chapter 3, Lessons 1, 2, 3, and 4
Chapter 4, Lesson 1
2.2Create and alter database objects. Chapter 2, Lesson 4
Chapter 4, Lessons 1 and 3
Chapter 5, Lessons 1 and 2
Chapter 8, Lessons 1, 2, and 3
Chapter 9, Lessons 1, 2, and 3
Chapter 10, Lessons 1, 2, and 3
Chapter 11, Lessons 1 and 2
A04A619603.fm Page xxxii Monday, April 7, 2003 1:37 PM
xxxii Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229
Skill Being Measured Location in Book
Implementing the Physical Database (continued)
2.3Alter database objects to support replication Chapter 14, Lesson 2
and partitioned views.
2.4Troubleshoot failed object creation. Chapter 14, Lesson 1
Retrieving and Modifying Data
3.1Import and export data. Chapter 7, Lesson 1
3.2Manipulate heterogeneous data. Chapter 7, Lesson 2
3.3Retrieve, filter, group, summarize, and Chapter 6, Lessons 1, 2, and 3
modify data by using Transact-SQL.
3.4Manage result sets by using cursors and Chapter 6, Lessons 1, 2, and 3
Transact-SQL. Chapter 7, Lesson 3
3.5 Extract data in XML format. Chapter 7, Lesson 4
Programming Business Logic
4.1Manage data manipulation by using stored Chapter 2, Lesson 3
procedures, transactions, triggers, user- Chapter 8, Lessons 1, 2, and 3
defined functions, and views. Chapter 9, Lessons 1, 2, and 3
Chapter 10, Lessons 1, 2, and 3
Chapter 12, Lessons 1, 2, and 3
4.2Enforce procedural business logic by using Chapter 2, Lesson 3
stored procedures, transactions, triggers, Chapter 8, Lessons 1, 2, and 3
user-defined functions, and views. Chapter 9, Lessons 1, 2, and 3
Chapter 10, Lessons 1, 2, and 3
Chapter 12, Lessons 1, 2, and 3
4.3Troubleshoot and optimize programming Chapter 14, Lesson 1
objects.
Tuning and Optimizing Data Access
5.1Analyze the query execution plan. Chapter 14, Lesson 1
5.2Capture, analyze, and replay SQL Profiler Chapter 14, Lesson 1
traces.
5.3Create and implement indexing strategies. Chapter 11, Lessons 1 and 2
5.4Analyze index use by using the Index Chapter 11, Lessons 1 and 2
Tuning wizard.
5.5Monitor and troubleshoot database activity Chapter 14, Lesson 1
by using SQL Profiler.
Designing a Database Security Plan
6.1Control data access by using stored proce- Chapter 2, Lesson 3
dures, triggers, user-defined functions, and Chapter 8, Lessons 1, 2, and 3
views. Chapter 9, Lessons 1, 2, and 3
Chapter 10, Lessons 1, 2, and 3
Chapter 13, Lessons 1, 2, and 3
