| Acknowledgments | xxv |
| Introduction | xxvii |
| PART I INTRODUCTION TO MICROSOFT SQL SERVER | |
| 1 Overview of Microsoft SQL Server | 2 |
| SQL Server Systems | 3 |
| New Features and Enhancements of SQL Server 2000 | 6 |
| Summary | 13 |
| 2 The Microsoft Windows 2000 Platform | 14 |
| The Windows 2000 Family | 15 |
| Windows 2000 Professional | 15 |
| Windows 2000 Server | 16 |
| Windows 2000 Advanced Server | 16 |
| Windows 2000 Datacenter Server | 16 |
| Windows 2000 Operating System Family Differences | 17 |
| Windows 2000 Components and Features | 17 |
| Reliability | 17 |
| Security | 19 |
| Ease of Use | 20 |
| System Administration and Deployment | 22 |
| The Mobile Experience | 26 |
| Performance | 27 |
| Internet Access | 29 |
| Active Directory | 30 |
| Summary | 33 |
| 3 Roles and Responsibilities of the Microsoft SQL Server DBA | 34 |
| Basic and Optional Duties of the SQL Server DBA | 35 |
| Installation and Configuration | 36 |
| Security | 37 |
| Operations | 38 |
| Service Levels | 39 |
| System Uptime | 40 |
| Documentation | 42 |
| Design and Development | 45 |
| Miscellaneous | 47 |
| DBA Tips and Techniques | 48 |
| Dealing with the User Community | 48 |
| System Tuning | 49 |
| Dealing with a Crisis | 50 |
| Summary | 51 |
| PART II INSTALLATION AND SETUP | |
| 4 Designing a Microsoft SQL Server System | 54 |
| System Requirements | 55 |
| System Application | 56 |
| Service Level Requirements | 58 |
| System Components and Options | 59 |
| Windows 2000 Versions | 59 |
| SQL Server Versions | 61 |
| Version Comparison | 62 |
| System Options | 62 |
| Database Layout | 64 |
| Transaction Log | 64 |
| Data Files | 65 |
| Application | 65 |
| Architecture | 66 |
| Performance and Scalability | 68 |
| Summary | 68 |
| 5 I/O Subsystem Configuration and Planning | 70 |
| Disk Drive Performance Characteristics | 71 |
| Disk Drive Construction | 72 |
| Disk Drive Characteristics | 73 |
| Disk Drive Specifications | 75 |
| Disk Drive Performance | 76 |
| Solutions to the Disk Performance Limitation Problem | 78 |
| RAID Overview | 78 |
| I/O Subsystem Concepts | 79 |
| Caching Controllers | 79 |
| Disk Drive Caches | 80 |
| Internal vs. External RAID | 80 |
| Storage Area Networks | 81 |
| Controller and Bus Bandwidth Issues | 82 |
| High-End I/O Subsystems | 82 |
| Elevator Sorting | 82 |
| Disk Reliability | 84 |
| Overview of Common RAID Levels | 84 |
| RAID 0 | 85 |
| RAID 1 | 86 |
| RAID 5 | 88 |
| RAID 10 | 91 |
| RAID Level Performance Comparison | 92 |
| Read Performance | 92 |
| Write Performance | 93 |
| Disk Calculations | 94 |
| RAID Comparison | 95 |
| I/O Latencies and SQL Server | 96 |
| Planning the SQL Server Disk Layout | 97 |
| Determine I/O Requirements | 97 |
| Plan the Disk Layout | 99 |
| Implement the Configuration | 100 |
| I/O Subsystem Tips and Recommendations | 102 |
| Summary | 103 |
| 6 Capacity Planning | 104 |
| Types of Capacity Planning | 105 |
| History of Capacity Planning | 107 |
| Transaction Processing | 109 |
| OLTP Transactions | 109 |
| DSS Transactions | 110 |
| Principles of Capacity Planning | 111 |
| CPU Utilization | 112 |
| Page Faulting | 116 |
| Capacity Planning for Memory | 118 |
| Collecting Memory Usage Data | 120 |
| Analyzing Memory Data | 121 |
| Capacity Planning for the Processor | 123 |
| Fault Tolerance | 124 |
| Collecting Usage Data for a Single CPU | 129 |
| Collecting Usage Data for Multiple CPUs | 130 |
| Capacity Planning for the Disk Subsystem | 131 |
| Disk Drives for Windows 2000 and SQL Server | 132 |
| Disk Drives for the Log Files | 133 |
| Disk Drives for the Database | 134 |
| Disk Drives Needed for the System | 136 |
| Collecting Disk Usage Data | 136 |
| Analyzing Disk Usage Data | 137 |
| Capacity Planning for the Network | 138 |
| Collecting Network Usage Data | 139 |
| Analyzing Network Usage Data | 139 |
| Choosing the Data to Collect | 140 |
| Collecting Process Data | 140 |
| Analyzing Process Data | 141 |
| Summary | 143 |
| 7 Installing Microsoft SQL Server | 144 |
| Server Installation | 145 |
| Upgrading from Earlier Versions | 153 |
| Client Installation | 161 |
| Summary | 161 |
| 8 Managing Microsoft SQL Server Services | 162 |
| SQL Server Services | 163 |
| Using SQL Server Service Manager | 165 |
| Using Windows 2000 Service Control Manager | 166 |
| Using SQL Server Enterprise Manager | 168 |
| Managing SQL Server | 168 |
| Managing Other Services | 175 |
| Summary | 177 |
| 9 Creating Databases | 178 |
| Database Structure | 179 |
| Files | 179 |
| Filegroups | 180 |
| Rules and Recommendations | 184 |
| Automatic File Growth | 185 |
| System Databases | 186 |
| Database Creation | 187 |
| Using the Create Database Wizard | 188 |
| Using Enterprise Manager | 193 |
| Using T-SQL Commands | 195 |
| Database Viewing | 198 |
| Using Enterprise Manager | 198 |
| Using SQL | 200 |
| Database Deletion | 200 |
| Using Enterprise Manager | 200 |
| Using SQL | 201 |
| Summary | 202 |
| 10 Creating Database Tables | 204 |
| Laying the Groundwork | 205 |
| Reviewing Table Basics | 206 |
| Defining a Database Table | 206 |
| Using System Data Types | 207 |
| Working with User-Defined Data Types | 212 |
| Creating Tables in a Filegroup | 218 |
| Creating the Product_Info Table in a Filegroup | 219 |
| Using Null Values | 220 |
| Creating the Product_Info Table Using NULLs | 221 |
| Adding the IDENTITY Property to the Product_Info Table | 222 |
| Creating a Table Using Enterprise Manager | 223 |
| Summary | 227 |
| 11 Microsoft SQL Server on the Network | 228 |
| Overview of Network Services | 229 |
| SQL Server APIs | 231 |
| DB-LIB Connectivity | 232 |
| ODBC Connectivity | 232 |
| ODBC Connection Pooling | 232 |
| Other APIs | 233 |
| Network Libraries | 233 |
| The SQL Server 2000 Server Network Utility | 234 |
| The SQL Server 2000 Client Network Utility | 235 |
| SQL Server Net-Libraries and Protocols | 239 |
| Network Components and SQL Server Performance | 240 |
| The Software Layer—Network Protocols | 241 |
| The Hardware Layer | 242 |
| Network Monitoring | 244 |
| Monitoring Performance | 244 |
| Determining Whether You Have a Problem | 245 |
| Finding Solutions to Network Problems | 246 |
| Summary | 247 |
| 12 Microsoft SQL Server and Microsoft Cluster Services | 248 |
| Types of Failure | 249 |
| Overview of MSCS | 250 |
| Basic Concepts | 252 |
| Cluster Components | 253 |
| Cluster Application Types | 260 |
| MSCS Modes | 260 |
| Examples of Clustered Systems | 263 |
| Example 1—High-Availability System with Static Load Balancing | 263 |
| Example 2—Hot Spare System with Maximum Availability | 264 |
| Example 3—Partial Server Cluster | 265 |
| Example 4—Virtual Server Only, with No Failover | 266 |
| SQL Server Cluster Configuration | 266 |
| Planning Your Configuration | 267 |
| Installing SQL Server for Clustering | 268 |
| Using a Three-Tier Application | 273 |
| Beyond MSCS | 273 |
| Summary | 274 |
| PART III USING MICROSOFT SQL SERVER | |
| 13 Introduction to Transact-SQL and SQL Query Analyzer | 276 |
| What Is SQL? | 277 |
| DDL | 278 |
| DML | 279 |
| What Is T-SQL? | 281 |
| A Review of New T-SQL Features | 281 |
| System Stored Procedures | 281 |
| System Tables | 282 |
| Functions | 283 |
| Data Types | 284 |
| Statements | 285 |
| How to Use T-SQL | 285 |
| The ISQL Utility | 285 |
| The OSQL Utility | 286 |
| The Query Analyzer | 287 |
| T-SQL Scripting | 289 |
| Summary | 291 |
| 14 Retrieving Data Using Transact-SQL | 292 |
| The SELECT Statement | 293 |
| The SELECT Clause | 294 |
| The FROM Clause | 298 |
| The INTO Clause | 304 |
| The WHERE Clause and Search Conditions | 305 |
| The GROUP BY Clause | 317 |
| The HAVING Clause | 320 |
| The ORDER BY Clause | 322 |
| The UNION Operator | 325 |
| T-SQL Functions | 329 |
| Aggregate Functions | 330 |
| Other Uses for SELECT | 332 |
| Summary | 333 |
| 15 Managing Tables Using T-SQL and Enterprise Manager | 334 |
| Modifying a Table Using T-SQL | 337 |
| Altering Columns | 337 |
| Adding Columns | 340 |
| Dropping Columns | 342 |
| Renaming Columns | 342 |
| Modifying a Table Using Enterprise Manager | 343 |
| Altering Columns | 344 |
| Adding Columns | 347 |
| Dropping Columns | 347 |
| Creating and Using a Database Diagram | 348 |
| The Effects of Altering a Table | 351 |
| Dropping a Table | 352 |
| Using T-SQL to Drop a Table | 352 |
| Using Enterprise Manager to Drop a Table | 353 |
| Summary | 355 |
| 16 Creating and Using Defaults, Constraints, and Rules | 356 |
| Defaults | 357 |
| Defining and Modifying Defaults by Using T-SQL | 358 |
Defining and Modifying Defaults
by Using Enterprise Manager | 364 |
| Constraints | 370 |
| Creating and Modifying Constraints by Using T-SQL | 370 |
| Creating and Modifying Constraints by Using Enterprise Manager | 381 |
| Rule Objects | 394 |
| Creating a Rule Object by Using T-SQL | 395 |
| Creating a Rule Object by Using Enterprise Manager | 395 |
| Summary | 397 |
| 17 Creating and Using Indexes | 398 |
| What Is an Index? | 399 |
| Indexing Concepts | 402 |
| Index Keys | 402 |
| Index Uniqueness | 405 |
| Types of Indexes | 406 |
| Creating Indexes | 409 |
| Using the Create Index Wizard | 409 |
| Using Transact-SQL | 415 |
| Using Fill Factor to Avoid Page Splits | 416 |
| Using the Full-Text Indexing Wizard | 418 |
| Creating Full-Text Indexes by Using Stored Procedures | 422 |
| Using the Full-Text Index | 423 |
| Rebuilding Indexes | 424 |
| Updating Index Statistics | 425 |
| Using Indexes | 426 |
| Using Hints | 427 |
| Using Query Analyzer | 428 |
| Designing Effective Indexes | 430 |
| Characteristics of an Effective Index | 430 |
| When to Use Indexes | 431 |
| Index Guidelines | 432 |
| Summary | 432 |
| 18 Creating and Using Views | 434 |
| What Is a View? | 435 |
| View Concepts | 436 |
| Types of Views | 436 |
| Advantages of Views | 436 |
| View Restrictions | 437 |
| Creating Views | 437 |
| Using T-SQL to Create a View | 437 |
| Using Enterprise Manager to Create a View | 446 |
| Using the Create View Wizard to Create a View | 449 |
| Tips for Views | 452 |
| Altering and Dropping Views | 453 |
| Using Enterprise Manager to Alter and Drop Views | 453 |
| Using T-SQL to Alter and Drop Views | 456 |
| View Enhancements in SQL Server 2000 | 457 |
| Updatable, Distributed Partitioned Views | 457 |
| Indexed Views | 464 |
| Summary | 465 |
