Microsoft SQL Server 2005: A Beginner''s Guide

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9780072260939 | 0072260939
Cover: Paperback
Copyright: 12/13/2005

Buy New

In Stock Usually Ships in 24-48 Hours

$43.21
eBook

Available Instantly

Online: 1825 Days

Downloadable: Lifetime Access

$40.50

It's been five years since Microsoft released a new version of SQL Server-which holds 39% of the Windows database market-and the latest edition of this bestselling beginner's guide gets users up and running on SQL Server 2005. The book covers database concepts, and discusses key topics for new users including the SQL Server Workbench,T-SQL, automated administration tasks, security, and analysis. Previous edition sold 30,000 copies Worked examples and exercises throughout provide hands-on experience Free code will be available online

Acknowledgments

xix

Introduction

xxi

Part I SQL Server: Basic Concepts

Database Systems and SQL Server

(20)

Database Systems: An Overview

(3)

Variety of User Interfaces

(1)

Physical Data Independence

(1)

Logical Data Independence

(1)

Query Optimization

(1)

Data Integrity

(1)

Concurrency Control

(1)

Backup and Recovery

(1)

Security and Authorization

(1)

Relational Database Systems

(3)

Working with the Book's Sample Database

(3)

SQL: A Relational Database Language

(2)

Syntax Conventions

(1)

Database Design

(6)

Normal Forms

(2)

Entity-Relationship (ER) Model

(2)

Conclusion

(1)

Overview of Microsoft SQL Server

(1)

Exercises

(2)

SQL Server Management Studio

(26)

The SQL Server Program Group and Books Online

(1)

Introduction to SQL Server Management Studio

(19)

Using Management Studio with the SQL Server Database Engine

(4)

Managing Databases Using Object Explorer

(7)

Authoring Activities Using SQL Server Management Studio

(5)

Conclusion

(1)

Exercises

(5)

Part II Transact-SQL Language

SQL Components

(24)

SQL's Basic Objects

(3)

Comments

(1)

Identifiers

(1)

Reserved Keywords

(1)

Data Types

(6)

Numeric Data Types

(1)

String Data Types

(2)

Specifying Date and Time

(1)

Derived Data Types

(1)

Miscellaneous Data Types

(1)

User-Defined Data Types

(1)

Predicates

(1)

Aggregate Functions

(1)

Scalar Functions

(7)

Numeric Functions

(1)

Date Functions

(1)

String Functions

(2)

Text/Image Functions

(1)

System Functions

(2)

Scalar Operators

(2)

Global Variables

(1)

Null Values

(1)

Conclusion

(1)

Exercises

(2)

Data Definition Language

(38)

Creating Database Objects

(24)

Creation of a Database

(4)

Create Table: A Basic Form

(2)

Create Table: The Enhanced Form

(1)

Create Table and Declarative Integrity Constraints

(5)

Referential Constraints

(5)

Creating Other Database Objects

(2)

Integrity Constraints and Domains

(5)

Modifying Database Objects

(7)

Altering a Database

(1)

Altering a Table: A Basic Form

100

(2)

Altering a Table: Enhanced Form

102

(3)

Removing Database Objects

105

(1)

Conclusion

106

(1)

Exercises

106

(5)

Simple Queries

111

(58)

Select Statement: A Basic Form

112

(2)

Where Clause

114

(17)

Boolean Operators

117

(5)

In and Between Operators

122

(3)

Queries Involving Null Values

125

(2)

Like Operator

127

(4)

Simple Subqueries

131

(8)

Subqueries and Comparison Operators

132

(1)

Subqueries and In Operator

133

(2)

Any and All Operators

135

(3)

Exists Function

138

(1)

Queries in the From Clause

138

(1)

Group by Clause

139

(2)

Aggregate Functions

141

(8)

Convenient Aggregates

141

(6)

Statistical Aggregates

147

(1)

Superaggregates (Operators Cube and Rollup)

148

(1)

User-Defined Aggregate Functions

148

(1)

Having Clause

149

(1)

Order by Clause

150

(2)

Select Statement and Identity Property

152

(2)

Set Operators

154

(5)

Case Expressions

159

(2)

Compute Clause

161

(3)

Temporary Tables

164

(1)

Computed Columns

165

(1)

Conclusion

166

(1)

Exercises

166

(3)

Complex Queries

169

(36)

Join Operator

170

(17)

Two Syntax Forms to Implement Joins

171

(1)

Equijoin

171

(4)

Cartesian Product

175

(2)

Natural Join

177

(2)

Thetajoin

179

(1)

