暫缺《Microsoft SQL Server 2000技術(shù)內(nèi)幕》作者簡(jiǎn)介
圖書(shū)目錄
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
鄂公網(wǎng)安備 42010302001612號(hào) 