MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

About This Book

Welcome to MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation. This training kit introduces you to SQL Server 2000 and provides detailed information 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 introduced 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

Each chapter in this book is divided into lessons. Most lessons include hands-on procedures 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.

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

The Supplemental Course Materials CD-ROM also 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, "The Online Book," later in this introduction (the other CD-ROM contains Windows 2000 Server and SQL Server 2000 Enterprise Edition).

Features of This Book

Each chapter opens with a "Before You Begin" section, which prepares you for completing the chapter.

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.

The "Review" section at the end of the chapter allows you to test what you have learned in the chapter's lessons.

Appendix A, "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.
• 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 brackets, 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:

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 activate 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 information.) If you choose the customized track option, see the "Before You Begin" section in each chapter. Any hands-on procedures that require preliminary work from preceding chapters refer to the appropriate chapters.

The book is divided into the following 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 modify 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.
• 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 purpose 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," introduces 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 database performance through partitioning and index tuning.
• Appendix A, "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 completed 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, "Implementing Data Integrity," must be completed before you can complete the exercises in Chapter 6, "Accessing and Modifying Data," and Chapter 7, "Managing and Manipulating Data."
• 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 follow Chapter 7.

Use the following table to find the best starting point for you.

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

Getting Started

This self-paced training course contains hands-on procedures to help you learn about Microsoft SQL Server 2000 Enterprise Edition.

Hardware Requirements

Your computer must meet the minimum hardware requirements for Windows 2000 Server. In addition, you must meet the following minimum configuration to support SQL Server 2000 Enterprise Edition. All hardware should be on the Microsoft Windows 2000 Server Hardware Compatibility List (HCL). The latest version of the HCL can be downloaded from the Hardware Compatibility List Web page at http://www.microsoft.com/hwtest/hcl/.

• 166-MHz or higher Pentium processor
• 64 MB RAM (minimum), 128 MB or more recommended
• SQL Server database components: 95 MB to 270 MB, 250 MB typical
• CD-ROM drive
• Microsoft Mouse or compatible pointing device

Software Requirements

The following software is required to complete the procedures in this course (120-day evaluation copies of each of these products are included on the CD-ROMs in this kit).

• Windows 2000 Server
• SQL Server 2000 Enterprise Edition

CAUTION

Setup Instructions

Set up your computer according to the manufacturer's instructions. Use default installation options when setting up Windows 2000 Server and SQL Server 2000. The Windows 2000 computer should be set up as a stand-alone server.

The Online Book

The CD-ROM also includes an online version of the book that you can view on-screen by using Microsoft Internet Explorer 4.01 or later.

To use the online version of this book

1. Insert the Supplemental Course Materials CD-ROM into your CD-ROM drive.
2. Select Run from the Start menu on your desktop, and type D:\Ebook\Setup.exe (where D is the name of your CD-ROM disk drive).

This will install an icon for the online book to your Start menu.

3. Click OK to exit the Installation wizard.

NOTE

The Microsoft Certified Professional Program

The Microsoft Certified Professional (MCP) program provides the best method to prove your command of current Microsoft products and technologies. Microsoft, an industry leader in certification, is on the forefront of testing methodology. Our exams and corresponding certifications are developed to validate your mastery of critical competencies as you design and develop, or implement and support, solutions with Microsoft products and technologies. Computer professionals who become Microsoft certified are recognized as experts and are sought after industry-wide.

The Microsoft Certified Professional program offers eight certifications, based on specific areas of technical expertise:

• Microsoft Certified Professional (MCP)  Demonstrated in-depth knowledge of at least one Microsoft operating system. Candidates may pass additional Microsoft certification exams to further qualify their skills with Microsoft BackOffice products, development tools, or desktop programs.
• Microsoft Certified Professional + Internet  MCPs with a specialty in the Internet are qualified to plan security, install and configure server products, manage server resources, extend servers to run scripts, monitor and analyze performance, and troubleshoot problems.
• Microsoft Certified Professional + Site Building  Demonstrated what it takes to plan, build, maintain, and manage Web sites using Microsoft technologies and products.
• Microsoft Certified Systems Engineer (MCSE)  Qualified to effectively plan, implement, maintain, and support information systems in a wide range of computing environments with Microsoft Windows NT Server and the Microsoft BackOffice integrated family of server software.
• Microsoft Certified Systems Engineer + Internet  MCSEs with an advanced qualification to enhance, deploy, and manage sophisticated intranet and Internet solutions that include a browser, proxy server, host servers, database, and messaging and commerce components. In addition, an MCSE + Internet-certified professional is able to manage and analyze Web sites.
• Microsoft Certified Database Administrator (MCDBA)  Individuals who derive physical database designs, develop logical data models, create physical databases, create data services by using Transact-SQL, manage and maintain databases, configure and manage security, monitor and optimize databases, and install and configure Microsoft SQL Server.
• Microsoft Certified Solution Developer (MCSD)  Qualified to design and develop custom business solutions with Microsoft development tools, technologies, and platforms, including Microsoft Office and Microsoft BackOffice.
• Microsoft Certified Trainer (MCT)  Instructionally and technically qualified to deliver Microsoft Official Curriculum through a Microsoft Certified Technical Education Center (CTEC).