Joining More Than Two Tables

180

(2)

Joining a Table with Itself

182

(2)

Outer Join

184

(3)

Correlated Subqueries

187

(6)

Exists Function and Subqueries

189

(4)

Should You Use Join or Subqueries?

193

(2)

Subquery Advantages

193

(1)

Join Advantages

194

(1)

Common Table Expressions

195

(7)

CTE and Nonrecursive Queries

195

(2)

Common Table Expressions and Recursive Queries

197

(5)

Conclusion

202

(1)

Exercises

203

(2)

Modification of a Table's Contents

205

(12)

Insert Statement

206

(4)

Inserting a Single Row

206

(3)

Inserting Multiple Rows

209

(1)

Update Statement

210

(3)

Delete Statement

213

(2)

Conclusion

215

(1)

Exercises

216

(1)

Stored Procedures and User-Defined Functions

217

(28)

SQL Extensions

218

(7)

Block of Statements

218

(1)

If Statement

219

(1)

While Statement

220

(1)

Local Variables

221

(1)

Miscellaneous Procedural Statements

222

(1)

Handling Events With Try and Catch Statements

223

(2)

Stored Procedures

225

(12)

Creation and Execution of Stored Procedures

226

(5)

System Stored Procedures

231

(1)

Stored Procedures and CLR

231

(6)

User-Defined Functions

237

(6)

Creation and Execution of User-Defined Functions

237

(4)

User-Defined Functions and CLR

241

(2)

Conclusion

243

(1)

Exercises

243

(2)

Indices and Query Optimization

245

(38)

Indices

246

(14)

Clustered and Nonclustered Indices

248

(2)

Indices and the Corresponding Transact-SQL Statements

250

(6)

Editing Index Information

256

(1)

Indices and Keys

257

(1)

Guidelines for Creating Indices

258

(2)

General Criteria to Improve Efficiency

260

(5)

Join vs. Correlated Subquery

260

(1)

Incomplete Statements

261

(1)

Like Operator

261

(1)

Transact-SQL Statements for Query Optimization

262

(3)

Query Optimizer

265

(13)

Optimizer Statistics

265

(2)

Implementing the Join Operation

267

(1)

Optimizer Hints

268

(10)

The DBCC Command and Indices

278

(2)

Conclusion

280

(1)

Exercises

280

(3)

Views

283

(24)

DDL Statements and Views

284

(7)

Creating a View

285

(4)

Altering and Removing Views

289

(2)

DML Statements and Views

291

(7)

View Retrieval

291

(1)

Insert Statement and a View

292

(3)

Update Statement and a View

295

(2)

Delete Statement and a View

297

(1)

Indexed Views

298

(7)

Creating an Indexed View

299

(3)

Benefits of Indexed Views

302

(1)

Using Indexed Views

303

(2)

Conclusion

305

(1)

Exercises

305

(2)

System Catalog

307

(18)

System Tables

308

(3)

Sysobjects

308

(1)

Syscolumns

309

(1)

Sysindexes

309

(1)

Sysusers

310

(1)

Sysdatabases

310

(1)

Sysdepends

310

(1)

Sysconstraints

311

(1)

Catalog Views

311

(2)

Sys_objects

312

(1)

Sys.columns

313

(1)

Sys.database_principals

313

(1)

Querying Catalog Views

313

(2)

Other Ways to Access System Information

315

(8)

System Procedures

316

(2)

System Functions

318

(2)

Property Functions

320

(1)

Information Schema

320

(3)

Conclusion

323

(1)

Exercises

324

(1)

SQL Server Security

325

(40)

Authentication

326

(6)

SQL Server Encryption Policy and Mechanisms

327

(2)

Setting Up SQL Server Security Using DDL

329

(2)

Setting Up SQL Server Security Using System Procedures

331

(1)

System Procedures Concerning Logins

331

(1)

Schema

332

(4)

User-Schema Separation

333

(1)

DDL Statements Concerning Schema

334

(2)

Database Security

336

(4)

Setting Up Database User Accounts with DDL

336

(2)

Setting Up Database User Accounts Using System Procedures

338

(1)

Default Database Schemas

339

(1)

Roles

340

(11)

Fixed Server Roles

340

(1)

Fixed Server Roles and Their Permissions

341

(2)

Fixed Database Roles

343

(1)

Fixed Database Roles and Their Permissions

344

(2)

Application Roles

346

(3)

User-Defined Database Roles

349

(2)

Authorization

351

(8)

Grant Statement

351

(6)

Deny Statement

357

(1)

Revoke Statement

358

