Beginning SQL Server 2000 Programming

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

ISBN: 9781590592526 | 1590592522
Cover: Paperback
Copyright: 9/1/2003

eBook

Available Instantly

Online: 180 Days

Downloadable: 180 Days

$32.91

Microsoft SQL Server 2000 provides enterprise-class database solutions, offering interactive development tools and complete management of your relational database applications.In this book youll build a sample database with SQL Server and in the process learn how to design with solutions in mind. If you are migrating from a desktop solution such as Access to SQL Server, this book will provide you with clear and practical demonstrations of the relevant tasks at every stage in the process. Once youve mastered the basics of SQL Server, youll move on to learn more advanced techniques. By the end of this book, youll have a firm grasp of the most important SQL Server features and be able to develop your own effective database solutions.

Introduction

(6)

What's Covered in this Book

(2)

Who is this Book For?

(1)

What You Need to Use this Book

(1)

Conventions

(1)

Tell Us What You Think

(1)

Errata & Updates

(2)

Chapter 1: SQL Server 2000 Installation

(52)

Why SQL Server 2000?

(1)

SQL Server Versions

(3)

Enterprise Edition

(1)

Enterprise Evaluation Edition

(1)

Developer Edition

(1)

Standard Edition

(1)

Personal Edition

(1)

SQL Server CE Edition

(1)

Desktop Edition

(1)

Hardware Requirements

(2)

CPU

(1)

Memory

(1)

Hard Disk

(1)

Screen Resolution

(1)

Other Hardware Considerations

(1)

Operating System Requirements

(2)

How Can You Tell?

(4)

Which Edition for this Book?

(1)

The Example

(1)

Installation

(36)

Installing SQL Server 2000 Developer Edition

(11)

Advanced Options

(2)

Other Types of Installation

(3)

The Connectivity Only Option

(1)

Client Tools Only

(1)

Multiple Instances

(3)

Typical, Minimum, or Custom?

(3)

Typical

(1)

Minimum

(1)

Custom

(1)

Destination Folder

(1)

Security

(50)

Services Accounts

(8)

Authentication Mode

(6)

Mixed Mode

(1)

The sa Login

(1)

Summary

(3)

Chapter 2: Managing SQL Server

(30)

Enterprise Manager

(8)

Service Manager

(2)

Configuring SQL Server Installations

(11)

Registering a New SQL Server

(5)

Summary

(3)

Chapter 3: Query Analyzer

(48)

Starting Query Analyzer

(6)

Starting from Enterprise Manager

(6)

The Object Browser

(4)

System Objects

(2)

Common Objects

(1)

Templates

(2)

The Query Pane

100

(1)

The Results Pane

101

(1)

Menu Options

101

(12)

The File Menu

101

(1)

The Edit Menu

101

(1)

Query Menu

101

(12)

The Tools Menu

113

(8)

The Query Analyzer Toolbar

121

(6)

Using Query Analyzer

127

(8)

Creating a New Login Account

127

(3)

Altering the Default Database for a User

130

(5)

Summary

135

(2)

Chapter 4: Database Analysis and Design

137

(14)

Gathering the Data

138

(2)

The information to Record

140

(2)

Club Details

140

(1)

Players

141

(1)

Matches

141

(1)

League

141

(1)

Society Groups

142

(1)

Newsletters

142

(1)

External and ignored information

142

(1)

Normalization

143

(4)

Each Entity Should Have a Unique Identifier

143

(1)

Only Store Information that Directly Relates to that Entity

143

(1)

Avoid Repeating Values or Columns

144

(1)

First Normal Form (INF)

144

(1)

Second Normal Form (2NF)

145

(1)

Third Normal Form (3NF)

145

(1)

Denormalization

146

(1)

Relationships

147

(1)

Database Diagram

147

(1)

Using the System, OLTP or OLAP?

148

(1)

OLTP

148

(1)

Data Placement

148

(1)

Backups

148

(1)

Indexes

148

(1)

OLAP

149

(1)

Summary

149

(2)

Chapter 5: Creating the Sample Database