Microsoft Certification Benefits

Microsoft certification, one of the most comprehensive certification programs available for assessing and maintaining software-related skills, is a valuable measure of an individual's knowledge and expertise. Microsoft certification is awarded to individuals who have successfully demonstrated their ability to perform specific tasks and implement solutions with Microsoft products. Not only does this provide an objective measure for employers to consider; it also provides guidance for what an individual should know to be proficient. And as with any skills-assessment and benchmarking measure, certification brings a variety of benefits to the individual and to employers and organizations.

Microsoft Certification Benefits for Individuals

As a Microsoft Certified Professional, you receive many benefits, including the following:

• Industry recognition of your knowledge and proficiency with Microsoft products and technologies.
• Access to technical and product information directly from Microsoft through a secured area of the MCP Web Site.
• MSDN Online Certified Membership that helps you tap into the best technical resources, connect to the MCP community, and gain access to valuable resources and services. (Some MSDN Online benefits may be available in English only or may not be available in all countries.) See the MSDN Web site for a growing list of certified member benefits.
• Logos to enable you to identify your Microsoft Certified Professional status to colleagues or clients.
• Invitations to Microsoft conferences, technical training sessions, and special events.
• A Microsoft Certified Professional certificate.
• Subscription to Microsoft Certified Professional Magazine (North America only), a career and professional development magazine.

Additional benefits, depending on your certification and geography, include:

• A complimentary one-year subscription to the Microsoft TechNet Technical Plus, providing valuable information on monthly CD-ROMs.
• A one-year subscription to the Microsoft Beta Evaluation program. This benefit provides you with up to 12 free monthly CD-ROMs containing beta software (English only) for many of Microsoft's newest software products.

Microsoft Certification Benefits for Employers and Organizations

Through certification, computer professionals can maximize the return on investment in Microsoft technology. Research shows that Microsoft certification provides organizations with the following:

• Excellent return on training and certification investments by providing a standard method of determining training needs and measuring results
• Increased customer satisfaction and decreased support costs through improved service, increased productivity, and greater technical self-sufficiency
• Reliable benchmark for hiring, promoting, and career planning
• Recognition and rewards for productive employees by validating their expertise
• Retraining options for existing employees so they can work effectively with new technologies
• Assurance of quality when outsourcing computer services

To learn more about how certification can help your company, see the backgrounders, white papers, and case studies available at http://www.microsoft.com/mcp/mktg/bus_bene.htm:

• Financial Benefits to Supporters of Microsoft Professional Certification, IDC white paper (1998wpidc.doc; 1,608K)
• Prudential Case Study (prudentl.exe; 70K self-extracting file)
• The Microsoft Certified Professional Program Corporate Backgrounder (mcpback.exe; 50K)
• A white paper (mcsdwp.doc; 158K) that evaluates the Microsoft Certified Solution Developer certification
• A white paper (mcsestud.doc; 161K) that evaluates the Microsoft Certified Systems Engineer certification
• Jackson Hole High School Case Study ( jhhs.doc; 180K)
• Lyondel Case Study (lyondel.doc; 21K)
• Stellcom Case Study (stellcom.doc; 132K)

Requirements for Becoming a Microsoft Certified Professional

The certification requirements differ for each certification and are specific to the products and job functions addressed by the certification.

To become a Microsoft Certified Professional, you must pass rigorous certification exams that provide a valid and reliable measure of technical proficiency and expertise. These exams are designed to test your expertise and ability to perform a role or task with a product, and are developed with the input of professionals in the industry. Questions in the exams reflect how Microsoft products are used in actual organizations, giving them "real-world" relevance.

Microsoft Certified Product Specialists are required to pass one operating system exam. Candidates may pass additional Microsoft certification exams to further qualify their skills with Microsoft BackOffice products, development tools, or desktop applications.

