Beginning SQL Server 2005 for Developers: From Novice To Professional

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

ISBN: 9781590595886 | 1590595882
Cover: Paperback
Copyright: 1/30/2006

Buy New

In Stock Usually Ships in 24-48 Hours

$48.01
eBook

Available Instantly

Online: 180 Days

Downloadable: 180 Days

$36.56

SQL Server 2005 will increase your programming options, productivity, analysis, and database management. If you have some basic knowledge of relational databases and want to start a career as a developer using SQL Server, then this book is your ideal first step. It explains the core jobs and roles for developing a database in both SQL Server 2000 and 2005. This book features practical steps to help you overcome issues you're likely to encounter. You'll learn to use SQL for querying, inserting, updating, and deleting data. You'll also learn how to back up and restore databases for basic administration in SQL Server. Further, you'll cover how to build a complete database, from the fundamentals of relational database design to table and index creation.

About the Author

xvii

About the Technical Reviewer

xix

Acknowledgments

xxi

Introduction

xxiii

SQL Server 2005 Overview and Installation

(24)

Why SQL Server 2005?

(1)

Evolution of SQL Server

(1)

Hardware Requirements

(2)

CPU

(1)

Memory

(1)

Hard Disk Space

(1)

Operating System Requirements

(1)

The Example

(1)

Installation

(1)

A Standard Installation

(12)

Preparing to Install

(11)

Security

(5)

Services Accounts

(1)

Looking at the Authentication Mode

(4)

The sa Login

(1)

Summary

(2)

SQL Server Management Studio

(28)

A Quick Overview of SSMS

(9)

Examining SSMS's Options

(11)

Environment Node

(3)

Source Control Node

(1)

Text Editor Node

(2)

Query Execution Node

(2)

Query Results Node

(3)

Query Editor

(2)

Surface Area Configuration Tool

(4)

Summary

(2)

Database Design and Creation

(42)

Defining a Database

(1)

Databases Within SQL Server

(3)

master

(1)

tempdb

(1)

model

(1)

msdb

(1)

AdventureWorks/AdventureWorksDW

(1)

Choosing the Database System Type

(2)

OLTP

(1)

OLAP

(1)

Example System Choice

(1)

Gathering the Data

(2)

Determining the Information to Store in the Database

(3)

Financial Products

(1)

Customers

(1)

Customer Addresses

(1)

Transactions

(1)

External and Ignored Information

(1)

Building Relationships

(6)

Using Keys

(2)

Creating Relationships

(3)

More on Foreign Keys

(1)

Normalization

(5)

Each Entity Should Have a Unique Identifier

(1)

Only Store Information That Directly Relates to That Entity

(1)

Avoid Repeating Values or Columns

(1)

Normalization Forms

(2)

Denormalization

(1)

Creating the Sample Database

(18)

Creating a Database in SQL Server Management Studio

(13)

Dropping the Database in SQL Server Management Studio

(3)

Creating a Database in a Query Pane

(2)

Summary

(1)

Security

(24)

Logins

(10)

Server Logins and Database Users

105

(1)

Roles

105

(6)

Fixed Server Roles

105

(2)

Database Roles

107

(1)

Application Roles

108

(3)

Schemas

111

(2)

Before You Can Proceed with Your Solution

113

(4)

Summary

117

(2)

Defining Tables

119

(34)

What Is a Table?

120

(1)

Defining a Table: SQL Server Management Studio

121

(5)

Different Table Data Types

121

(4)

Different Program Data Types

125

(1)

Columns Are More Than Simple Data Repositories

126

(1)

Default Values

126

(1)

Generating Identity Values

126

(1)

The Use of Null Values

127

(1)

Why Define a Column to Allow NULL?

127

(1)

Image and Large Text Storage in SQL Server

127

(1)

Creating a Table in SQL Server Management Studio

128

(6)

Defining a Table Through the Query Editor

134

(2)

Defining a Table: Using a Template

136

(3)

Creating and Altering a Template

