Cover image for Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
Delaney, Kalen.
Personal Author:
Publication Information:
Redmond, Wash. : Microsoft Press, [2001]

Physical Description:
xxxv, 1046 pages : illustrations ; 25 cm + 1 computer optical disc (4 3/4 in.).
General Note:
"Based on the first edition by Ron Soukup."
Title Subject:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D3 S66 2001 Book and Software Set Central Closed Stacks

On Order



Master the inner workings of today's premier relational database management system with this official guide to the SQL Server 2000 engine. Written by a renowned columnist in partnership with the product development team, this is the must-read book for anyone who needs to understand how SQL Server internal structures affect application development and functionality. Its extensive details about the latest product enhancements, updated installation and administration, and its development advice can help you create high-performance data-warehousing, transaction-processing, and decision-support applications that will scale up for any challenge.

Topics covered include:

Evolution, architecture, and toolset: The history of SQL Server since 1989, an architectural overview, and a comprehensive tour of its tools and features Planning and deployment: Everything you need to know about installation and deployment issues such as multiple instances, Super Sockets, and upgrades Databases, files, tables, and data: How to create, modify, back up, and restore databases, files, and tables; and how to query and modify data Built-in development tools: Using Query Analyzer and SQL Server Profiler to simplify system administration and optimize performance; programming with Transact-SQL; and extending functionality with batches, transactions, stored procedures, and triggers Query-processing techniques: Multiple indexes, hash and merge joins, and data-manipulation capabilities such as parallel queries Internal architecture: Low-level details of data and index-page structures, locking mechanisms, optimization, and plan caching Integration with other tools: Usage with Microsoft Office 2000, Visual Studio development system, and the BackOffice server familyINCLUDED ON TWO CD-ROMS! A 120-day evaluation copy of Microsoft SQL Server 2000 Enterprise Edition A searchable electronic copy of the book Sample scripts White papers and articles Tools and utilities

For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.

Author Notes

Over 60 SQL Server MVPs contributed to this book, which was assembled and edited by Kalen Delaney and SQL Server legends Louis Davidson (Database Design and Architecture), Greg Low (Business Intelligence.), Brad McGhee (Performance Tuning and Optimization), Paul Nielson (Database Development), and Paul Randal and Kimberly Tripp (Database Administration.)

Table of Contents

