Introduction 1
Chapter 1 Introducing Microsoft SQL Server 2000 3
Northwind Distributors Case Study 3
Microsoft SQL Server 2000 Overview 4
Implementation Architectures 4
How Clients Communicate with SQL Server 6
SQL Server Services 8
Default Databases 9
What a Database Includes 10
New Features in SQL Server 2000 12
Planning a SQL Server 2000 Installation 15
What Is the Primary Function of This Server? 15
What Are the Performance and Redundancy Expectations? 16
How Will SQL Server Be Licensed? 17
Will Replication Be Required? 17
Chapter Review 18
Questions 18
Answers 19
Chapter 2 Installing SQL Server 2000 21
Installing SQL Server 2000 21
Performing a Local Installation 21
Performing a Remote Installation 25
Installing the Client Utilities 27
Unattended Installation 28
Upgrading to SQL Server 2000 31
Upgrade Requirements 31
Upgrading SQL Server 7.0 to SQL Server 2000 31
Upgrading SQL Server 6.5 to SQL Server 2000 31
Connectivity Problems 35
Uninstalling SQL Server 2000 35
Failed Installations and General Troubleshooting 35
Troubleshooting SQL Server Installations 35
Chapter Review 36
Questions 36
Answers 38
Chapter 3 Configuring Microsoft SQL Server 2000 39
Verifying Your SQL Server 2000 Installation 39
Windows 2000 Service Control Manager 39
SQL Server Service Manager 40
SQL Server Administration Tools 42
SQL Server Enterprise Manager 42
Client Network Utility 45
Configure SQL XML Support in IIS 46
Import and Export Data 46
SQL Profiler 47
Server Network Utility 48
SQL Query Analyzer 48
Common Configuration Tasks 50
SQL Server Startup 50
Connection Management 52
Configuring Mail Support 56
Configuring Network Libraries 57
Introducing the Structured Query Language 58
Transact-SQL Commands 58
Using the SQL Query Designer 64
Chapter Review 66
Questions 67
Answers 69
Chapter 4 Creating SQL Server 2000 Databases 70
What Is a SQL Server Database? 70
Where the Data Is Stored 71
Creating a Database 72
Modifying a Database 76
Available Database Options 76
Setting Database Options 80
Changing the Size of a Database 82
Attaching and Detaching a Database 84
Chapter Review 86
Questions 86
Answers 88
Chapter 5 Creating and Managing Database Objects 89
Introducing Tables 89
Primary and Foreign Keys 90
Creating a Table 93
Modifying Tables 98
Deleting Tables 101
Introducing the Database Designer 101
Introducing Constraints 106
Introducing Indexes 109
Creating an Index 111
Deleting an Index 112
Introducing Stored Procedures 113
Executing Stored Procedures 115
Creating Stored Procedures 116
Modifying a Stored Procedure 118
Deleting a Stored Procedure 118
Introducing Triggers 119
Creating a New Trigger 120
Reviewing and Editing an Existing Trigger 123
Introducing Views 125
Examining Existing Views 126
Accessing the Data in a View 126
Creating Views 127
Modifying Views 130
Deleting Views 132
Chapter Review 132
Questions 132
Answers 134
Chapter 6 Managing and Monitoring SQL Server 2000 Security 136
Configuring the SQL Server Security Mode 136
Windows Authentication Mode 136
Mixed Mode 138
Creating and Managing Logins 139
Delegation and Impersonation 143
Creating and Managing User Accounts 143
Creating and Managing Security Roles 145
Server Roles 146
Database Roles 147
Creating Database Roles 149
Setting permissions in a database 150
Enforce and manage security 154
Manage Security Auditing 154
Auditing Security with the SQL Profiler(SP) 155
Chapter Reivew 159
Questions 160
Answers 162
Chapter 7 Managing,Monitoring,and Troubleshooting SQL Server 2000 Databases 163
Optimizing Database Performance 163
Indexes 164
The Index Tuning Wizard 164
Customize Locking 165
Recompiling 170
Optimizing Hardware Use 170
Managing Server Memory 171
Managing Server Processors 173
Managing Hard Drives 174
Optimizing Files and Filegroups 174
Performing Disaster Recovery Operations 176
Performing Backups 176
Recovering and Restoring Data 182
Configuring,Maintaining,and Troubleshooting Log Shipping 185
Performing Integrity Checks 187
Using SQL Server Agent 190
Creating and Managing Jobs 190
Configuring Operators and Alerts 193
Chapter Review 195
Questions 195
Answers 197
Chapter 8 Extracting and Transforming Data with SQL Server 2000 198
Supporting the Extensible Markup Language(XML) 198
XML Overview 199
Setting up IIS 201
Importing and Exporting Data 205
Using the Bulk Copy Program(BCP) 206
Using Data Transformation Services(DTS) 210
Developing and Managing DTS Packages 211
Introducing DTS 211
Transforming Data with DTS 216
Managing Linked Servers 218
Creating a Linked Server 218
Executing Heterogeneous Queries 220
Managing Replication Services 220
Replication Service Architecture 220
Implementing Replication 222
Chapter Review 240
Questions 241
Answers 242
Chapter 9 Developing a Logical Data Model 243
Defining Entities 244
Determining the Primary Entities 244
Defining the Attributes of Your Entities 244
Selecting a Level of Normalization 245
Primary Key 246
Designing Entity Keys 246
Foreign Key 247
UNIQUE Constraints 248
Designing Attribute Domain Integrity 249
CHECK Constraints 250
Data Types 250
Creating a User-Defined Data Type with the Enterprise Manager 252
Selecting Attribute Properties 253
Designing Logical Data Models from Business Scenarios 255
Exercise#1:Southwest Bookstore 255
Exercise#1:Southwest Bookstore Suggested Solution 256
Chapter Review 257
Questions 258
Answers 260
Chapter 10 Implementing the Physical Database 261
Creating a New Database 261
Database Properties 263
Creating the Southwest Bookstore Database 264
Creating Database Objects 268
Creating Tables 268
Creating Indexes 276
Creating Views 278
Creating Stored Procedures 281
Creating User-Defined Functions 285
Chapter Review 288
Questions 288
Answers 289
Chapter 11 Retrieving and Modifying Data 291
Performing Bulk Data Operations 291
Using the Bulk Copy Program(BCP) 292
Using the Bulk Insert T-SQL Command 294
Using Data Transformation Services(DTS) 296
Managing Data Using Transact-SQL 304
Transact-SQL Commands 305
Using Cursors with Transact-SQL 310
Navigating a Cursor 312
Accessing Data in a Heterogeneous Environment 313
Creating a Linked Server 313
Executing Queries to Linked Servers 315
Accessing Data Using Heterogeneous Queries 315
Retuming Data Using XML 317
Extensible Markup Language(XML)Overview 317
Accessing XML Data with a URL 319
Chapter Review 321
Questions 322
Answers 324
Chapter 12 Programming Business Logic 325
Designing Stored Procedures 325
Creating Stored Procedures 326
Using Input Parameters 329
Using Output Parameters 330
Modifying a Stored Procedure Using T-SQL 331
Deleting a Stored Procedure 332
Creating Triggers 333
Creating a New Trigger 333
Reviewing and Editing an Existing Trigger 337
Developing User-Defined Functions 339
Creating Views 340
Implementing Views 341
Accessing the Data in a View 343
Modifying Views 344
Deleting Views 346
Chapter Review 346
Questions 347
Answers 349
Windows Authentication Mode 350
How SQL Server Manages Database Access 350
Chapter 13 Designing a Database Security Plan 350
Mixed Mode 351
SQL Server Logins 351
SQL Server Roles 354
Controlling Table Access 355
Restricting Access at the Table Level 355
Restricting Access at the Column Level 356
Controlling Access Using Other Database Objects 358
Using Stored Procedures 358
Using Triggers 359
Using User-Defined Functions 360
Controlling Access Using Views 361
Using Application Roles 362
Chapter Review 363
Questions 364
Answers 365
Chapter 14 Tuning and Optimizing Data Access 367
Optimizing Database Performance 367
Indexes 368
Customize Locking 371
Analyzing Query Execution Plans 375
Interactively Executing a Stored Procedure 377
Using the SQL Profiler to Analyze Performance 380
Chapter Review 383
Questions 384
Answers 386
Appendix A Microsoft Certification Exam Prep 70-228 387
Questions 387
Answers 398
Appendix B Microsoft Certification Exam Prep 70-229 402
Questions 402
Answers 414