139

(2)

The Alter Table Command

141

(1)

Defining the Remaining Tables

142

(1)

Setting a Primary Key

143

(1)

Creating a Relationship

144

(6)

Check Existing Data on Creation

148

(1)

Enforce Foreign Key Constraints

149

(1)

Delete Rule/Update Rule

149

(1)

Using the Alter Table SQL Statement

150

(1)

Summary

151

(2)

Creating Indexes and Database Diagramming

153

(32)

What Is an Index?

153

(3)

Types of Indexes

154

(2)

Uniqueness

156

(1)

Determining What Makes a Good Index

156

(3)

Using Low-Maintenance Columns

156

(1)

Primary and Foreign Keys

157

(1)

Finding Specific Records

157

(1)

Using Covering Indexes

157

(1)

Looking for a Range of Information

158

(1)

Keeping the Data in Order

158

(1)

Determining What Makes a Bad Index

159

(1)

Using Unsuitable Columns

159

(1)

Choosing Unsuitable Data

159

(1)

Including Too Many Columns

159

(1)

Including Too Few Records in the Table

159

(1)

Reviewing Your Indexes for Performance

160

(1)

Creating an Index

160

(13)

Creating an Index with the Table Designer

161

(2)

Indexes and Statistics

163

(1)

The Create Index Syntax

164

(2)

Creating an Index in Query Editor: Template

166

(4)

Creating an Index in Query Editor: SQL Code

170

(3)

Dropping an Index

173

(1)

Altering an Index in Query Editor

174

(2)

Diagramming the Database

176

(7)

Database Diagramming Basics

176

(1)

The SQL Server Database Diagram Tool

177

(1)

The Default Database Diagram

178

(2)

The Database Diagram Toolbar

180

(3)

Summary

183

(2)

Database Backups, Recovery, and Maintenance

185

(60)

Transaction Logs

186

(2)

Backup Strategies

188

(1)

When Problems May Occur

189

(1)

Taking a Database Offline

190

(1)

Backing the Data Up

191

(16)

Backing Up the Database Using T-SQL

196

(8)

Transaction Log Backup Using T-SQL

204

(3)

Restoring a Database

207

(7)

Restoring Using SQL Server Management Studio

207

(3)

Restoring Using T-SQL

210

(4)

Detaching and Attaching a Database

214

(8)

Detaching and Attaching Using SQL Server Management Studio

215

(5)

Detaching and Attaching Using T-SQL

220

(2)

Producing SQL Script for the Database

222

(8)

Maintaining Your Database

230

(1)

Creating a Database Maintenance Plan

230

(13)

Summary

243

(2)

Working with the Data

245

(64)

The T-SQL Insert Command Syntax

246

(1)

Insert SQL Command

247

(7)

Default Values

249

(1)

Using NULL Values

249

(5)

DBCC CHECKIDENT

254

(1)

Column Constraints

255

(7)

ADD CONSTRAINT

256

(6)

Dealing with Several Records at Once

262

(1)

Inserting Several Records in a Query Batch

262

(1)

Retrieving Data

263

(1)

Using SQL Server Management Studio to Retrieve Data

264

(2)

The Select Statement

266

(2)

Naming the Columns

268

(1)

The First Searches

269

(2)

Varying the Output Display

271

(2)

Limiting the Search: The Use of Where

273

(5)

SET ROWCOUNT n

276

(1)

TOP n

277

(1)

TOP n PERCENT

278

(1)

String Functions

278

(2)

Order! Order!

280

(2)

The LIKE Operator

282

(2)

Creating Data: SELECT INTO

284

(2)

Who Can Add, Delete, and Select Data

286

(5)

Securables

291

(1)

Updating Data

291

(5)

The Update Command

292

(1)

Updating Data Within Query Editor

293

(3)

Transactions

296

(7)

Begin Tran

298

(1)

Commit Tran

298

(1)

Rollback Tran

298

(1)

Locking Data

299

(1)

Updating Data: Using Transactions

