ISBN: 0-7356-0998-5 
Pages: 1088 
Disk: 2 Companion CD(s) 
Stolin-Softwares Price: $49.99
Release: 11/15/2000 
Level: Intermediate 

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

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