(1)

Views and Data Access

359

(1)

Stored Procedures and Data Access

360

(1)

Conclusion

361

(1)

Exercises

362

(3)

Triggers

365

(20)

Introduction

366

(1)

How Triggers Work

366

(10)

Application Areas for After Triggers

369

(4)

Application Areas for Instead of Triggers

373

(1)

First and Last Triggers

374

(1)

DDL Triggers

375

(1)

Triggers and CLR

376

(5)

The Output Clause

381

(2)

Modifying Trigger's Structure

383

(1)

Conclusion

383

(1)

Exercises

384

(1)

Transactions

385

(20)

Introduction

386

(1)

Transact-SQL Statements and Transactions

387

(4)

Transaction Logging

391

(1)

Locking

391

(6)

Lock Granularity

392

(1)

Kinds of Locks

393

(3)

The Select Statement and Locking

396

(1)

Isolation Levels

397

(4)

Row Versioning and Snapshot Isolation Level

399

(2)

Deadlock

401

(1)

Conclusion

402

(1)

Exercises

402

(3)

SQL Server System Environment

405

(22)

Disk Storage

406

(4)

Data Pages

407

(3)

Large Objects

410

(1)

Index Pages

410

(1)

System Databases

410

(1)

Utilities

411

(5)

bcp Utility

412

(1)

osql Utility

413

(1)

sqlcmd Utility

414

(2)

Instances of SQL Server

416

(2)

Unicode

418

(1)

Character Encoding

418

(1)

SQL Server Architecture

419

(4)

Multiprocessor Hardware Architectures

420

(1)

Multithreading Architecture of SQL Server

421

(2)

Conclusion

423

(1)

Exercises

423

(4)

Part III SQL Server: System Administration

Overview of System Administration

427

(6)

Administration Tools

428

(3)

MS SQL Server 2005 Program Group

429

(1)

SQL Computer Manager

429

(1)

Dynamic Management Views

430

(1)

System Administrator

431

(1)

Conclusion

432

(1)

Planning the Installation and Installing SQL Server

433

(16)

Planning the Installation

434

(3)

Purpose of SQL Server

434

(1)

Hardware and Software Requirements

435

(1)

SQL Server Editions

436

(1)

Installation Recommendations

437

(1)

Installing SQL Server

437

(8)

Beginning the Installation

438

(7)

Configuring SQL Server After Installation

445

(1)

Create Server Groups and Register the Server

445

(1)

Set Server Options

446

(1)

Starting and Stopping an Instance of SQL Server

446

(2)

Conclusion

448

(1)

Managing Databases and Database Files

449

(12)

Managing Filegroups

450

(2)

Viewing Filegroups

451

(1)

Managing Databases

452

(6)

Creating Databases

452

(1)

Viewing and Modifying Database Options

453

(3)

Modifying Databases

456

(2)

Managing Transaction Logs

458

(1)

Conclusion

459

(1)

Exercises

459

(2)

Managing Security

461

(12)

Introduction

462

(3)

Implementing a Security Mode

463

(1)

Managing SQL Server Logins

464

(1)

Database Security Permissions

465

(3)

Roles

465

(3)

Managing Permissions

468

(3)

Conclusion

471

(1)

Exercises

471

(2)

Backup and Recovery

473

(34)

Introduction

474

(1)

Software and Hardware Failures

474

(1)

Transaction Log

475

(1)

Backup

475

(3)

Full Database Backup

476

(1)

Differential Backup

476

(1)

Transaction Log Backup

476

(2)

Database File Backup

478

(1)

Performing Backup

478

(13)

Backup Using Management Studio

478

(4)

Backup Using Transact-SQL Statements

482

(3)

Which Databases to Back Up?

485

(1)

Minimizing System Downtime

486

(3)

High Availability

489

(2)

Recovery

491

(13)

Automatic Recovery

491

(1)

Manual Recovery

492

(9)

Recovery Models

501

(2)

Recovery to a Mark

503

(1)

Conclusion

504

(1)

Exercises

504

(3)

Automating System Administration Tasks

507

(20)

Introduction

508

(1)

SQL Server Agent

509

(1)

Running and Configuring SQL Server Agent

510

(1)

Creating Jobs and Operators

510

(7)

Job Steps

510

(1)

Creating a Job Using SQL Server Management Studio

511

(3)

Creating Job Schedules

514

(1)

Creating Operators for Notification

515

(1)

Viewing the Job History Log

516

(1)

Alerts

517

(7)

Error Messages

517

(2)

SQL Server Error Log

519

(1)

