Foreword
Prefaces
System Requirements
Part 1 Overview
Chapter l The Evolution of Microsoft SQL Server: 1989 to 2000
SQL SERVER: THE EARLY YEARS
RON'S STORY
KALEN'S STORY
MICROSOFT SQL SERVER SHIPS
DEVELOPMENT ROLES EVOLVE
OS/2 AND FRIENDLY FIRE
SQL SERVER 4.2
OS/2 2.0 Release on Hold
Version 4.2 Released
SQL SERVER FOR WINDOWS NT
SUCCESS BRINGS FUNDAMENTAL CHANGE
THE END OF JOINT DEVELOPMENT
THE CHARGE TO SQL95
THE NEXT VERSION
THE SECRET OF THE SPHINX
SOFTWARE FOR THE NEW CENTURY
Chapter 2 A Tour of SQL Server
THE SQL SERVER ENGINE
Transact-SQL
DBMS-ENFORCED DATA INTEGRITY
Declarative Data lntegrity
Datatypes
CHECK Constraints and Rules
Defaults
Triggers
TRANSACTION PROCESSING
Atomicity
Consistency
Isolation
Durability
SYMMETRIC SERVER ARCHITECTURE
Traditional Process/Thread Model
SQL Server Process/Thread Model
Multiuser Performance
SECURITY
Monitoring and Managing Security
HIGH AVAILABILITY
DISTRIBUTED DATA PROCESSING
DATA REPLICATION
SYSTEMS MANAGEMENT
SQL Server Enterprise Manager
Distributed Management Objects
Windows Management Instrumentation
SQL-DMO and Visual Basic Scripting
SQL Server Agent
SQL SERVER UTILITIES AND EXTENSIONS
Web Assistant Wizard and Internet Enabling
SQL Profiler
SQL Server Service Manager
System Monitor Integration
Client Network Utility
Server Network Utility
SQL Server Installation
OSQL and ISQL
SQL Query Analyzer
Bulk Copy and Data Transformation Services
SNMP Integration
SQL Server Books Online
CLIENT DEVELOPMENT INTERFACES
ODBC
OLE DB
ADO
DB-Library
ESQL/C
Server Development Interface
SUMMARY
Part II Architectural Overview
Chapter 3 SQL Server Architecture
THE SQL SERVER ENGINE
The Net-Library
Open Data Services
The Relational Engine and the Storage Engine
The Access Methods Manager
The Row Operations Manager and the Index Manager
The Page Manager and the Text Manager
The Transaction Manager
The Lock Manager
Other Managers
MANAGING MEMORY
The Buffer Manager and Memory Pools
Access to In-Memory Pages
Access to Free Pages (Lazywriter)
Checkpoints
Accessing Pages Using the Buffer Manager
Large Memory lssues
The Log Manager
TRANSACTION LOGGING AND RECOVERY
Locking and Recovery
Page LSNs and Recovery
THE SQL SERVER KERNEL AND INTERACTION WITH THE OPERATING SYSTEM
Threading and Symmetric Multiprocessing
The Worker Thread Pool
Disk I/O in Windows NT/2000
SUMMARY
Part III Using Microsoft SQL Server
Chapter 4 Planning for and Installing SQL Server
SQL SERVER EDITIONS
Embedded SQL Server
HARDWARE GUIDELINES
Use Hardware on the Windows Hardware Compatibility List
Performance = Fn(Processor Cycles, Memory, I/O Throughput)
lnvest in Benchmarking
HARDWARE COMPONENTS
The Processor
Memory
Disk Drives, Controllers, and Disk Arrays
RAID Solutions
More About Drives and Controllers
Uninterruptible Power Supply
The Disk Subsystem
Fallback Server Capability
Other Hardware Considerations
THE OPERATING SYSTEM
THE FILE SYSTEM
SECURITY AND THE USER CONTEXT
LICENSING
SQL Server Processor License
Server Licenses and CALs
Multiplexing: Use of Middleware,Transaction Servers, and Multitiered Architectures
Multiple Instances
NETWORK PROTOCOLS
COLLATION
Character Sets
Sort Orders
MULTIPLE INSTANCES
Installing Named Instances
Named Instance Server Connectivity
INSTALLING SQL SERVER
Upgrading from a Previous Version
BASIC CONFIGURATION AFTER INSTALLATION
Starting the SQL Server Service
Changing the System Administrator Password
Configuring SQL Server's Error Log
Working with Multiple Instances
REMOTE AND UNATTENDED INSTALLATION
Remote Installation
Unattended Installation
Changing Installation Options
Adding Additional Components
SUMMARY
Chapter 5 Databases and Database Files
SPEClAL SYSTEM DATABASES
master
model
tempdb
pubs
Northwind
msdb
DATABASE FILES
CREATING A DATABASE
A CREATE DATABASE Example
EXPANDING AND SHRINKING A DATABASE
Automatic File Expansion
Manual File Expansion
Automatic File Shrinkage
Manual File Shrinkage
CHANGES IN LOG SlZE
Log Truncation
USING DATABASE FILEGROUPS
The Default Filegroup
A FILEGROUP CREATION Example
ALTERING A DATABASE
ALTER DATABASE Examples
DATASASES UNDER THE HOOD
Space Allocation
SETTING DATABASE OPTIONS
State Options
Cursor Optians
Auto Options
SQL Options
Recovery Options
OTHER DATABASE CONSIDERATIONS
Databases vs. Schemas
Using Removable Media
Detaching and Reattaching a Database
Compatibility Levels
BACKING Up AND RESTORING A DATABASE
Types of Backups
Recovery Models
Choosing a Backup Type
Restoring a Database
SUMMARY
Chapter 6 Tables
CREATING TABLES
Naming Tables and Columns
Reserved Keywords
Delimited Identifiers
Naming Conventions
Datatypes
Much Ado About NULL
USER-DEFINED DATATYPES
IDENTITY PROPERTY
INTERNAL STORAGE
Data Pages
Examining Data Pages
The Structure of Data Rows
Column Offset Arrays
Storage of Fixed-Length and Variable-Length Rows
Page Linkage
Text and Image Data
sql_variant Datatype
CONSTRAINTS
PRIMARY KEY and UNIQUE Constraints
FOREIGN KEY Constraints
Constraint-Checking Solutions
Restrictions on Dropping Tables
Self-Referencing Tables
CHECK Constraints
Default Constraints
More About Constraints
ALTERING A TABLE
Changing a Datatype
Adding a New Column
Adding Dropping, Disabling, or Enabling a Constraint
Dropping a Column
Enabling or Disabling a Trigger
TEMPORARY TABLES
Private Temporary Tables (#)
Global Temporary Tables (##)
Direct Use of tempdb
Constraints on Temporary Tables
SYSTEM TABLES
SUMMARY
Chapter 7 Querying Data
THE SELECT STATEMENT
JOINS
Outer Joins
The Obsolete *= OUTER JOIN Operator
Cross Joins
DEALING WITH NULL
NULL in the Real World
IS NULL and = NULL
SUBQUERIES
Correlated Subqueries
VIEWS AND DERIVED TABLES
Altering Views
Partitioned Views
OTHER SEARCH EXPRESSIONS
LIKE
BETWEEN
Aggregate Functions
Datacube——Aggregate Variations
TOP
UNION
SUMMARY
Chapter 8 Indexes
INDEX ORGANIZATION
Clustered Indexes
Nonclustered Indexes
CREATING AN INDEX
Constraints and Indexes
THE STRUCTURE OF INDEX PAGES
Clustered Index Rows with a Uniqueifier
Index Row Formats
INDEX SPACE REQUIREMENTS
B-Tree Size
Actual vs. Estimated Size
MANAGING AN INDEX
Types of Fragmentation
Detecting Fragmentation
Removing Fragmentation
SPECIAL INDEXES
Prerequisites
Indexes on Computed Columns
Indexed Views
USING AN INDEX
Looking for Rows
Joining
Sorting
Grouping
Maintaining Uniqueness
SUMMARY
Chapter 9 Modifying Data
BASIC MODIFICATION OPERATIONS
INSERT
UPDATE
DELETE
Modifying Data Through Views
DATA MODIFICATION INTERNALS
Inserting Rows
Splitting Pages
Deleting Rows
Updating Rows
Table-Level vs. Index-Level Data Modification
Logging
Locking
SUMMARY
Chapter 10 Programming with Transact-SQL
TRANSACT-SQL AS A PROGRAMMING LANGUAGE
Programming at Multiple Levels
TRANSACT-SQL PROGRAMMING CONSTRUCTS
Variables
Control-of-Flow Tools
CASE
PRINT
RAISERROR
FORMATMESSAGE
Operators
Scalar Functions
Table-Valued Functions
TRANSACT-SQL EXAMPLES AND BRAINTEASERS
Generating Test Data
Getting Rankings
Finding Differences Between Intervals
Selecting Instead of Iterating
FULL-TEXT SEARCHING
Full-Text Indexes
Setting Up Full-Text Indexes
Maintaining Full-Text Indexes
Querying Full-Text Indexes
Performance Considerations for Full-Text Indexes
SUMMARY
Chapter 11 Batches, Stored Procedures, and Functions
BATCHES
ROUTINES
STORED PROCEDURES
Nested Stored Procedures
Recursion in Stored Procedures
Stored Procedure Parameters
USER-DEFINED FUNCTIONS
Table Variable s
Scalar-Valued Functions
Table-Valued Functions
System Table-Valued Functions
Managing User-Defined Functions
REWRITING STORED PROCEDURES AS FUNCTIONS
ROLLING YOUR OWN SYSTEM ROUTINES
Your Own System Procedures
Your Own System Functions
EXECUTING BATCHES, OR WHAT'S STORED ABOUT STORED PROCEDURES (AND FUNCTlONS)?
Step One: Parse Commands and Create the Sequence Tree
Step Two: Compile the Batch
Step Three: Execute
Step Four: Recompile Execution Plans
Storage of Routines
Encrypting Routines
Altering a Routine
TEMPORARY STORED PROCEDURES
Private Temporary Stored Procedures
Global Temporary Stored Procedures
Procedures Created from Direct Use of tempdb
AUTOSTART STORED PROCEDURES
SYSTEM STORED PROCEDURES
General System Procedures
Catalog Stored Procedures
SQL Server Agent Stored Procedures
Replication Stored Procedures
Extended Stored Procedures
EXECUTE("ANY STRING")
SUMMARY
Chapter 12 Transactions and Triggers
TRANSACTIONS
Explicit and Implicit Transactions
Error Checking in Transactions
Transaction Isolation Levels
Other Characteristics of Transactions
Nested Transaction Blocks
Savepoints
TRIGGERS
After Triggers
Instead-of Triggers
Managing Triggers
Using Triggers to Implement Referential Actions
Recursive Triggers
SUMMARY
Chapter 13 Special Transact-SQL Operations:Working with Cursors and Large Objects
CURSOR BASICS
CURSORS ANO ISAMS
Problems with ISAM-Style Applications
CURSOR MODELS
Transact-SQL Cursors
API Server Cursors
Client Cursors
Default Result Sets
API Server Cursors vs. Transact-SQL Cursors
APPROPRIATE USE OF CURSORS
Row-by-Row Operations
Query Operations
Scrolling Applications
Choosing a Cursor
Cursor Membership, Scrolling, and Sensitivity to Change
WORKING WITH TRANSACT-SQL CURSORS
DECLARE
OPEN
FETCH
UPDATE
DELETE
CLOSE
DEALLOCATE
The Simplest Cursor Syntax
Fully Scrollable Transact-SQL Cursors
Concurrency Control with Transact-SQL Cursors
CURSOR VARIABLES
Obtaining Cursor Information
WORKING WITH TEXT AND IMAGE DATA
WRITETEXT
READTEXT
UPDATETEXT
SUMMARY
PartIV Performance and Tuning
Chapter 14 Locking
THE LOCK MANAGER
The Lock Manager and Isolation Levels
Spinlocks
Deadlocks
LOCK TYPES FOR USER DATA
Lock Modes
Lock Granularity
Lock Duration
Lock Ownership
Viewing Locks
LOCK COMPATIBILITY
INTERNAL LOCKING ARCHITECTURE
Lock Blocks
Lock Owner Blocks
Syslockinfo Table
BOUND CONNECTIONS
ROW-LEVEL VS. PAGE-LEVEL LOCKING
Lock Escalation
LOCKING HINTS AND TRACE FLAGS
SUMMARY
Chapter 15 The Query Processor
THE SQL MANAGER
COMPILATION AND OPTIMIZATION
Compilation
Optimization
How the Query Optimizer Works
Join Selection
Other Processing Strategies
Maintaining Statistics
THE PROCEDURE CACHE
USING STORED PROCEDURES AND CACHING MECHANISMS
Ad Hoc Caching
Autoparameterization
The sp_executessq/Procedure
The Prepare and Execute Method
Sharing Cached Plans
Examining the Plan Cache
Multiple Plans in Cache
When to Use Stored Procedures and Other Caching Mechanisms
Recompiling Stored Procedures
Other Benefits of Stored Procedures
EXECUTION
SUMMARY
Chapter 16 Query Tuning
THE DEVELOPMENT TEAM
APPLICATION AND DATABASE DESIGN
Normalize Your Database
Evaluate Your Critical Transactions
Keep TabIe Row Lengths and Keys Compact
PLANNING FOR PEAK USAGE
PERCEIVED RESPONSE TIME FOR INTERACTIVE SYSTEMS
PROTOTYPING, BENCHMARKING, AND TESTING
Development Methodologies
CREATING USEFUL INDEXES
Choose the Clustered Index Carefully
Make Nonclustered Indexes Highly Selective
Tailor Indexes to Critical Transactions
Pay Attention to Column Order
Index Columns Used in Joins
Create or Drop Indexes as Needed
The Index Tuning Wizard
MONITORING QUERY PERFORMANCE
STATISTICS IO
STATISTICS TIME
Showplan
Using Query Hints
Stored Procedure Optimization
CONCURRENCY AND CONSISTENCY TRADEOFFS
RESOLVING BLOCKING PROBLEMS
Indexes and Blocking
RESOLVING DEADLOCK PROBLEMS
Cycle Deadlock Example
Conversion Deadlock Example
Preventing Deadlocks
Handling Deadlocks
Volunteering to Be the Deadlock Victim
Watching Locking Activity
Identifying the Culprit
Lock Hints
SEGREGATING OLTP AND DSS APPLICATIONS
ENVIRONMENTAL CONCERNS
Case Sensitivity
Nullability and ANSl Compliance Settings
Locale-Specific SET Options
SUMMARY
Chapter 17 Configuration and Performance Monitoring
OPERATING SYSTEM CONFIGURATION SETTINGS
Task Management
Resource Allocation
PAGEFILE.SYS Location
File System Selection
Nonessential Services
Network Protocols
SQL SERVER CONFIGURATION SETTINGS
Serverwide Options
Buffer Manager Options
Startup Parameters on SQLSERVR.EXE
SYSTEM MAINTENANCE
MONITORING SYSTEM BEHAVIOR
SQL Profiler
System Monitor
Other Performance Monitoring Considerations
SUMMARY
Bibliography and Suggested Reading
Index
鄂公网安备 42010302001612号 