Inside Microsoft SQL Server 2000

by Delaney, Kalen

Edition: 3rd

ISBN13: 9780735609983

ISBN10: 0735609985

Format: Paperback

Pub. Date: 2000-11-15

Publisher(s): Microsoft Press

Other versions by this Author

List Price: ~~$64.82~~

Rent Book

Select for Price

Add to Cart

There was a problem. Please try again later.

New Book

We're Sorry
Sold Out

Used Book

We're Sorry
Sold Out

eBook

We're Sorry
Not Available

Buy from our Marketplace starting at $15.14

Summary

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

Author Biography

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.)

Foreword

xxi

Prefaces

xxiii

System Requirements

xxxv

Part I Overview

The Evolution of Microsoft SQL Server: 1989 to 2000

(28)

SQL Server: The Early Years

(2)

Ron's Story

(1)

Kalen's Story

(1)

Microsoft SQL Server Ships

(2)

Development Roles Evolve

(1)

OS/2 and Friendly Fire

(2)

SQL Server 4.2

(2)

OS/2 2.0 Release On Hold

(1)

Version 4.2 Released

(1)

SQL Server for Windows NT

(5)

Success Brings Fundamental Change

(1)

The End of Joint Development

(2)

The Charge to SQL95

(2)

The Next Version

(1)

The Secret of the Sphinx

(2)

Software for the New Century

(3)

A Tour of SQL Server

(38)

The SQL Server Engine

(4)

Transact-SQL

(4)

DBMS-Enforced Data Integrity

(5)

Declarative Data Integrity

(1)

Datatypes

(1)

Check Constraints and Rules

(1)

Defaults

(1)

Triggers

(2)

Transaction Processing

(2)

Atomicity

(1)

Consistency

(1)

Isolation

(1)

Durability

(1)

Symmetric Server Architecture

(2)

Traditional Process/Thread Model

(1)

SQL Server Process/Thread Model

(1)

Multiuser Performance

(1)

Security

(1)

Monitoring and Managing Security

(1)

High Availability

(1)

Distributed Data Processing

(2)

Data Replication

(2)

Systems Management

(5)

SQL Server Enterprise Manager

(2)

Distributed Management Objects

(1)

Windows Management Instrumentation

(1)

SQL-DMO and Visual Basic Scripting

(1)

SQL Server Agent

(2)

SQL Server Utilities and Extensions

(6)

Web Assistant Wizard and Internet Enabling

(1)

SQL Profiler

(1)

SQL Server Service Manager

(1)

System Monitor Integration

(1)

Client Network Utility

(1)

Server Network Utility

(1)

SQL Server Installation

(1)

OSQL and ISQL

(1)

SQL Query Analyzer

(1)

Bulk Copy and Data Transformation Services

(1)

SNMP Integration

(1)

SQL Server Books Online

(1)

Client Development Interfaces

(3)

ODBC

(1)

OLE DB

(1)

ADO

(1)

DB-Library

(1)

ESQL/C

(1)

Server Development Interface

(1)

Summary

(3)

Part II Architectural Overview

SQL Server Architecture

(46)

The SQL Server Engine

(22)

The Net-Library

(4)

Open Data Services

(3)

The Relational Engine and the Storage Engine

(4)

The Access Methods Manager

(1)

The Row Operations Manager and the Index Manager

(4)

The Page Manager and the Text Manager

(1)

The Transaction Manager

(3)

The Lock Manager

(1)

Other Managers

(1)

Managing Memory

(10)

The Buffer Manager and Memory Pools

(1)

Access to In-Memory Pages

(1)

Access to Free Pages (Lazywriter)

(2)

Checkpoints

(2)

Accessing Pages Using the Buffer Manager

(1)

Large Memory Issues

(4)

The Log Manager

101

(1)

Transaction Logging and Recovery

101

(5)

Locking and Recovery

104

(1)

Page LSNs and Recovery

104

(2)

The SQL Server Kernel and Interaction with the Operating System

106

(5)

Threading and Symmetric Multiprocessing

107

(2)

The Worker Thread Pool

109

(2)

Disk I/O in Windows NT/2000

111

