| About This Book | xiii |
| Intended Audience | xiii |
| Prerequisites | xiv |
| Reference Materials | xiv |
| About the CD-ROM | xiv |
| Features of this Book | xv |
| The Microsoft Certified Professional Program | xxii |
| Technical Support | xxvii |
| CHAPTER 1 Introduction to Microsoft SQL Server 2000 | 1 |
| About This Chapter | 1 |
| Before You Begin | 1 |
| Lesson 1: Overview of SQL Server 2000 | 2 |
| What Is SQL Server 2000? | 2 |
| SQL Server 2000 Features | 4 |
| Editions of SQL Server 2000 | 7 |
| Lesson Summary | 8 |
| Lesson 2: Components of SQL Server 2000 | 9 |
| Overview of the SQL Server 2000 Components | 9 |
| SQL Server 2000 Relational Database Engine | 9 |
| SQL Server 2000 Replication | 10 |
| SQL Server 2000 DTS | 11 |
| SQL Server 2000 Analysis Services | 12 |
| SQL Server 2000 English Query | 12 |
| SQL Server Meta Data Services | 13 |
| SQL Server Books Online | 14 |
| SQL Server 2000 Tools | 14 |
| Lesson Summary | 19 |
| Lesson 3: Overview of SQL Server 2000 Architecture | 20 |
| Database Architecture | 20 |
| Relational Database Engine Architecture | 28 |
| Administration Architecture | 33 |
| Data Warehousing and Online Analytical Processing (OLAP) | 38 |
| Application Development Architecture | 39 |
| Lesson Summary | 41 |
| Review | 42 |
| CHAPTER 2 Using Transact-SQL on a SQL Server Database | 45 |
| About This Chapter | 45 |
| Before You Begin | 45 |
| Lesson 1: SQL Server Programming Tools | 46 |
| SQL Query Analyzer | 46 |
| isqlw Command-Prompt Utility | 59 |
| isql Command Prompt Utility | 60 |
| osql Command-Prompt Utility | 60 |
| Lesson Summary | 65 |
| Lesson 2: Introduction to Transact-SQL | 66 |
| Overview of Transact-SQL | 66 |
| Transact-SQL Statements | 66 |
| Lesson Summary | 75 |
| Lesson 3: Transact-SQL Syntax Elements | 77 |
| Identifiers | 77 |
| Variables | 78 |
| Functions | 79 |
| Data Types | 82 |
| Expressions | 82 |
| Control-of-Flow Language Elements | 83 |
| Comments | 84 |
| Lesson Summary | 88 |
| Lesson 4: Executing Transact-SQL Statements | 89 |
| Single Transact-SQL Statements | 89 |
| Batches | 91 |
| Stored Procedures and Triggers | 93 |
| Transact-SQL Scripts | 94 |
| Lesson Summary | 95 |
| Review | 96 |
| CHAPTER 3 Designing a SQL Server Database | 99 |
| About This Chapter | 99 |
| Before You Begin | 99 |
| Lesson 1: Introduction to Database Design | 100 |
| Components of a SQL Server Database | 100 |
| Normalizing a Database Design | 101 |
| Entity Relationships | 105 |
| Lesson Summary | 111 |
| Lesson 2: Planning a SQL Server Database | 112 |
| Files and Filegroups | 112 |
| Transaction Logs | 114 |
| Environment | 115 |
| SQL Server Installation | 116 |
| Security | 117 |
| Lesson Summary | 118 |
| Lesson 3: Identifying System Requirements | 119 |
| The Process of Identifying System Requirements | 119 |
| Identifying System Goals | 120 |
| Identifying the Amount and Types of Data | 122 |
| Identifying How the Data Will Be Used | 123 |
| Identifying Business Rules of the System | 123 |
| Lesson Summary | 127 |
| Lesson 4: Developing a Logical Data Model | 129 |
| Identifying Entities and Their Attributes | 129 |
| Identifying Relationships Between Entities | 131 |
| Identifying Constraints on Data | 132 |
| Lesson Summary | 139 |
| Review | 141 |
| CHAPTER 4 Implementing SQL Server Databases and Tables | 143 |
| About This Chapter | 143 |
| Before You Begin | 143 |
| Lesson 1: Creating and Managing a SQL Server Database | 144 |
| Creating a SQL Server Database | 144 |
| Managing a SQL Server Database | 146 |
| Exercise 1: Creating and Managing a Database | 150 |
| Lesson Summary | 154 |
| Lesson 2: Identifying Data Types | 155 |
| System-Supplied Data Types | 155 |
| User-Defined Data Types | 160 |
| Exercise 2: Identifying Column Data Types | 160 |
| Lesson Summary | 162 |
| Lesson 3: Creating and Managing Tables | 163 |
| Creating Tables in a SQL Server Database | 163 |
| Managing Tables in a SQL Server Database | 170 |
| Exercise 3: Creating and Managing Tables in a SQL Server Database | 172 |
| Lesson Summary | 175 |
| Review | 176 |
| CHAPTER 5 Implementing Data Integrity | 179 |
| About This Chapter | 179 |
| Before You Begin | 179 |
| Lesson 1: Introduction to Data Integrity | 180 |
| Enforcing Data Integrity | 180 |
| Types of Data Integrity | 182 |
| Exercise 1: Identifying the Properties Used to Ensure Data Integrity | 184 |
| Lesson Summary | 185 |
| Lesson 2: Implementing Integrity Constraints | 187 |
| Introduction to Integrity Constraints | 187 |
| PRIMARY KEY Constraints | 188 |
| UNIQUE Constraints | 190 |
| FOREIGN KEY Constraints | 191 |
| CHECK Constraints | 194 |
| Exercise 2: Adding Constraints to Existing Tables | 196 |
| Lesson Summary | 201 |
| Review | 202 |
| CHAPTER 6 Accessing and Modifying Data | 205 |
| About This Chapter | 205 |
| Before You Begin | 205 |
| Lesson 1: Accessing Data in a SQL Server Database | 206 |
| The Fundamentals of a SELECT Statement | 206 |
| The SELECT Clause | 207 |
| The INTO Clause | 209 |
| The FROM Clause | 209 |
| The WHERE, GROUP BY, and HAVING Clauses | 209 |
| The ORDER BY Clause | 211 |
| Exercise 1: Using SELECT Statements to Access Data | 211 |
| Lesson Summary | 214 |
| Lesson 2: Using Advanced Query Techniques to Access Data | 215 |
| Using Joins to Retrieve Data | 215 |
| Defining Subqueries inside SELECT Statements | 218 |
| Summarizing Data | 221 |
| Exercise 2: Using Advanced Query Techniques to Retrieve Data | 223 |
| Lesson Summary | 228 |
| Lesson 3: Modifying Data in a SQL Server Database | 229 |
| Inserting Data into a SQL Server Database | 229 |
| Modifying Data in a SQL Server Database | 232 |
| Deleting Data from a SQL Server Database | 235 |
| Exercise 3: Modifying Data in a SQL Server Database | 236 |
| Lesson Summary | 239 |
| Review | 240 |
| CHAPTER 7 Managing and Manipulating Data | 243 |
| About This Chapter | 243 |
| Before You Begin | 243 |
| Lesson 1: Importing and Exporting Data | 244 |
| Using the bcp Utility and the BULK INSERT Statement | 244 |
| Using DTS | 248 |
| Exercise 1: Importing and Exporting Data | 251 |
| Lesson Summary | 254 |
| Lesson 2: Using Distributed Queries to Access External Data | 256 |
| Introduction to Distributed Queries | 256 |
| Using Linked Server Names in Distributed Queries | 257 |
| Using Ad Hoc Computer Names in Distributed Queries | 259 |
| Exercise 2: Using Distributed Queries to Access External Data | 260 |
| Lesson Summary | 262 |
| Lesson 3: Using Cursors to Retrieve Data | 263 |
| Introduction to Cursors | 263 |
| Fetching and Scrolling | 268 |
| Controlling Cursor Behavior | 268 |
| Cursor Locking | 269 |
| Exercise 3: Creating a Cursor to Retrieve Data | 269 |
| Lesson Summary | 271 |
| Lesson 4: Retrieving XML Data | 272 |
| Introduction to XML | 272 |
| Using the FOR XML Clause to Retrieve Data | 273 |
| Using the OPENXML Function to Access XML Data | 275 |
| Exercise 4: Retrieving XML Data | 279 |
| Lesson Summary | 280 |
| Review | 281 |
| CHAPTER 8 Implementing Stored Procedures | 283 |
| About This Chapter | 283 |
| Before You Begin | 283 |
| Lesson 1: Introduction to Stored Procedures | 284 |
| Purpose and Advantages of Stored Procedures | 284 |
| Categories of Stored Procedures | 286 |
| Lesson Summary | 291 |
| Lesson 2: Creating, Executing, Modifying, and Deleting Stored Procedures | 292 |
| How a Procedure Is Stored | 292 |
| Methods for Creating Stored Procedures | 293 |
| Executing a Stored Procedure | 299 |
| Modifying Stored Procedures | 302 |
| Deleting Stored Procedures | 303 |
| Lesson Summary | 309 |
| Lesson 3: Programming Stored Procedures | 310 |
| Parameters and Variables | 310 |
| The RETURN Statement and Error Handling | 312 |
| Nesting Procedures | 318 |
| Cursors | 318 |
| Lesson Summary | 326 |
| Review | 327 |
| CHAPTER 9 Implementing Triggers | 329 |
| About This Chapter | 329 |
| Before You Begin | 330 |
| Lesson 1: Introduction to Triggers | 331 |
| Extending Data Integrity with Triggers | 331 |
| Trigger Events | 333 |
| Lesson Summary | 337 |
| Lesson 2: Creating and Managing Triggers | 339 |
| Creating Triggers Using Transact-SQL | 339 |
| Creating a Trigger Using Enterprise Manager | 342 |
| Trigger Management | 342 |
| Viewing, Dropping, and Disabling Triggers | 344 |
| Lesson Summary | 349 |
| Lesson 3: Programming Triggers | 350 |
| The Inserted and Deleted Pseudo Tables | 350 |
| Trigger Syntax, System Commands, and Functions | 351 |
| Common Trigger Programming Tasks | 353 |
| Lesson Summary | 359 |
| Review | 361 |
| CHAPTER 10 Implementing Views | 363 |
| About This Chapter | 363 |
| Before You Begin | 363 |
| Lesson 1: Introduction to Views | 364 |
| Overview of Views | 364 |
| Scenarios for Using Views | 365 |
| Lesson Summary | 367 |
| Lesson 2: Creating, Modifying, and Deleting Views | 368 |
| Creating Views | 368 |
| Modifying Views | 375 |
| Deleting Views | 376 |
| Exercise 1: Creating and Modifying a View | 376 |
| Lesson Summary | 378 |
| Lesson 3: Accessing Data through Views | 379 |
| Viewing Data through Views | 379 |
| Modifying Data through Views | 379 |
| Exercise 2: Using the AuthorsBooks View to Access Data | 384 |
| Lesson Summary | 387 |
| Review | 388 |
| CHAPTER 11 Implementing Indexes | 391 |
| About This Chapter | 391 |
| Before You Begin | 391 |
| Lesson 1: Index Architecture | 392 |
| Purpose and Structure | 392 |
| Index Types | 393 |
| Index Characteristics | 395 |
| Index Information | 397 |
| Full-Text Indexing | 399 |
| Lesson Summary | 402 |
| Lesson 2: Index Creation and Administration | 403 |
| Index Creation | 403 |
| Index Administration | 408 |
| Choosing to Index | 410 |
| Index Performance | 412 |
| Lesson Summary | 417 |
| Review | 418 |
| CHAPTER 12 Managing SQL Server Transactions and Locks | 421 |
| About This Chapter | 421 |
| Before You Begin | 421 |
| Lesson 1: Transaction and Locking Architecture | 422 |
| Transaction Log Architecture | 422 |
| Concurrency Architecture | 427 |
| Locking Architecture | 427 |
| Distributed Transaction Architecture | 429 |
| Exercise 1: Accessing and Modifying the Transaction Log | 429 |
| Lesson Summary | 431 |
| Lesson 2: Managing SQL Server Transactions | 432 |
| Overview of SQL Server Transactions | 432 |
| Types of Transactions | 435 |
| Distributed Transactions | 440 |
| Exercise 2: Implementing Explicit Transactions | 442 |
| Lesson Summary | 445 |
| Lesson 3: Managing SQL Server Locking | 446 |
| Types of Concurrency Problems | 446 |
| Optimistic and Pessimistic Concurrency | 448 |
| Isolation Levels | 448 |
| Customizing Locking | 449 |
| Exercise 3: Configuring Transaction Properties | 456 |
| Lesson Summary | 457 |
| Review | 459 |
| CHAPTER 13 Designing and Administering SQL Server 2000 Security | 461 |
| About This Chapter | 461 |
| Before You Begin | 461 |
| Lesson 1: Overview of SQL Server 2000 Security | 462 |
| Physical Security | 462 |
| Network Protocol Security | 462 |
| Domain Security | 464 |
| Local Computer Security | 465 |
| SQL Server Security | 465 |
| Application | 471 |
| Lesson Summary | 472 |
| Lesson 2: Designing a Database Security Plan | 473 |
| Requirements | 473 |
| Nesting and Ownership Chains | 475 |
| Security Design Recommendations | 476 |
| Lesson Summary | 477 |
| Lesson 3: Database Security Implementation and Administration | 479 |
| Administering Authentication | 479 |
| Administering Authorization | 481 |
| Administering Permissions | 483 |
| Administering Roles | 487 |
| Lesson Summary | 494 |
| Review | 496 |
| CHAPTER 14 SQL Server Monitoring and Tuning | 499 |
| About This Chapter | 499 |
| Before You Begin | 499 |
| Lesson 1: Monitoring Databases with SQL Profiler | 500 |
| SQL Server Monitoring | 500 |
| SQL Profiler | 501 |
| Lesson Summary | 510 |
| Lesson 2: Index Tuning and Database Partitioning | 511 |
| SQL Server Tuning | 511 |
| Index Tuning Wizard Overview | 511 |
| Partitioning Overview | 514 |
| Lesson Summary | 518 |
| Review | 519 |
| APPENDIX A Questions and Answers | 521 |
| GLOSSARY | 575 |
| INDEX | 623 |