151

(38)

Defining a Database

152

(1)

Transaction Logs

153

(1)

Databases within SQL Server

154

(3)

master

154

(1)

tempdb

155

(1)

model

156

(1)

msdb

156

(1)

pubs

156

(1)

Northwind

157

(1)

Creating our Sample Database

157

(30)

Creating a Database in Enterprise Manager

158

(12)

Viewing within Enterprise Manager

164

(6)

Dropping the Database in Enterprise Manager

170

(3)

Creating a Database - Using a Wizard

173

(5)

Creating a Database in Query Analyzer

178

(7)

Viewing in Query Analyzer

183

(1)

Using Commands - Query Analyzer

184

(1)

Dropping a Database in Query Analyzer

185

(2)

Summary

187

(2)

Charter 6: Defining Tables

189

(40)

What is a Table?

190

(1)

Before You Can Define a Table

191

(15)

Server Roles

204

(2)

Defining a Table - Enterprise Manager

206

(6)

Defining a Table Through Query Analyzer

212

(3)

Generating IDENTITY values

215

(1)

The Use of NULL Values

215

(1)

Why Define a Column to Allow NULL?

216

(1)

Defining a Table - Using a Template

216

(5)

Checking if a Table has Already Been Created

220

(1)

The ALTER TABLE Command

221

(1)

Defining the Remaining Tables

221

(1)

Different Data Types

222

(4)

Image and Large Text Storage in SQL Server

226

(1)

Summary

227

(2)

Chapter 7: Creating Indexes

229

(36)

What is an Index?

230

(3)

What Types of Indexes are There?

231

(1)

Clustered

231

(1)

Non-Clustered

231

(1)

Primary

231

(1)

Foreign/Referencing Key

232

(1)

Candidate/Alternate Key

232

(1)

Unique Key

232

(1)

Uniqueness

232

(1)

Determining What Makes a Good Index

233

(2)

Low Maintenance

233

(1)

Primary and Foreign Keys

234

(1)

Finding Specific Records

234

(1)

Covered Indexes

234

(1)

Looking for a Range of Information

235

(1)

Keeping the Data in Order

235

(1)

Determining What Makes a Bad Index

235

(2)

Poor Choice of Columns

236

(1)

Poor Selectivity of Data

236

(1)

Including Many Columns in an Index

236

(1)

Small Number of Records in the Table

236

(1)

Constantly Review

237

(1)

Good, Bad, How do I Tell?

237

(1)

Creating an Index

237

(25)

Creating an Index - Table Designer

237

(4)

Indexes and Statistics

241

(1)

Setting a Primary Key

241

(4)

Creating an Index - Using a Wizard

245

(4)

Creating an Index - Tasks

249

(3)

The CREATE INDEX Syntax

252

(2)

IGNORE_DUP_KEY

253

(1)

DROP_EXISTING

254

(1)

Creating an Index in Query Analyzer - Template

254

(4)

Creating an Index - Query Analyzer - SQL Code

258

(2)

Dropping an Index

260

(1)

Altering an Index in Query Analyzer

260

(2)

Summary

262

(3)

Chapter 8: Building Relationships

265

(20)

What is a Relationship?

266

(1)

What is Referential Integrity?

266

(1)

Types of Relationships

267

(2)

What is a Foreign Key?

269

(1)

Creating a Relationship

270

(6)

Check Existing Data on Creation

275

(1)

Enforce Relationship for Replication

275

(1)

Enforce Relationship for INSERTs and UPDATEs

275

(1)

Creating a Relationship - Missing Keys

276

(3)

Using the Alter Table SQL Statement

279

(2)

Display Dependencies

281

(2)

Summary

283

(2)

Chapter 9: Diagramming the Database

285

(38)

What is a Database Diagram?

286

(1)

The Database Diagramming Tool

287

(1)

The Default Database Diagram

288

(1)

Creating a Database Diagram

289

(6)

Altering the Layout

295

(9)

Working on the Database from the Diagram

304

(7)

Adding in Further Relationships

311

(4)