Event Log

519

(1)

Defining Alerts to Handle SQL Server Errors

520

(3)

Defining Alerts to Handle User-Defined Errors

523

(1)

Conclusion

524

(1)

Exercises

525

(2)

Performance and Tuning

527

(30)

Introduction

528

(1)

Factors that Affect Performance

529

(9)

Database Applications and Performance

529

(2)

The Database Server and Performance

531

(1)

System Resources and Performance

532

(1)

CPU

533

(5)

Monitoring Tools

538

(10)

Application Performance Tools

538

(4)

SQL Server-Specific Monitoring

542

(4)

Tools for Monitoring System Resources

546

(2)

Choose the Right Tool

548

(9)

SQL Server Profiler

548

(1)

Database Engine Tuning Advisor

549

(7)

Conclusion

556

(1)

Data Partitioning

557

(12)

Introduction

558

(1)

Ways to Partition Your Data

559

(1)

Steps for Creating Partitioned Tables

559

(7)

Set Partition Goals

560

(1)

Determine Partitioning Key and Number of Partitions

560

(1)

Create a Filegroup for Each Partition

561

(1)

Create Partition Function and Partition Scheme

562

(3)

Collocating Objects

565

(1)

Guidelines for Partitioning Tables and Indices

566

(1)

Conclusion

566

(3)

Data Replication

569

(20)

Distributed Data

570

(2)

Methods for Distributing Data

570

(2)

SQL Server Replication---An Overview

572

(8)

Publishers, Distributors, and Subscribers

572

(1)

Publications and Articles

573

(2)

Replication Types

575

(3)

Replication Models

578

(2)

Managing Replication

580

(4)

Configuring the Distribution and Publication Servers

580

(1)

Setting Up Publications

581

(2)

Setting Up Subscription Servers

583

(1)

Conclusion

584

(1)

Exercises

585

(4)

Part IV Microsoft Analysis Services

Data Warehousing: An Introduction

589

(16)

Online Transaction Processing vs. Data Warehousing

590

(2)

Online Transaction Processing

590

(1)

Data Warehouse Systems

591

(1)

Data Warehouses and Data Marts

592

(3)

Data Warehouse Design

595

(3)

Dimensional Model

595

(3)

Cubes and Storage Modes

598

(2)

MOLAP, ROLAP, and HOLAP

599

(1)

Aggregation

600

(2)

How Much to Aggregate?

601

(1)

Data Access

602

(1)

Conclusion

603

(1)

Exercises

603

(2)

Microsoft Analysis Services

605

(16)

BI Development Studio

607

(5)

Analysis Services and Cubes

612

(7)

Creating a Cube

614

(1)

Designing Storage Aggregation and Processing the Cube

615

(3)

Browsing a Cube

618

(1)

Conclusion

619

(2)

Business Intelligence and SQL

621

(18)

OLAP Extensions in Transact-SQL

622

(15)

Cube Operator

622

(4)

Rollup Operator

626

(2)

Ranking Functions

628

(6)

Top n Clause

634

(2)

Pivot and Unpivot Operators

636

(1)

Conclusion

637

(2)

Microsoft Reporting Services

639

(20)

Introduction

640

(2)

Report Manager

640

(1)

Report Server

641

(1)

Report Server Database

641

(1)

Creating Reports

642

(10)

Report Wizard

643

(7)

Parameterized Reports

650

(2)

Processing and Managing Reports

652

(1)

Accessing and Delivering Reports

653

(3)

On-Demand Reports

653

(2)

Report Subscription

655

(1)

Conclusion

656

(1)

Exercises

656

(3)

Part V XML Support

Overview of XML

659

(10)

World Wide Web

660

(1)

XML and Related Languages

661

(4)

XML-Basic Concepts

661

(4)

Document Type Definitions

665

(3)

XML Schema

667

(1)

Conclusion

668

(1)

SQL Server 2005 and XML

669

(18)

Storing XML in Relational Databases

670

(1)

The XML Data Type

671

(7)

Indexing an XML Column

674

(1)

Typed vs. Untyped XML

675

(3)

Constructing XML Documents Using the for XML Clause

678

(7)

Raw Mode

678

(1)

Auto Mode

679

(1)

Explicit Mode

680

(2)

Path Mode

682

(1)

Specifying Directives with Different Modes

683

(2)

XML Query Languages

685

(1)

Conclusion

686

(1)

Index

687

Please wait while the item is added to your bag...

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Microsoft SQL Server 2005: A Beginner''s Guide

Summary

Author Biography

Table of Contents

Supplemental Materials