| |  | Copyright |
| | | About the Lead Authors |
| | | | About the Contributing Authors |
| | | | About the Technical Editors |
| |
| | | Acknowledgments |
| | | Introduction |
| | | | Who Is This Book's Intended Audience? |
| | | | The Layout of This Book |
| | | | Conventions Used in This Book |
| | | | Good Luck! |
| |
| | | Part I. Welcome to Microsoft SQL Server |
| | | | Chapter 1. The Microsoft SQL Server Environment |
| | | | Overview of Client/Server |
| | | | SQL Server 2000 Components and Features |
| | | | Summary |
| |
| | | | Chapter 2. SQL Server 2000 Environments and Editions |
| | | | SQL Server 2000 Environments |
| | | | SQL Server 2000 Editions |
| | | | SQL Server Licensing Models |
| | | | Summary |
| |
| | | | Chapter 3. What's New in SQL Server 2000 |
| | | | New SQL Server 2000 Features |
| | | | SQL Server 2000 Enhancements |
| | | | SQL Server and the .NET Framework |
| | | | Summary |
| |
| |
| | | Part II. SQL Server Tools and Utilities |
| | | | Chapter 4. SQL Server Enterprise Manager |
| | | | Establishing Server Groups and Registering SQL Server in Enterprise Manager |
| | | | Server Configuration |
| | | | Databases |
| | | | Security |
| | | | Server Management |
| | | | Data Transformation Services |
| | | | Support Services |
| | | | System Tools |
| | | | Replication |
| | | | Using the Database Taskpad |
| | | | Using the Query Designer |
| | | | Scripting Objects |
| | | | Getting Help |
| | | | Summary |
| |
| | | | Chapter 5. SQL Server Command-Line Utilities |
| | | | BCP |
| | | | Data Transformation Services Utilities |
| | | | ISQL/OSQL |
| | | | makepipe and readpipe |
| | | | ODBCcmpt |
| | | | odbcping |
| | | | rebuildm |
| | | | regrebld |
| | | | Replication Utilities |
| | | | SQLDiag |
| | | | SQLMaint |
| | | | SQLServr |
| | | | VSwitch |
| | | | Summary |
| |
| | | | Chapter 6. SQL Server Query Analyzer and SQL Debugger |
| | | | Query Analyzer |
| | | | The SQL Debugger |
| | | | Summary |
| |
| | | | Chapter 7. Using the SQL Server Profiler |
| | | | SQL Server Profiler Architecture |
| | | | Creating Traces |
| | | | Saving and Exporting Traces |
| | | | Replaying Trace Data |
| | | | Defining Server-Side Traces |
| | | | Profiler Usage Scenarios |
| | | | Summary |
| |
| |
| | | Part III. SQL Server Administration |
| | | | Chapter 8. Installing and Upgrading SQL Server |
| | | | Choosing a SQL Server Edition |
| | | | Hardware Requirements |
| | | | Software Requirements |
| | | | Selecting Installation Configuration Options |
| | | | Verifying Installation |
| | | | Starting, Stopping, and Pausing SQL Server |
| | | | Post-Installation Configuration |
| | | | Installation Troubleshooting |
| | | | Remote Installation |
| | | | Unattended Installation |
| | | | Installing a Named Instance |
| | | | Upgrading from Previous Versions |
| | | | Summary |
| |
| | | | Chapter 9. Client Installation and Configuration |
| | | | The SQL Server Client Architecture |
| | | | Installing Client Tools |
| | | | Client Configuration |
| | | | Network Protocol Support |
| | | | Installing ODBC |
| | | | Microsoft Driver for JDBC |
| | | | Connecting to SQL Server over the Internet |
| | | | Summary |
| |
| | | | Chapter 10. SQL Server System and Database Administration |
| | | | Responsibilities of the System Administrator |
| | | | System Databases |
| | | | System Tables |
| | | | System-Stored Procedures |
| | | | Useful System Procedures |
| | | | Other Methods of Querying the System Tables |
| | | | Summary |
| |
| | | | Chapter 11. Creating and Managing Databases |
| | | | What Makes Up a SQL Server Database? |
| | | | Data Storage in SQL Server |
| | | | Database Files |
| | | | Creating Databases |
| | | | Managing Databases |
| | | | Setting Database Options |
| | | | Summary |
| |
| | | | Chapter 12. Creating and Managing Tables in SQL Server |
| | | | Datatypes |
| | | | Numeric Datatypes |
| | | | Datatype Synonyms |
| | | | User-Defined Datatypes |
| | | | Creating Tables |
| | | | Creating Tables Using T-SQL |
| | | | Creating Tables Using Table Designer |
| | | | Viewing Table Properties and Data in Enterprise Manager |
| | | | Limitations on Table Size and Dimensions |
| | | | Dropping Tables |
| | | | Temporary Tables |
| | | | Summary |
| |
| | | | Chapter 13. Creating and Managing Indexes |
| | | | Types of Indexes |
| | | | Creating Indexes Using T-SQL |
| | | | Creating Indexes Using Enterprise Manager |
| | | | Managing Indexes |
| | | | Dropping Indexes |
| | | | Summary |
| |
| | | | Chapter 14. Implementing Data Integrity |
| | | | Types of Data Integrity |
| | | | Enforcing Data Integrity |
| | | | Constraints |
| | | | Rules |
| | | | Defaults |
| | | | Summary |
| |
| | | | Chapter 15. Security and User Administration |
| | | | An Overview of SQL Server Security |
| | | | Authentication Methods |
| | | | SQL Server Security: Logins |
| | | | SQL Server Security: Users |
| | | | SQL Server Security: Roles |
| | | | Managing SQL Server Logins |
| | | | Managing SQL Server Users |
| | | | Managing Database Roles |
| | | | Managing SQL Server Permissions |
| | | | SQL Server Permission Approaches |
| | | | Object Owners |
| | | | Using Encryption |
| | | | Summary |
| |
| | | | Chapter 16. Database Backup and Restore |
| | | | Developing a Backup and Restore Plan |
| | | | Why Back Up Your Databases? |
| | | | A Typical Backup and Restore Scenario |
| | | | Types of Backups |
| | | | Setting the Recovery Mode |
| | | | Backup Devices |
| | | | Media Sets and Families |
| | | | Creating Backup Devices with Transact-SQL |
| | | | Creating Backup Devices with SQL Enterprise Manager |
| | | | Backing Up the Database |
| | | | Backing Up the Transaction Log |
| | | | Restoring the Database |
| | | | Transact-SQL Restore Examples |
| | | | Restoring to a Different Database |
| | | | Restoring a File or Filegroup |
| | | | Restoring to a Point in Time |
| | | | Performing a Partial Database Restore |
| | | | Restoring the System Databases |
| | | | Additional Backup Considerations |
| | | | Summary |
| |
| | | | Chapter 17. Database Maintenance |
| | | | What Needs to Be Maintained |
| | | | The Database Maintenance Plan Wizard |
| | | | Setting Up Your Own Maintenance Tasks |
| | | | The Database Console Commands (DBCC) |
| | | | Using SQLMAINT.EXE for Database Maintenance |
| | | | Summary |
| |
| | | | Chapter 18. SQL Server Scheduling and Notification |
| | | | SQL Agent Overview |
| | | | Configuring SQL Server Agent |
| | | | Viewing the SQL Server Agent Error Log |
| | | | Operators |
| | | | Jobs |
| | | | Alerts |
| | | | Scripting Jobs and Alerts |
| | | | Multiserver Job Management |
| | | | Event Forwarding |
| | | | Summary |
| |
| | | | Chapter 19. Managing Linked and Remote Servers |
| | | | Remote Servers |
| | | | Linked Servers |
| | | | Adding, Dropping, and Configuring Linked Servers |
| | | | Mapping Local Logins to Logins on Linked Servers |
| | | | Obtaining General Information About Linked Servers |
| | | | Executing a Stored Procedure Via a Linked Server |
| | | | Setting Up Linked Servers Through Enterprise Manager |
| | | | Summary |
| |
| | | | Chapter 20. Importing and Exporting SQL Server Data Using BCP and DTS |
| | | | Bulk-Copy Program (BCP) |
| | | | The Bulk-Copy Program |
| | | | Logged and Non-Logged Operations |
| | | | The BULK INSERT Statement (Transact-SQL) |
| | | | Improving Load Performance |
| | | | BCP Extras |
| | | | Data Transformation Services (DTS) |
| | | | DTS Architecture and Concepts |
| | | | Package Execution Utilities |
| | | | Running the DTS Wizard |
| | | | DTS Designer |
| | | | A Bit More on Metadata |
| | | | Summary |
| |
| | | | Chapter 21. Administering Very Large SQL Server Databases |
| | | | Do I Have a Very Large Database (VLDB)? |
| | | | VLDB Maintenance Issues |
| | | | Partitioning Data |
| | | | Summary |
| |
| | | | Chapter 22. Data Replication |
| | | | What Is Replication? |
| | | | The Publisher, Distributor, and Subscriber Metaphor |
| | | | Replication Scenarios |
| | | | Replication Agents |
| | | | Planning for SQL Server Data Replication |
| | | | SQL Server Replication Types |
| | | | User Requirements Drive the Replication Design |
| | | | Setting Up Replication |
| | | | Scripting Replication |
| | | | Monitoring Replication |
| | | | Summary |
| |
| | | | Chapter 23. SQL Mail |
| | | | Setting Up an E-Mail Client/Profile |
| | | | Configuring SQL Mail |
| | | | Configuring SQLAgentMail |
| | | | SQL Mail Stored Procedures |
| | | | Summary |
| |
| | | | Chapter 24. SQL Server Clustering |
| | | | From Windows NT Enterprise Edition to Windows 2000 Advanced Server |
| | | | Cluster Services |
| | | | SQL Clustering and Fail-Over Support |
| | | | Network Load Balancing |
| | | | Summary |
| |
| | | | Chapter 25. Defining System Administration and Naming Standards |
| | | | The SQL Server Environment Approach |
| | | | SQL Server Naming Standards |
| | | | Summary |
| |
| |
| | | Part IV. Transact-SQL |
| | | | Chapter 26. Using Transact-SQL in SQL Server 2000 |
| | | | T-SQL and ANSI/ISO SQL |
| | | | What's New for T-SQL in SQL Server 2000 |
| | | | SELECT , INSERT , UPDATE , and DELETE |
| | | | SQL Server Functions |
| | | | Programming Constructs |
| | | | Cursors |
| | | | Summary |
| |
| | | | Chapter 27. Creating and Managing Views in SQL Server |
| | | | Definition of Views |
| | | | Using Views to Simplify Retrieval of Data |
| | | | Using Views to Hide Underlying Table Structures |
| | | | Using Views as a Security Mechanism |
| | | | Data Modifications and Views |
| | | | Creating Views |
| | | | Altering and Dropping Views |
| | | | Partitioned Views |
| | | | Indexed Views |
| | | | Views and Query Performance |
| | | | Summary |
| |
| | | | Chapter 28. Creating and Managing Stored Procedures in SQL Server |
| | | | Advantages of Stored Procedures |
| | | | Creating and Executing Stored Procedures |
| | | | Deferred Name Resolution |
| | | | Viewing and Modifying Stored Procedures |
| | | | Using Input Parameters |
| | | | Using Output Parameters |
| | | | Returning Procedure Status |
| | | | Cursors in Stored Procedures |
| | | | Nested Stored Procedures |
| | | | Using Temporary Tables in Stored Procedures |
| | | | Using the table Datatype |
| | | | Remote Stored Procedures |
| | | | Debugging Stored Procedures with Query Analyzer |
| | | | Debugging with Microsoft Visual Studio and Visual Basic |
| | | | System Stored Procedures |
| | | | Stored-Procedure Performance |
| | | | Using Dynamic SQL in Stored Procedures |
| | | | Autostart Procedures |
| | | | Extended Stored Procedures |
| | | | Stored Procedure Coding Guidelines and Limitations |
| | | | Summary |
| |
| | | | Chapter 29. Creating and Managing Triggers |
| | | | Benefits and Uses of Triggers |
| | | | Creating Triggers |
| | | | AFTER Triggers |
| | | | inserted and deleted Tables |
| | | | Checking for Column Updates |
| | | | Nested Triggers |
| | | | Recursive Triggers |
| | | | Enforcing Referential Integrity with Triggers |
| | | | INSTEAD OF Triggers |
| | | | Summary |
| |
| | | | Chapter 30. User-Defined Functions |
| | | | Why Use User-Defined Functions? |
| | | | Types of Functions |
| | | | Creating and Managing Functions |
| | | | System-Wide Table Valued Functions |
| | | | Rewriting Stored Procedures as Functions |
| | | | Summary |
| |
| | | | Chapter 31. Transaction Management and the Transaction Log |
| | | | What Is a Transaction? |
| | | | How SQL Server Manages Transactions |
| | | | Defining Transactions |
| | | | Transaction Logging and the Recovery Process |
| | | | Transactions and Batches |
| | | | Transactions and Stored Procedures |
| | | | Transactions and Triggers |
| | | | Transactions and Locking |
| | | | Coding Effective Transactions |
| | | | Long-Running Transactions |
| | | | Bound Connections |
| | | | Distributed Transactions |
| | | | Summary |
| |
| | | | Chapter 32. Distributed Transaction Processing |
| | | | Distributed Transaction Processing |
| | | | Distributed Queries |
| | | | Linked Servers |
| | | | Distributed Transactions |
| | | | MS DTC Architecture |
| | | | Summary |
| |
| |
| | | Part V. SQL Server Internals and Performance Tuning |
| | | | Chapter 33. SQL Server Internals |
| | | | SQL Server Memory Management |
| | | | SQL Server Process Management |
| | | | SQL Server Disk I/O |
| | | | SQL Server Storage Structures |
| | | | Database Files and Filegroups |
| | | | Database Pages |
| | | | Tables |
| | | | Indexes |
| | | | Data Modification and Performance |
| | | | Summary |
| |
| | | | Chapter 34. Indexes and Performance |
| | | | Index Usage Criteria |
| | | | Index Selection |
| | | | Evaluating Index Usefulness |
| | | | Index Statistics |
| | | | Index Design Guidelines |
| | | | Indexed Views |
| | | | Indexes on Computed Columns |
| | | | The Index Tuning Wizard |
| | | | Choosing Indexes: Query Versus Update Performance |
| | | | Summary |
| |
| | | | Chapter 35. Understanding Query Optimization |
| | | | What Is a Query Optimizer? |
| | | | Query Compilation and Optimization |
| | | | Step 1: Query Analysis |
| | | | Step 2: Index Selection |
| | | | Step 3: Join Selection |
| | | | Step 4: Execution Plan Selection |
| | | | Reusing Query Plans |
| | | | Other Query Processing Strategies |
| | | | Parallel Query Processing |
| | | | Data Warehousing and Large Database Query Strategies |
| | | | Common Query Optimization Problems |
| | | | Managing the Optimizer |
| | | | Summary |
| |
| | | | Chapter 36. Query Analysis |
| | | | Displaying Execution Plans in Query Analyzer |
| | | | Query Analyzer Server Trace |
| | | | Query Analyzer Client Statistics |
| | | | SHOWPLAN_ALL and SHOWPLAN_TEXT |
| | | | Statistics |
| | | | Query Analysis with SQL Profiler |
| | | | Summary |
| |
| | | | Chapter 37. Monitoring SQL Server Performance |
| | | | Performance-Monitoring Approach |
| | | | Performance Monitor |
| | | | Windows Performance Counters |
| | | | SQL Server Performance Counters |
| | | | SNMP Support |
| | | | Using DBCC to Examine Performance |
| | | | Other SQL Server Performance Considerations |
| | | | Summary |
| |
| | | | Chapter 38. Locking and Performance |
| | | | The Need for Locking |
| | | | Transaction Isolation Levels in SQL Server |
| | | | The Lock Manager |
| | | | Monitoring Lock Activity in SQL Server |
| | | | SQL Server Lock Types |
| | | | SQL Server Lock Granularity |
| | | | Lock Compatibility |
| | | | Locking Contention and Deadlocks |
| | | | Table Hints for Locking |
| | | | Optimistic Locking |
| | | | Summary |
| |
| | | | Chapter 39. Database Design and Performance |
| | | | Basic Tenets of Designing for Performance |
| | | | Logical Database Design Issues |
| | | | Denormalizing the Database |
| | | | Database Filegroups and Performance |
| | | | RAID Technology |
| | | | Summary |
| |
| | | | Chapter 40. Configuring, Tuning, and Optimizing SQL Server Options |
| | | | SQL Server Instance Architecture |
| | | | Configuration Options |
| | | | Fixing an Incorrect Option Setting |
| | | | Setting Configuration Options with SQL Enterprise Manager |
| | | | Obsolete Configuration Options |
| | | | Configuration Options and Performance |
| | | | Summary |
| |
| |
| | | Part VI. Additional SQL Server Features |
| | | | Chapter 41. Using XML in SQL Server 2000 |
| | | | Creating a Virtual Directory for Use with SQL Server 2000 |
| | | | Exploring Extensible Markup Language (XML) |
| | | | Retrieving Data Using the FOR XML Clause |
| | | | Using FOR XML RAW |
| | | | Using FOR XML AUTO |
| | | | Using FOR XML AUTO, ELEMENTS |
| | | | Using FOR XML EXPLICIT |
| | | | Retrieving XML-Data Schemas |
| | | | Retrieving Binary Data in XML |
| | | | The Basics of XML Path Language (XPath) |
| | | | Using XML in Stored Procedures |
| | | | Using OPENXML to Read XML |
| | | | Using URL Queries |
| | | | Using XML Templates |
| | | | Using XML Updategrams |
| | | | Summary |
| |
| | | | Chapter 42. Microsoft SQL Server Analysis Services |
| | | | What Is Analysis Services and OLAP? |
| | | | Understanding the Analysis Services Environment and the "Land of Wizards" |
| | | | An Analytics Design Methodology |
| | | | An Analytics Mini-Methodology |
| | | | An OLAP Requirements Example |
| | | | OLAP Cube Creation |
| | | | Creating an OLAP Database |
| | | | Working with a Relational Database |
| | | | Files at the Operating System Level |
| | | | Summary |
| |
| | | | Chapter 43. Microsoft Transaction Server |
| | | | MTS Overview |
| | | | Auilding an MTS Component |
| | | | Installing an MTS Component |
| | | | Configuring Security |
| | | | Running an MTS Application |
| | | | Using Database Connection Pooling |
| | | | Summary |
| |
| | | | Chapter 44. SQL Server Full-Text Search Services |
| | | | How Search Server Works |
| | | | Setting Up a Full-Text Index |
| | | | Maintaining Full-Text Indexes |
| | | | Full-Text Searches |
| | | | Using Document Filters |
| | | | Summary |
| |
| | | | Chapter 45. SQL Server Notification Services |
| | | | Introduction to SQL Notification Services |
| | | | Obtaining and Installing SQL Notification Services |
| | | | SQL Notification Services Architecture |
| | | | Instances and Applications |
| | | | Deploying and Configuring a Notification Services Instance |
| | | | Monitoring and Administering a Notification Services Instance |
| | | | Summary |
| |
| | | | Chapter 46. SQL Server and the Microsoft .NET Framework |
| | | | The Microsoft .NET Framework Class Library |
| | | | ADO.NET |
| | | | .NET SQL Providers |
| | | | .NET Concurrency Model (Default) |
| | | | XML Externalization in .NET |
| | | | A .NET Application Within Visual Studio .NET |
| | | | Summary |
| |
| | | | Chapter 47. English Query |
| | | | What Is English Query? |
| | | | Getting Started with English Query |
| | | | How Does English Query Work? |
| | | | Building a Simple English Query Application |
| | | | Contextual Information |
| | | | Summary |
| |
| |
| | | Index |