Forewordp. xxi
Prefacesp. xxiii
System Requirementsp. xxxv
Part I Overview
Chapter 1 The Evolution of Microsoft SQL Server: 1989 to 2000p. 3
SQL Server: The Early Yearsp. 4
Ron's Storyp. 6
Kalen's Storyp. 7
Microsoft SQL Server Shipsp. 8
Development Roles Evolvep. 10
OS/2 and Friendly Firep. 11
SQL Server 4.2p. 13
SQL Server for Windows NTp. 15
Success Brings Fundamental Changep. 20
The End of Joint Developmentp. 21
The Charge to SQL95p. 23
The Next Versionp. 25
The Secret of the Sphinxp. 26
Software for the New Centuryp. 28
Chapter 2 A Tour of SQL Serverp. 31
The SQL Server Enginep. 33
DBMS-Enforced Data Integrityp. 37
Transaction Processingp. 42
Symmetric Server Architecturep. 44
Securityp. 46
High Availabilityp. 47
Distributed Data Processingp. 48
Data Replicationp. 50
Systems Managementp. 52
SQL Server Utilities and Extensionsp. 57
Client Development Interfacesp. 63
Summaryp. 66
Part II Architectural Overview
Chapter 3 SQL Server Architecturep. 69
The SQL Server Enginep. 69
Managing Memoryp. 91
Transaction Logging and Recoveryp. 101
The SQL Server Kernel and Interaction with the Operating Systemp. 106
Summaryp. 111
Part III Using Microsoft SQL Server
Chapter 4 Planning for and Installing SQL Serverp. 115
SQL Server Editionsp. 116
Hardware Guidelinesp. 119
Hardware Componentsp. 121
The Operating Systemp. 141
The File Systemp. 142
Security and the User Contextp. 143
Licensingp. 145
Network Protocolsp. 150
Collationp. 150
Multiple Instancesp. 157
Installing SQL Serverp. 160
Basic Configuration After Installationp. 162
Remote and Unattended Installationp. 166
Summaryp. 170
Chapter 5 Databases and Database Filesp. 171
Special System Databasesp. 172
Database Filesp. 175
Creating a Databasep. 176
Expanding and Shrinking a Databasep. 179
Changes in Log Sizep. 182
Using Database Filegroupsp. 186
Altering a Databasep. 190
Databases Under the Hoodp. 192
Setting Database Optionsp. 197
Other Database Considerationsp. 205
Backing Up and Restoring a Databasep. 209
Summaryp. 219
Chapter 6 Tablesp. 221
Creating Tablesp. 222
User-Defined Datatypesp. 239
Identity Propertyp. 241
Internal Storagep. 245
Constraintsp. 270
Altering A Tablep. 306
Temporary Tablesp. 309
System Tablesp. 312
Summaryp. 315
Chapter 7 Querying Datap. 317
The SELECT Statementp. 317
Joinsp. 320
Dealing with NULLp. 336
Subqueriesp. 345
Views and Derived Tablesp. 357
Other Search Expressionsp. 365
Summaryp. 401
Chapter 8 Indexesp. 403
Index Organizationp. 404
Creating an Indexp. 408
The Structure of Index Pagesp. 411
Index Space Requirementsp. 427
Managing an Indexp. 431
Special Indexesp. 437
Using an Indexp. 446
Summaryp. 448
Chapter 9 Modifying Datap. 449
Basic Modification Operationsp. 449
Data Modification Internalsp. 476
Summaryp. 498
Chapter 10 Programming with Transact-SQLp. 499
Transact-SQL As A Programming Languagep. 500
Transact-SQL Programming Constructsp. 503
Transact-SQL Examples and Brainteasersp. 563
Full-Text Searchingp. 579
Summaryp. 598
Chapter 11 Batches, Stored Procedures, and Functionsp. 599
Batchesp. 600
Routinesp. 604
Stored Proceduresp. 605
User-Defined Functionsp. 617
Rewriting Stored Procedures as Functionsp. 627
Rolling Your Own System Routinesp. 628
Executing Batches, or What's Stored About Stored Procedures (and Functions)?p. 632
Temporary Stored Proceduresp. 640
Autostart Stored Proceduresp. 642
System Stored Proceduresp. 643
Execute ("Any String")p. 653
Summaryp. 655
Chapter 12 Transactions and Triggersp. 657
Transactionsp. 657
Triggersp. 682
Summaryp. 701
Chapter 13 Special Transact-SQL Operations: Working with Cursors and Large Objectsp. 703
Cursor Basicsp. 704
Cursors and ISAMsp. 707
Cursor Modelsp. 711
Appropriate Use of Cursorsp. 715
Working with Transact-SQL Cursorsp. 725
Cursor Variablesp. 743
Working with Text and Image Datap. 750
Summaryp. 769
Part IV Performance and Tuning
Chapter 14 Lockingp. 773
The Lock Managerp. 773
Lock Types for User Datap. 779
Lock Compatibilityp. 798
Internal Locking Architecturep. 800
Bound Connectionsp. 807
Row-Level vs. Page-Level Lockingp. 811
Locking Hints and Trace Flagsp. 813
Summaryp. 813
Chapter 15 The Query Processorp. 815
The SQL Managerp. 816
Compilation and Optimizationp. 816
The Procedure Cachep. 852
Using Stored Procedures and Caching Mechanismsp. 855
Executionp. 865
Summaryp. 865
Chapter 16 Query Tuningp. 867
The Development Teamp. 868
Application and Database Designp. 868
Planning for Peak Usagep. 874
Perceived Response Time for Interactive Systemsp. 874
Prototyping, Benchmarking, and Testingp. 876
Creating Useful Indexesp. 880
Monitoring Query Performancep. 889
Concurrency and Consistency Tradeoffsp. 914
Resolving Blocking Problemsp. 915
Resolving Deadlock Problemsp. 919
Segregating OLTP and DSS Applicationsp. 934
Environmental Concernsp. 935
Summaryp. 942
Chapter 17 Configuration and Performance Monitoringp. 943
Operating System Configuration Settingsp. 944
SQL Server Configuration Settingsp. 946
System Maintenancep. 960
Monitoring System Behaviorp. 961
Summaryp. 985
Bibliography and Suggested Readingp. 987
Indexp. 993