|
Inside SQL Server 2000
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 family INCLUDED 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
FEATURES:
* Includes up-to-the-minute information about SQL Server 2000 with extensive coverage of critical new features.
* Author Kalen Delaney, a leading SQL Server trainer and columnist, imparts her in-depth knowledge of this milestone software product.
* Accompanying product information on CD serves as a key reference for solution builders and technology evaluators.
CONTENTS:
Table of Contents
Foreword xxi
Prefaces xxiii
System Requirements xxxv
Part I Overview
Chapter 1 The Evolution of Microsoft SQL Server: 1989 to 2000 3
SQL Server: The Early Years 4
Ron's Story 6
Kalen's Story 7
Microsoft SQL Server Ships 8
Development Roles Evolve 10
OS/2 and Friendly Fire 11
SQL Server 4.2 13
OS/2 2.0 Release on Hold 14
Version 4.2 Released 14
SQL Server for Windows NT 15
Success Brings Fundamental Change 20
The End of Joint Development 21
The Charge to SQL95 23
The Next Version 25
The Secret of the Sphinx 26
Software for the New Century 28
Chapter 2 A Tour of SQL Server 31
The SQL Server Engine 33
Transact-SQL 33
DBMS-Enforced Data Integrity 37
Declarative Data Integrity 38
Datatypes 39
CHECK Constraints and Rules 40
Defaults 40
Triggers 40
Transaction Processing 42
Atomicity 42
Consistency 43
Isolation 43
Durability 43
Symmetric Server Architecture 44
Traditional Process/Thread Model 44
SQL Server Process/Thread Model 45
Multiuser Performance 45
Security 46
Monitoring and Managing Security 47
High Availability 47
Distributed Data Processing 48
Data Replication 50
Systems Management 52
SQL Server Enterprise Manager 52
Distributed Management Objects 54
Windows Management Instrumentation 54
SQL-DMO and Visual Basic Scripting 55
SQL Server Agent 55
SQL Server Utilities and Extensions 57
Web Assistant Wizard and Internet Enabling 57
SQL Profiler 58
SQL Server Service Manager 59
System Monitor Integration 60
Client Network Utility 60
Server Network Utility 61
SQL Server Installation 61
OSQL and ISQL 61
SQL Query Analyzer 61
Bulk Copy and Data Transformation Services 62
SNMP Integration 63
SQL Server Books Online 63
Client Development Interfaces 63
ODBC 64
OLE DB 64
ADO 64
DB-Library 65
ESQL/C 65
Server Development Interface 65
Summary 66
Part II Architectural Overview
Chapter 3 SQL Server Architecture 69
The SQL Server Engine 69
The Net-Library 70
Open Data Services 74
The Relational Engine and the Storage Engine 77
The Access Methods Manager 81
The Row Operations Manager and the Index Manager 82
The Page Manager and the Text Manager 86
The Transaction Manager 87
The Lock Manager 90
Other Managers 91
Managing Memory 91
The Buffer Manager and Memory Pools 92
Access to In-Memory Pages 92
Access to Free Pages (Lazywriter) 93
Checkpoints 95
Accessing Pages Using the Buffer Manager 97
Large Memory Issues 97
The Log Manager 101
Transaction Logging and Recovery 101
Locking and Recovery 104
Page LSNs and Recovery 104
The SQL Server Kernel and
Interaction with the Operating System 106
Threading and Symmetric Multiprocessing 107
The Worker Thread Pool 109
Disk I/O in Windows NT/2000 111
Summary 111
Part III Using Microsoft SQL Server
Chapter 4 Planning for and Installing SQL Server 115
SQL Server Editions 116
Embedded SQL Server 119
Hardware Guidelines 119
Use Hardware on the
Windows Hardware Compatibility List 119
Performance = Fn
(Processor Cycles, Memory, I/O Throughput) 120
Invest in Benchmarking 121
Hardware Components 121
The Processor 122
Memory 124
Disk Drives, Controllers, and Disk Arrays 125
RAID Solutions 120
More About Drives and Controllers 137
Uninterruptible Power Supply 138
The Disk Subsystem 139
Fallback Server Capability 140
Other Hardware Considerations 141
The Operating System 141
The File System 142
Security and the User Context 143
Licensing 145
SQL Server Processor License 145
Server Licenses and CALs 145
Multiplexing: Use of Middleware,
Transaction Servers, and Multitiered Architectures 146
Multiple Instances 147
Network Protocols 150
Collation 150
Character Sets 151
Sort Orders 152
Multiple Instances 157
Installing Named Instances 158
Named Instance Server Connectivity 159
Installing SQL Server 160
Upgrading from a Previous Version 161
Basic Configuration After Installation 162
Starting the SQL Server Service 162
Changing the System Administrator Password 163
Configuring SQL Server's Error Log 164
Working with Multiple Instances 164
Remote and Unattended Installation 166
Remote Installation 167
Unattended Installation 167
Changing Installation Options 168
Adding Additional Components 169
Summary 170
Chapter 5 Databases and Database Files 171
Special System Databases 172
master 172
model 173
tempdb 173
pubs 174
Northwind 174
msdb 175
Database Files 175
Creating a Database 176
A CREATE DATABASE Example 178
Expanding and Shrinking a Database 179
Automatic File Expansion 179
Manual File Expansion 179
Automatic File Shrinkage 179
Manual File Shrinkage 180
Changes in Log Size 182
Log Truncation 185
Using Database Filegroups 186
The Default Filegroup 187
A FILEGROUP CREATION Example 189
Altering a Database 190
ALTER DATABASE Examples 191
Databases Under the Hood 192
Space Allocation 194
Setting Database Options 197
State Options 198
Cursor Options 202
Auto Options 202
SQL Options 203
Recovery Options 205
Other Database Considerations 205
Databases vs. Schemas 205
Using Removable Media 206
Detaching and Reattaching a Database 207
Compatibility Levels 208
Backing Up and Restoring a Database 209
Types of Backups 210
Recovery Models 211
Choosing a Backup Type 214
Restoring a Database 215
Summary 219
Chapter 6 Tables 221
Creating Tables 222
Naming Tables and Columns 223
Reserved Keywords 224
Delimited Identifiers 224
Naming Conventions 225
Datatypes 226
Much Ado About NULL 235
User-Defined Datatypes 239
Identity Property 241
Internal Storage 245
Data Pages 246
Examining Data Pages 248
The Structure of Data Rows 252
Column Offset Arrays 254
Storage of Fixed-Length and Variable-Length Rows 255
Page Linkage 259
Text and Image Data 260
sql_variant Datatype 266
Constraints 270
PRIMARY KEY and UNIQUE Constraints 271
FOREIGN KEY Constraints 277
Constraint-Checking Solutions 286
Restrictions on Dropping Tables 287
Self-Referencing Tables 287
CHECK Constraints 289
Default Constraints 294
More About Constraints 298
Altering a Table 306
Changing a Datatype 306
Adding a New Column 308
Adding, Dropping, Disabling, or Enabling a Constraint 308
Dropping a Column 309
Enabling or Disabling a Trigger 309
Temporary Tables 309
Private Temporary Tables (#) 310
Global Temporary Tables (##) 310
Direct Use of tempdb 311
Constraints on Temporary Tables 311
System Tables 312
Summary 315
Chapter 7 Querying Data 317
The SELECT Statement 317
Joins 320
Outer Joins 324
The Obsolete *= OUTER JOIN Operator 329
Cross Joins 335
Dealing with NULL 336
NULL in the Real World 340
IS NULL and = NULL 343
Subqueries 345
Correlated Subqueries 350
Views and Derived Tables 357
Altering Views 361
Partitioned Views 362
Other Search Expressions 365
LIKE 365
BETWEEN 371
Aggregate Functions 371
Datacube - Aggregate Variations 378
TOP 392
UNION 395
Summary 401
Chapter 8 Indexes 403
Index Organization 404
Clustered Indexes 406
Nonclustered Indexes 406
Creating an Index 408
Constraints and Indexes 410
The Structure of Index Pages 411
Clustered Index Rows with a Uniqueifier 412
Index Row Formats 415
Index Space Requirements 427
B-Tree Size 427
Actual vs. Estimated Size 428
Managing an Index 431
Types of Fragmentation 431
Detecting Fragmentation 432
Removing Fragmentation 435
Special Indexes 437
Prerequisites 437
Indexes on Computed Columns 441
Indexed Views 443
Using an Index 446
Looking for Rows 446
Joining 446
Sorting 446
Grouping 448
Maintaining Uniqueness 448
Summary 448
Chapter 9 Modifying Data 449
Basic Modification Operations 449
INSERT 450
UPDATE 463
DELETE 465
Modifying Data Through Views 467
Data Modification Internals 476
Inserting Rows 477
Splitting Pages 477
Deleting Rows 481
Updating Rows 489
Table-Level vs. Index-Level Data Modification 495
Logging 497
Locking 497
Summary 498
Chapter 10 Programming with Transact-SQL 499
Transact-SQL as a Programming Language 500
Programming at Multiple Levels 501
Transact-SQL Programming Constructs 503
Variables 503
Control-of-Flow Tools 510
CASE 511
PRINT 514
RAISERROR 515
FORMATMESSAGE 518
Operators 519
Scalar Functions 527
Table-Valued Functions 562
Transact-SQL
Examples and Brainteasers 563
Generating Test Data 563
Getting Rankings 567
Finding Differences Between Intervals 573
Selecting Instead of Iterating 578
Full-Text Searching 579
Full-Text Indexes 581
Setting Up Full-Text Indexes 582
Maintaining Full-Text Indexes 585
Querying Full-Text Indexes 589
Performance Considerations for Full-Text Indexes 597
Summary 598
Chapter 11 Batches, Stored Procedures, and Functions 599
Batches 600
Routines 604
Stored Procedures 605
Nested Stored Procedures 607
Recursion in Stored Procedures 608
Stored Procedure Parameters 613
User-Defined Functions 617
Table Variables 617
Scalar-Valued Functions 618
Table-Valued Functions 621
System Table-Valued Functions 624
Managing User-Defined Functions 624
Rewriting Stored Procedures as Functions 627
Rolling Your Own System Routines 628
Your Own System Procedures 628
Your Own System Functions 630
Executing Batches, or What's Stored
About Stored Procedures (and Functions)? 632
Step One: Parse Commands and Create the Sequence Tree 632
Step Two: Compile the Batch 632
Step Three: Execute 632
Step Four: Recompile Execution Plans 634
Storage of Routines 636
Encrypting Routines 637
Altering a Routine 640
Temporary Stored Procedures 640
Private Temporary Stored Procedures 640
Global Temporary Stored Procedures 641
Procedures Created from Direct Use of tempdb 641
Autostart Stored Procedures 642
System Stored Procedures 643
General System Procedures 644
Catalog Stored Procedures 645
SQL Server Agent Stored Procedures 646
Replication Stored Procedures 646
Extended Stored Procedures 647
Execute("any string") 653
Summary 655
Chapter 12 Transactions and Triggers 657
Transactions 657
Explicit and Implicit Transactions 659
Error Checking in Transactions 660
Transaction Isolation Levels 666
Other Characteristics of Transactions 676
Nested Transaction Blocks 677
Savepoints 681
Triggers 682
After Triggers 682
Instead-of Triggers 688
Managing Triggers 692
Using Triggers to Implement Referential Actions 692
Recursive Triggers 700
Summary 701
Chapter 13 Special Transact-SQL Operations:
Working with Cursors and Large Objects 703
Cursor Basics 704
Cursors and ISAMs 707
Problems with ISAM-Style Applications 710
Cursor Models 711
Transact-SQL Cursors 711
API Server Cursors 712
Client Cursors 713
Default Result Sets 714
API Server Cursors vs. Transact-SQL Cursors 714
Appropriate Use of Cursors 715
Row-by-Row Operations 716
Query Operations 716
Scrolling Applications 717
Choosing a Cursor 718
Cursor Membership, Scrolling, and Sensitivity to Change 720
Working with Transact-SQL Cursors 725
DECLARE 726
OPEN 728
FETCH 729
UPDATE 730
DELETE 731
CLOSE 731
DEALLOCATE 732
The Simplest Cursor Syntax 732
Fully Scrollable Transact-SQL Cursors 732
Concurrency Control with Transact-SQL Cursors 735
Cursor Variables 743
Obtaining Cursor Information 744
Working with Text and Image Data 750
WRITETEXT 752
READTEXT 755
UPDATETEXT 761
Summary 769
Part IV Performance and Tuning
Chapter 14 Locking 773
The Lock Manager 773
The Lock Manager and Isolation Levels 774
Spinlocks 775
Deadlocks 775
Lock Types for User Data 779
Lock Modes 780
Lock Granularity 780
Lock Duration 790
Lock Ownership 790
Viewing Locks 791
Lock Compatibility 798
Internal Locking Architecture 800
Lock Blocks 802
Lock Owner Blocks 804
Syslockinfo Table 804
Bound Connections 807
Row-Level vs. Page-Level Locking 811
Lock Escalation 812
Locking Hints and Trace Flags 813
Summary 813
Chapter 15 The Query Processor 815
The SQL Manager 816
Compilation and Optimization 816
Compilation 817
Optimization 818
How the Query Optimizer Works 820
Join Selection 839
Other Processing Strategies 845
Maintaining Statistics 846
The Procedure Cache 852
Using Stored Procedures and Caching Mechanisms 855
Ad Hoc Caching 856
Autoparameterization 856
The sp_executesql Procedure 857
The Prepare and Execute Method 857
Sharing Cached Plans 857
Examining the Plan Cache 858
Multiple Plans in Cache 860
When to Use Stored Procedures
and Other Caching Mechanisms 862
Recompiling Stored Procedures 862
Other Benefits of Stored Procedures 863
Execution 865
Summary 865
Chapter 16 Query Tuning 867
The Development Team 868
Application and Database Design 868
Normalize Your Database 869
Evaluate Your Critical Transactions 871
Keep Table Row Lengths and Keys Compact 873
Planning for Peak Usage 874
Perceived Response Time for Interactive Systems 874
Prototyping, Benchmarking, and Testing 876
Development Methodologies 878
Creating Useful Indexes 880
Choose the Clustered Index Carefully 881
Make Nonclustered Indexes Highly Selective 882
Tailor Indexes to Critical Transactions 883
Pay Attention to Column Order 885
Index Columns Used in Joins 885
Create or Drop Indexes as Needed 887
The Index Tuning Wizard 887
Monitoring Query Performance 889
STATISTICS IO 889
STATISTICS TIME 893
Showplan 893
Using Query Hints 908
Stored Procedure Optimization 912
Concurrency and Consistency Tradeoffs 914
Resolving Blocking Problems 915
Indexes and Blocking 917
Resolving Deadlock Problems 919
Cycle Deadlock Example 919
Conversion Deadlock Example 919
Preventing Deadlocks 922
Handling Deadlocks 922
Volunteering to Be the Deadlock Victim 923
Watching Locking Activity 923
Identifying the Culprit 924
Lock Hints 931
Segregating OLTP and DSS Applications 934
Environmental Concerns 935
Case Sensitivity 935
Nullability and ANSI Compliance Settings 936
Locale-Specific SET Options 942
Summary 942
Chapter 17 Configuration and Performance Monitoring 943
Operating System Configuration Settings 944
Task Management 944
Resource Allocation 944
PAGEFILE.SYS Location 945
File System Selection 945
Nonessential Services 946
Network Protocols 946
SQL Server Configuration Settings 946
Serverwide Options 947
Buffer Manager Options 958
Startup Parameters on SQLSERVR.EXE 960
System Maintenance 960
Monitoring System Behavior 961
SQL Profiler 961
System Monitor 976
Other Performance Monitoring Considerations 985
Summary 985
Bibliography and Suggested Reading 987
Index 993
|