Microsoft Certified Professional + Internet specialists are required to pass the prescribed Microsoft Windows NT Server 4.0, TCP/IP, and Microsoft Internet Information System exam series.

Microsoft Certified Professionals with a specialty in site building are required to pass two exams covering Microsoft FrontPage, Microsoft Site Server, and Microsoft Visual InterDev technologies to provide a valid and reliable measure of technical proficiency and expertise.

Microsoft Certified Systems Engineers are required to pass a series of core Microsoft Windows operating system and networking exams, and BackOffice technology elective exams.

Microsoft Certified Systems Engineers + Internet specialists are required to pass seven operating system exams and two elective exams that provide a valid and reliable measure of technical proficiency and expertise.

Microsoft Certified Database Administrators are required to pass three core exams and one elective exam that provide a valid and reliable measure of technical proficiency and expertise.

Microsoft Certified Solution Developers are required to pass two core Microsoft Windows operating system technology exams and two BackOffice technology elective exams.

Microsoft Certified Trainers are required to meet instructional and technical requirements specific to each Microsoft Official Curriculum course they are certified to deliver. In the United States and Canada, call Microsoft at (800) 636-7544 for more information on becoming a Microsoft Certified Trainer or visit http://www.microsoft.com/train_cert/mct/. Outside the United States and Canada, contact your local Microsoft subsidiary.

Technical Training for Computer Professionals

Technical training is available in a variety of ways, with instructor-led classes, online instruction, or self-paced training available at thousands of locations worldwide.

Self-Paced Training

For motivated learners who are ready for the challenge, self-paced instruction is the most flexible, cost-effective way to increase your knowledge and skills.

A full line of self-paced print and computer-based training materials is available direct from the source—Microsoft Press. Microsoft Official Curriculum courseware kits from Microsoft Press are designed for advanced computer system professionals. These resources are available from Microsoft Press and the Microsoft Developer Division. Self-paced training kits from Microsoft Press feature print-based instructional materials, along with CD-ROM-based product software, multimedia presentations, lab exercises, and practice files. The Mastering Series provides in-depth, interactive training on CD-ROM for experienced developers. They're both great ways to prepare for Microsoft Certified Professional exams.

Online Training

For a more flexible alternative to instructor-led classes, turn to online instruction. It's as near as the Internet and it's ready whenever you are. Learn at your own pace and on your own schedule in a virtual classroom, often with easy access to an online instructor. Without ever leaving your desk, you can gain the expertise you need. Online instruction covers a variety of Microsoft products and technologies. It includes options ranging from Microsoft Official Curriculum to choices available nowhere else. It's training on demand, with access to learning resources 24 hours a day. Online training is available through Microsoft Certified Technical Education Centers.

Microsoft Certified Technical Education Centers

Microsoft Certified Technical Education Centers (CTECs) are the best source for instructor-led training that can help you prepare to become a Microsoft Certified Professional. The Microsoft CTEC program is a worldwide network of qualified technical training organizations that provide authorized delivery of Microsoft Official Curriculum courses by Microsoft Certified Trainers to computer professionals.

For a listing of CTEC locations in the United States and Canada, visit http://www.microsoft.com/CTEC/default.htm.

Technical Support

Every effort has been made to ensure the accuracy of this book and the contents of the companion CD-ROMs. If you have comments, questions, or ideas regarding this book or the companion CD-ROMs, please send them to Microsoft Press using either of the following methods:

E-mail:

tkinput@microsoft.com

Postal Mail:

Microsoft Press

Attn: MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation Editor

One Microsoft Way

Redmond, WA 98052-6399

Microsoft Press provides corrections for books through the World Wide Web at the following address:

http://mspress.microsoft.com/support/.

Please note that product support is not offered through the above mail addresses. For further information regarding Microsoft software support options, please connect to http://www.microsoft.com/support/ or call Microsoft Support Network Sales at (800) 936-3500.

The Evaluation Edition of Microsoft SQL Server 2000 included with this book is unsupported by both Microsoft and Microsoft Press, and should not be used on a primary work computer. For online support information relating to the full version of Microsoft SQL Server 2000 that might also apply to the Evaluation Edition, you can connect to http://support.microsoft.com/.

For information about ordering the full version of any Microsoft software, please call Microsoft Sales at (800) 426-9400 or visit http://www.microsoft.com. Information about any issues relating to the use of this evaluation edition with this training kit is posted to the Support section of the Microsoft Press Web site (http://mspress.microsoft.com/support/).