Creating the Default Diagram

315

(4)

Summary

319

(4)

Chapter 10: Database Backups and Recovery

323

(44)

Backup Strategies

324

(2)

When Problems May Occur

326

(1)

Taking a Database Offline

326

(3)

Bringing a Database Back Online

329

(1)

Backing Up the Data

329

(6)

Backing up the Transaction Log with a Wizard

335

(9)

Restoring a Database

344

(6)

Detaching and Attaching a Database

350

(10)

Producing SQL Script for the Database

360

(4)

Summary

364

(3)

Chapter 11: Maintaining Your Database

367

(20)

Database Maintenance

368

(1)

Creating a Database Maintenance Plan

368

(10)

Data Optimization Information

378

(1)

Database Integrity Check

379

(1)

Database Backup Plan and Transaction Log Backup Plan

379

(1)

Reports

379

(1)

New Operator

379

(1)

Maintenance Plan History

380

(1)

Checking the Maintenance Plan

381

(3)

Summary

384

(3)

Chapter 12: Inserting Data

387

(32)

Who Can Add Data

388

(1)

The T-SQL INSERT Command Syntax

389

(1)

INSERT SQL Command

390

(2)

Using NULL and Default Values

392

(2)

Allowing NULLS and the Allow Nulls Option

393

(1)

Default Values

394

(1)

Getting it Wrong

394

(1)

Allowing Defaults to Populate Columns

394

(2)

Altering the Table

394

(2)

Inserting Data Using Default Values

396

(4)

Using Enterprise Manager

396

(6)

DBCC CHECKIDENT

399

(1)

Using Query Analyzer

400

(1)

Dragging and Dropping Column Names

401

(1)

Column Constraints

402

(6)

Add Constraint

403

(5)

Inserting Images

408

(4)

Where to Store Images

409

(2)

WRITETEXT and TEXTPTR

411

(1)

Dealing with Several Records at Once

412

(3)

Inserting Several Records in a Query Batch

413

(2)

No Permissions for INSERT

415

(2)

Summary

417

(2)

Chapter 13: Retrieving Data

419

(38)

Using Enterprise Manager

420

(3)

The SELECT Statement

423

(2)

Naming the Columns

425

(1)

The First Searches

426

(2)

Displaying the Output Differently

428

(3)

Limiting the Search

431

(4)

String Functions

435

(2)

LTRIM/RTRIM

435

(1)

LEFT/RIGHT

436

(1)

Order! Order!

437

(2)

Other Limiting Methods

439

(4)

SET ROWCOUNT n

440

(1)

TOP n

441

(1)

TOP n PERCENT

442

(1)

The LIKE Operator

443

(4)

Retrieving Pictures

447

(2)

Creating Data - SELECT INTO

449

(2)

Using More Than One Table

451

(4)

Summary

455

(2)

Chapter 14: Updating Data and Transactions

457

(20)

The UPDATE Command

458

(1)

Updating Data - Query Analyzer

458

(5)

Transactions

463

(2)

Atomicity

464

(1)

Consistency

464

(1)

Isolation

464

(1)

Durability

464

(1)

BEGIN TRAN

464

(1)

COMMIT IRAN

465

(1)

ROLLBACK IRAN

465

(1)

Locking Data

465

(1)

Updating Data - Using Transactions

466

(3)

Updating Image Data Types

469

(2)

Building Your Own Template

471

(4)

Summary

475

(2)

Chapter 15: Deleting Data

477

(12)

DELETE Syntax

478

(1)

The DELETE Statement

478

(3)

Transaction Log Activity

481

(1)

TOP Clause in DELETE Statements

482

(1)

Using Enterprise Manager to Delete Rows

482

(2)

Truncate Table

484

(1)

Dropping the Table

485

(1)

Summary

486

(3)

Chapter 16: Building a View

489

(32)

What Is a View?

490

(1)

Using Views as Security

491

(1)

Encrypting Views

492

(1)

Creating a View - Enterprise Manager

492

(6)

Creating a View - Using a Wizard

498

(10)