299

(2)

Nested Transactions

301

(2)

Deleting Data

303

(3)

Delete Syntax

303

(1)

Using the Delete Statement

304

(2)

Truncating a Table

306

(1)

Dropping a Table

307

(1)

Summary

308

(1)

Building a View

309

(26)

What Is a View?

310

(1)

Using Views for Security

310

(1)

Encrypting View Definitions

311

(1)

Creating a View: SQL Server Management Studio

312

(6)

Creating a View Using a View

318

(6)

Create View Syntax

324

(2)

Creating a View: A Query Editor pane

326

(1)

Creating a View: Schemabinding

327

(3)

Indexing a View

330

(2)

Summary

332

(3)

Stored Procedures

335

(24)

What Is a Stored Procedure?

335

(1)

Create Procedure Syntax

336

(3)

Returning a Set of Records

339

(1)

Creating a Stored Procedure: Management Studio

339

(5)

Different Methods of Executing

344

(1)

No EXEC

344

(1)

With EXEC

344

(1)

Using Return

344

(4)

Controlling the Flow

348

(7)

If... Else

349

(1)

Begin...End

349

(1)

While...Break Statement

350

(2)

CASE Statement

352

(3)

Bringing It All Together

355

(2)

Summary

357

(2)

T-SQL Essentials

359

(44)

Using More Than One Table

359

(6)

Variables

365

(2)

Temporary Tables

367

(2)

Aggregations

369

(3)

Count/Count_Big

369

(1)

SUM

370

(1)

MAX/MIN

371

(1)

AVG

372

(1)

GROUP BY

372

(2)

HAVING

374

(1)

Distinct Values

375

(1)

Functions

376

(15)

Date and Time

376

(4)

String

380

(6)

System Functions

386

(5)

RAISERROR

391

(3)

Error Handling

394

(1)

@@Error

395

(1)

Try...Catch

396

(5)

Summary

401

(2)

Advanced T-SQL

403

(24)

Subqueries

403

(4)

405

(1)

EXISTS

406

(1)

The APPLY Operator

407

(2)

CROSS APPLY

407

(1)

OUTER APPLY

408

(1)

Common Table Expressions

409

(3)

Recursive CTE

410

(2)

Pivoting Data

412

(3)

PIVOT

413

(1)

UNPIVOT

414

(1)

Ranking Functions

415

(6)

ROW_NUMBER

416

(2)

RANK

418

(1)

DENSE_RANK

419

(1)

NTILE

420

(1)

Using the MAX Data Type

421

(5)

Image LOB

424

(2)

Summary

426

(1)

Triggers

427

(24)

What Is a Trigger?

427

(1)

The DML Trigger?

428

(1)

Create Trigger Syntax for DML triggers

429

(2)

Why Not Use a Constraint?

431

(1)

Deleted and Inserted Logical Tables

431

(1)

Creating a DML FOR Trigger

432

(4)

Checking Specific Columns

436

(8)

Using Update()

436

(5)

Using COLUMNS_UPDATED()

441

(3)

DDL Triggers

444

(3)

DDL_DATABASE_LEVEL_EVENTS

445

(2)

Dropping a DDL trigger

447

(1)

Eventdata()

447

(3)

Summary

450

(1)

SQL Server 2005 Reporting Services

451

(22)

What Is Reporting Services?

451

(1)

Reporting Services Architecture from 5000 Feet

452

(1)

Reporting Services Architecture: A Closer Look

453

(5)

The Application Layer

454

(2)

The Server Layer

456

(2)

The Data Layer

458

(1)

Building Your First Report Using Report Wizard

458

(4)

Building a Report from Scratch

462

(10)

Data-Related Elements

463

(1)

Report Items

463

(1)

Reports Structure

464

(8)

Summary

472

(1)

Appendix Glossary of Terms

473

(12)

Index

485

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

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Beginning SQL Server 2005 for Developers: From Novice To Professional

Summary

Table of Contents

Supplemental Materials