(1)

Summary

111

(4)

Part III Using Microsoft SQL Server

Planning for and Installing SQL Server

115

(56)

SQL Server Editions

116

(3)

Embedded SQL Server

119

(1)

Hardware Guidelines

119

(2)

Use Hardware on the Windows Hardware Compatibility List

119

(1)

Performance = Fn (Processor Cycles, Memory, I/O Throughput)

120

(1)

Invest in Benchmarking

121

(1)

Hardware Components

121

(20)

The Processor

122

(2)

Memory

124

(1)

Disk Drives, Controllers, and Disk Arrays

125

RAID Solutions

120

(17)

More About Drives and Controllers

137

(1)

Uninterruptible Power Supply

138

(1)

The Disk Subsystem

139

(1)

Fallback Server Capability

140

(1)

Other Hardware Considerations

141

(1)

The Operating System

141

(1)

The File System

142

(1)

Security and the User Context

143

(2)

Licensing

145

(5)

SQL Server Processor License

145

(1)

Server Licenses and CALs

145

(1)

Multiplexing: Use of Middleware, Transaction Servers, and Multitiered Architectures

146

(1)

Multiple Instances

147

(3)

Network Protocols

150

(1)

Collation

150

(7)

Character Sets

151

(1)

Sort Orders

152

(5)

Multiple Instances

157

(3)

Installing Named Instances

158

(1)

Named Instance Server Connectivity

159

(1)

Installing SQL Server

160

(2)

Upgrading from a Previous Version

161

(1)

Basic Configuration After Installation

162

(4)

Starting the SQL Server Service

162

(1)

Changing the System Administrator Password

163

(1)

Configuring SQL Server's Error Log

164

(1)

Working with Multiple Instances

164

(2)

Remote and Unattended Installation

166

(4)

Remote Installation

167

(1)

Unattended Installation

167

(1)

Changing Installation Options

168

(1)

Adding Additional Components

169

(1)

Summary

170

(1)

Databases and Database Files

171

(50)

Special System Databases

172

(3)

master

172

(1)

model

173

(1)

termpdb

173

(1)

pubs

174

(1)

North wind

174

(1)

msdb

175

(1)

Database Files

175

(1)

Creating a Database

176

(3)

A Create Database Example

178

(1)

Expanding and Shrinking a Database

179

(3)

Automatic File Expansion

179

(1)

Manual File Expansion

179

(1)

Automatic File Shrinkage

179

(1)

Manual File Shrinkage

180

(2)

Changes in Log Size

182

(4)

Log Truncation

185

(1)

Using Database Filegroups

186

(4)

The Default Filegroup

187

(2)

A Filegroup Creation Example

189

(1)

Altering a Database

190

(2)

Alter Database Examples

191

(1)

Databases Under the Hood

192

(5)

Space Allocation

194

(3)

Setting Database Options

197

(8)

State Options

198

(4)

Cursor Options

202

(1)

Auto Options

202

(1)

SQL Options

203

(2)

Recovery Options

205

(1)

Other Database Considerations

205

(4)

Databases vs. Schemas

205

(1)

Using Removable Media

206

(1)

Detaching and Reattaching a Database

207

(1)

Compatibility Levels

208

(1)

Backing Up and Restoring a Database

209

(10)

Types of Backups

210

(1)

Recovery Models

211

(3)

Choosing a Backup Type

214

(1)

Restoring a Database

215

(4)

Summary

219

(2)

Tables

221

(96)

Creating Tables

222

(17)

Naming Tables and Columns

223

(1)

Reserved Keywords

224

(1)

Delimited Identifiers

224

(1)

Naming Conventions

225

(1)

Datatypes

226

(9)

Much Ado About NULL

235

(4)

User-Defined Datatypes

239

(2)

Identity Property

241

(4)

Internal Storage

245

(25)

Data Pages

246

(2)

Examining Data Pages

248

(4)

The Structure of Data Rows

252

(2)

Column Offset Arrays

254

(1)

Storage of Fixed-Length and Variable-Length Rows

255

(4)

Page Linkage

259

(1)

Text and Image Data

260

(6)

sql_variant Datatype