CREATE VIEW Syntax

508

(1)

Creating a View - Query Analyzer

509

(1)

Creating a View - Using a Template

510

(4)

Setting Permissions on a View

514

(1)

Indexing a View

515

(3)

Summary

518

(3)

Chapter 17: Stored Procedures

521

(34)

What is a Stored Procedure?

521

(1)

Why Not Choose a View?

522

(1)

CREATE PROCEDURE Syntax

523

(2)

Returning a Set of Records

525

(1)

Creating a Stored Proc - Enterprise Manager

525

(4)

Creating a Stored Proc - Using a Wizard

529

(9)

Returning an Error using RETURN

538

(1)

Use of the SET Statement

539

(1)

Creating a Stored Proc - Using a Template

539

(5)

Controlling the Flow

544

(2)

IF ELSE

544

(1)

BEGIN. .. END

545

(1)

CASE Statement

545

(1)

Validating a Parameter - Using Query Analyzer

546

(3)

Using the CASE Statement

549

(3)

Summary

552

(3)

Chapter 18: Advanced Stored Procedures

555

(24)

System Variables

555

(2)

@@ERROR

556

(1)

@@IDENTITY

556

(1)

Setting Multiple Values

557

(1)

Altering a Stored Procedure

557

(7)

RAISERROR

564

(8)

System Stored Procedures

572

(5)

sp_addmessage

572

(1)

sp_help

573

(1)

sp_password

574

(1)

sp_who

574

(1)

System Catalog Stored Procedures

575

(1)

Extended Stored Procedures

576

(1)

Summary

577

(2)

Chapter 19: XML and Data Retrieval

579

(52)

HTML/XML background

580

(2)

How does XML work?

582

(3)

Installing IIS 5 on Windows 2000

585

(7)

Testing Your IIS 5 Installation

592

(2)

SQL Server and IIS

594

(13)

Retrieving Data Through XML

607

(6)

Web Assistant Wizard

613

(12)

Retrieving images

625

(2)

XPath Queries

627

(1)

Summary

628

(3)

Chapter 20: Triggers

631

(24)

What Is a Trigger?

632

(1)

CREATE TRIGGER Syntax

633

(1)

Why Not Use a Constraint?

634

(1)

Deleted and Inserted Logical Tables

635

(1)

Creating a Trigger

635

(4)

Dealing with Table Updates

639

(6)

Using the UPDATE() Function

645

(7)

What About Image Data Types?

652

(1)

Summary

653

(2)

Appendix A: Further References

655

(20)

SQL Server Resources

655

(5)

Books Online

655

(5)

Internet Resources

660

(9)

Microsoft Sites

660

(6)

SQL Server

661

(2)

Technet

663

(1)

MSDN

664

(2)

Microsoft Knowledge Base

666

(1)

Non-Microsoft Sites

666

(78)

Online Magazines

667

(1)

User Groups

668

(1)

Books

669

(6)

Appendix B: System Functions

675

(14)

Appendix C: Function Listing

689

(22)

String Functions

689

(7)

Data Type Conversions

696

(3)

Date and Time Functions

699

(4)

Mathematical Functions

703

(8)

Appendix D: Access Upsizing

711

(8)

Access Upsizing Wizard

711

(2)

SQL Server DTS

713

(6)

Appendix E: Security

719

(8)

Users

719

(1)

Logins

719

(1)

Roles

720

(1)

Initial Permissions

720

(2)

Giving Other Users Access Rights

722

(5)

Appendix F: Glossary of Terms

727

(16)

Appendix G: Support, Errata, and forums.apress.com

743

(8)

Support and Errata on www.apress.com

744

(1)

Finding Errata

744

(1)

Adding an Erratum to the Web Site

744

(1)

Customer Support

744

(1)

What We Can't Answer

745

(1)

How to Tell Us Exactly What You Think

745

(1)

The Peer-to-Peer Forums at forums.apress.com

745

(6)

Index

751

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

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Beginning SQL Server 2000 Programming

Summary

Table of Contents

Supplemental Materials