266

(4)

Constraints

270

(36)

Primary Key and Unique Constraints

271

(6)

Foreign Key Constraints

277

(9)

Constraint-Checking Solutions

286

(1)

Restrictions on Dropping Tables

287

(1)

Self-Referencing Tables

287

(2)

Check Constraints

289

(5)

Default Constraints

294

(4)

More About Constraints

298

(8)

Altering a Table

306

(3)

Changing a Datatype

306

(2)

Adding a New Column

308

(1)

Adding, Dropping, Disabling, or Enabling a Constraint

308

(1)

Dropping a Column

309

(1)

Enabling or Disabling a Trigger

309

(1)

Temporary Tables

309

(3)

Private Temporary Tables (#)

310

(1)

Global Temporary Tables (##)

310

(1)

Direct Use of tempdb

311

(1)

Constraints on Temporary Tables

311

(1)

System Tables

312

(3)

Summary

315

(2)

Querying Data

317

(86)

The Select Statement

317

(3)

Joins

320

(16)

Outer Joins

324

(5)

The Obsolete *= Outer Join Operator

329

(6)

Cross Joins

335

(1)

Dealing With Null

336

(9)

NULL in the Real World

340

(3)

Is NULL and = NULL

343

(2)

Subqueries

345

(12)

Correlated Subqueries

350

(7)

Views and Derived Tables

357

(8)

Altering Views

361

(1)

Partitioned Views

362

(3)

Other Search Expressions

365

(36)

365

(6)

Between

371

(1)

Aggregate Functions

371

(7)

Datacube---Aggregate Variations

378

(14)

Top

392

(3)

Union

395

(6)

Summary

401

(2)

Indexes

403

(46)

Index Organization

404

(4)

Clustered Indexes

406

(1)

Nonclustered Indexes

406

(2)

Creating an Index

408

(3)

Constraints and Indexes

410

(1)

The Structure of Index Pages

411

(16)

Clustered Index Rows with a Uniqueifier

412

(3)

Index Row Formats

415

(12)

Index Space Requirements

427

(4)

B-Tree Size

427

(1)

Actual vs. Estimated Size

428

(3)

Managing an Index

431

(6)

Types of Fragmentation

431

(1)

Detecting Fragmentation

432

(3)

Removing Fragmentation

435

(2)

Special Indexes

437

(9)

Prerequisites

437

(4)

Indexes on Computed Columns

441

(2)

Indexed Views

443

(3)

Using an Index

446

(2)

Looking for Rows

446

(1)

Joining

446

(1)

Sorting

446

(2)

Grouping

448

(1)

Maintaining Uniqueness

448

(1)

Summary

448

(1)

Modifying Data

449

(50)

Basic Modification Operations

449

(27)

Insert

450

(13)

Update

463

(2)

Delete

465

(2)

Modifying Data Through Views

467

(9)

Data Modification Internals

476

(22)

Inserting Rows

477

(1)

Splitting Pages

477

(4)

Deleting Rows

481

(8)

Updating Rows

489

(6)

Table-Level vs. Index-Level Data Modification

495

(2)

Logging

497

(1)

Locking

497

(1)

Summary

498

(1)

Programming with Transact-SQL

499

(100)

Transact-SQL As A Programming Language

500

(3)

Programming at Multiple Levels

501

(2)

Transact-SQL Programming Constructs

503

(60)

Variables

503

(7)

Control-of-Flow Tools

510

(1)

Case

511

(3)

514

(1)

Raiserror

515

(3)

Formatmessage

518

(1)

Operators

519

(8)

Scalar Functions

527

(35)

Table-Valued Functions

562

(1)

Transact-SQL Examples and Brainteasers

563

(16)

Generating Test Data

563

(4)

Getting Rankings

567

(6)

Finding Differences Between Intervals

573

(5)

Selecting Instead of Iterating

578

(1)

Full-Text Searching

579

(19)

Full-Text Indexes

581

(1)

Setting Up Full-Text Indexes

582

(3)

Maintaining Full-Text Indexes

585

(4)

Querying Full-Text Indexes

589

(8)

Performance Considerations for Full-Text Indexes

597

(1)

Summary

598

(1)

Batches, Stored Procedures, and Functions

599

(58)

Batches

600

(4)

Routines

604

(1)

Stored Procedures

605

(12)

Nested Stored Procedures

607

(1)

Recursion in Stored Procedures

608

(5)

Stored Procedure Parameters

613

(4)

User-Defined Functions

617

(10)

Table Variables

(557)

Scalar-Valued Functions

618

(3)

Table-Valued Functions

621

(3)

System Table-Valued Functions

624

(1)

Managing User-Defined Functions

624

(3)

Rewriting Stored Procedures as Functions

627

(1)

Rolling Your Own System Routines

628

(4)

Your Own System Procedures

628

(2)

Your Own System Functions

630

(2)

Executing Batches, or What's Stored about Stored Procedures (and Functions)?

632

(8)

Parse Commands and Create the Sequence Tree

632

(1)

Compile the Batch

632

(1)

Execute

632

(2)

Recompile Execution Plans

634

(2)

Storage of Routines

636

(1)

Encrypting Routines

637

(3)

Altering a Routine

640

(1)

Temporary Stored Procedures

640

(2)

Private Temporary Stored Procedures

640

(1)

Global Temporary Stored Procedures

641

(1)

Procedures Created from Direct Use of tempdb

641

(1)

Autostart Stored Procedures

642

(1)

System Stored Procedures

643

(10)

General System Procedures

644

(1)

Catalog Stored Procedures

645

(1)

SQL Server Agent Stored Procedures

646

(1)

Replication Stored Procedures

646

(1)

Extended Stored Procedures

647

(6)

Execute (``Any String'')

653

(2)

Summary

655

(2)

Transactions and Triggers

657

(46)

Transactions

657

(25)

Explicit and Implicit Transactions

659

(1)

Error Checking in Transactions

660

(6)

Transaction Isolation Levels

666

(10)

Other Characteristics of Transactions

676

(1)

Nested Transaction Blocks

677

(4)

Savepoints

681

(1)

Triggers

682

(19)

After Triggers

682

(6)

Instead-of Triggers

688

(4)

Managing Triggers

692

(1)

Using Triggers to Implement Referential Actions

692

(8)

Recursive Triggers

700

(1)

Summary

701

(2)

Special Transact-SQL Operations: Working with Cursors and Large Objects

703

(70)

Cursor Basics

704

(3)

Cursors and ISAMs

707

(4)

Problems with ISAM-Style Applications

710

(1)

Cursor Models

711

(4)

Transact-SQL Cursors

711

(1)

API Server Cursors

712

(1)

Client Cursors

713

(1)

Default Result Sets

714

(1)

API Server Cursors vs. Transact-SQL Cursors

714

(1)

Appropriate Use of Cursors

715

(10)

Row-by-Row Operations

716

(1)

Query Operations

716

(1)

Scrolling Applications

717

(1)

Choosing a Cursor

718

(2)

Cursor Membership, Scrolling, and Sensitivity to Change

720

(5)

Working with Transact-SQL Cursors

725

(18)

Declare

726

(2)

Open

728

(1)

Fetch

729

(1)

Update

730

(1)

Delete

731

(1)

731

(1)

Deallocate

732

(1)

The Simplest Cursor Syntax

732

(1)

Fully Scrollable Transact-SQL Cursors

732

(3)

Concurrency Control with Transact-SQL Cursors

735

(8)

Cursor Variables

743

(7)

Obtaining Cursor Information

744

(6)

Working with Text and Image Data

750

(19)

Writetext

752

(3)

Readtext

755

(6)

Updatetext

761

(8)

Summary

769

(4)

Part IV Performance and Tuning

Locking

773

(42)

The Lock Manager

773

(6)

The Lock Manager and Isolation Levels

774

(1)

Spinlocks

775

(1)

Deadlocks

775

(4)

Lock Types for User Data

779

(19)

Lock Modes

780

(1)

Lock Granularity

780

(10)

Lock Duration

790

(1)

Lock Ownership

790

(1)

Viewing Locks

791

(7)

Lock Compatibility

798

(2)

Internal Locking Architecture

800

(7)

Lock Blocks

802

(2)

Lock Owner Blocks

804

(1)

Syslockinfo Table

804

(3)

Bound Connections

807

(4)

Row-Level vs. Page-Level Locking

811

(2)

Lock Escalation

812

(1)

Locking Hints and Trace Flags

813

(1)

Summary

813

(2)

The Query Processor

815

(52)

The SQL Manager

816

(1)

Compilation and Optimization

816

(36)

Compilation

817

(1)

Optimization

818

(2)

How the Query Optimizer Works

820

(19)

Join Selection

839

(6)

Other Processing Strategies

845

(1)

Maintaining Statistics

846

(6)

The Procedure Cache

852

(3)

Using Stored Procedures and Caching Mechanisms

855

(10)

Ad Hoc Caching

856

(1)

Autoparameterization

856

(1)

The sp_executesql Procedure

857

(1)

The Prepare and Execute Method

857

(1)

Sharing Cached Plans

857

(1)

Examining the Plan Cache

858

(2)

Multiple Plans in Cache

860

(2)

When to Use Stored Procedures and Other Caching Mechanisms

862

(1)

Recompiling Stored Procedures

862

(1)

Other Benefits of Stored Procedures

863

(2)

Execution

865

(1)

Summary

865

(2)

Query Tuning

867

(76)

The Development Team

868

(1)

Application and Database Design

868

(6)

Normalize Your Database

869

(2)

Evaluate Your Critical Transactions

871

(2)

Keep Table Row Lengths and Keys Compact

873

(1)

Planning for Peak Usage

874

(1)

Perceived Response Time for Interactive Systems

874

(2)

Prototyping, Benchmarking, and Testing

876

(4)

Development Methodologies

878

(2)

Creating Useful Indexes

880

(9)

Choose the Clustered Index Carefully

881

(1)

Make Nonclustered Indexes Highly Selective

882

(1)

Tailor Indexes to Critical Transactions

883

(2)

Pay Attention to Column Order

885

(1)

Index Columns Used in Joins

885

(2)

Create or Drop Indexes as Needed

887

(1)

The Index Tuning Wizard

887

(2)

Monitoring Query Performance

889

(25)

Statistics IO

889

(4)

Statistics Time

893

(1)

Showplan

893

(15)

Using Query Hints

908

(4)

Stored Procedure Optimization

912

(2)

Concurrency and Consistency Tradeoffs

914

(1)

Resolving Blocking Problems

915

(4)

Indexes and Blocking

917

(2)

Resolving Deadlock Problems

919

(15)

Cycle Deadlock Example

919

(1)

Conversion Deadlock Example

919

(3)

Preventing Deadlocks

922

(1)

Handling Deadlocks

922

(1)

Volunteering to Be the Deadlock Victim

923

(1)

Watching Locking Activity

923

(1)

Identifying the Culprit

924

(7)

Lock Hints

931

(3)

Segregating OLTP and DSS Applications

934

(1)

Environmental Concerns

935

(7)

Case Sensitivity

935

(1)

Nullability and ANSI Compliance Settings

936

(6)

Locale-Specific SET Options

942

(1)

Summary

942

(1)

Configuration and Performance Monitoring

943

(44)

Operating System Configuration Settings

944

(2)

Task Management

944

(1)

Resource Allocation

944

(1)

PAGEFILE.SYS Location

945

(1)

File System Selection

945

(1)

Nonessential Services

946

(1)

Network Protocols

946

(1)

SQL Server Configuration Settings

946

(14)

Serverwide Options

947

(11)

Buffer Manager Options

958

(2)

Startup Parameters on SQLSERVR.EXE

960

(1)

System Maintenance

960

(1)

Monitoring System Behavior

961

(24)

SQL Profiler

961

(15)

System Monitor

976

(9)

Other Performance Monitoring Considerations

985

(1)

Summary

985

(2)

Bibliography and Suggested Reading

987

(6)

Index

993

Inside Microsoft SQL Server 2000

Rent Book

New Book

Used Book

eBook

How Marketplace Works:

Summary

Author Biography

Table of Contents

Digital License