Introduction

Welcome to the SQL Server 2005 Bible. SQL Server is an incredible database product. It offers an excellent mix of performance, reliability, ease of administration, and new architectural options, yet enables the developer or DBA to control minute details when desired. SQL Server is a dream system for a database developer. If there's a theme to SQL Server 2005, it's new architectural options—SQL Server 2005 opens several new possibilities for designing more scalable and powerful systems. The first goal of this book is to share with you the pleasure of working with SQL Server.

Like all books in the Bible series, you can expect to find both hands-on tutorials and real-world practical applications, as well as reference and background information that provides a context for what you are learning. However, to cover every minute detail of every command of this very complex product would consume thousands of pages, so it is the second goal of this book to provide a concise yet comprehensive guide to SQL Server 2005 based on the information I have found most useful in my experience as a database developer, consultant, and instructor. By the time you have completed the SQL Server 2005 Bible, you will be well prepared to develop and manage your SQL Server 2005 database.

Some of you are repeat readers of mine (thanks!) and are familiar with my approach from the SQL Server 2000 Bible. Even though you might be familiar with my approach and my tone, you will find several new features in this edition, including the following:

• Coverage of the new architectural options, such as Service Broker, Web Services, XQuery, and Reporting Services

• More detail on the BI features

• Ten chapters on selecting data with queries

A wise database developer once showed a box to an apprentice and asked, "How many sides do you see?" The apprentice replied, "There are six sides to the box." The experienced database developer then said, "Users may see six sides, but database developers see only two sides: the inside and the outside. To the database developer, the cool code goes inside the box." This book is about thinking inside the box.

Who Should Read This Book

Whether you are a database developer or a database administrator, whether you are just starting out, have one year of experience or five, this book contains material that will be useful to you. This book is written so that you can learn from my experience and the experience of the co-authors. Each chapter begins with the assumption that you've never seen the topic before, and then progresses through the subject, presenting the information that makes a difference.

The next section describes how the book is divided into different parts. After you read the summaries, you will best know where to start. No matter how you approach it, be sure to read Chapter 1, "The Information Architecture Principle." That chapter describes two key principles upon which the rest of the book is based. Any other chapters you read will be clearer and make more sense once you have internalized both the Information Architecture Principle and optimization theory.

How This Book Is Organized

One of the differences repeat readers will note from prior editions is a slightly different organization. I really got to pull apart the text and draw more or less attention to certain areas of the book as the content and the changes to SQL Server 2005 dictated. Some chapters are only 10 pages long, while others are 30 or more. It was a challenge for me to stop where I did at nearly 1,400 pages! In any event, the book is organized into the following parts and chapters.

Each part is designed to be read sequentially and most chapters build on the previous chapters, but many readers have told me they use the book as a reference. Either way, I hope you find the book useful in your career.

Part I: Laying the Foundation

If SQL Server is the box, and developing is thinking inside the box, then the first part of this book is an introduction to the box. Beginning with the Information Architecture Principle and optimization theory, Part I presents six key topics that lay the foundation for your success with SQL Server:

• Information Architecture Principle

• Relational database modeling

• SQL Server architecture

• Installation

• Client software connectivity

• Management Studio

This isn't the obligatory "introduction to normalization" view of database design. Part I is a current, comprehensive, principle-driven view of information architecture and an introduction to the technologies of SQL Server.

Part II: Manipulating Data with Select

If SQL Server is the box, then Part II is about being one with the box. It begins by exploring the basic logical query flow, and quickly digs deeper into topics such as relational division, correlated subqueries, set-difference queries, and distributed queries. I've devoted ten chapters to the select command because understanding the multiple options and creative techniques available with queries is key to becoming a successful SQL Server developer, DBA, or architect.

Please don't assume that Part II is only for beginners. These ten chapters present the core power of SQL. Part VI explores optimization, and it's tempting to go there for optimization ideas. However, the second strategy of optimization theory is using good set-based code, so here are ten chapters on how to optimize your database by writing better queries.

Part III: Developing with SQL Server

If SQL Server is the box, then Part III is all about thinking inside the box, and moving the processing as close to the data as possible. Part III opens with DDL commands (create, alter, and drop), and progresses through nine chapters that dig deeper into Transact-SQL. The conclusion of the T-SQL chapters brings it all together with the data abstraction layer.

SQL Server's list of technologies keeps growing, so the remainder of Part III explores the .NET common language runtime (CLR), SQL Server Mobile, Service Broker, XML, XQuery, Web Services, and InfoPath. Go ahead, unleash the programmer within and have fun. There's a whole world of developer possibilities with SQL Server 2005.

Part IV: Enterprise Data Management

If SQL Server is the box, then Part IV is about keeping the box running smoothly, day after day, because this part is all about the enterprise DBA role.

While SQL Server is more automated than ever before, and Microsoft sometimes makes the error of presenting SQL Server as the database that doesn't require a DBA, the truth is that it takes diligent work to keep a production database up 24 hours a day, 7 days a week, 365 days a year.

Part V: Business Intelligence

If SQL Server is the box, then Part V is about coaxing every secret out of the box. The whole process of analyzing historical and current data both today and in the future is the proactive side of IT, collectively called business intelligence (BI).

In the past three releases of SQL Server, Microsoft has been steadily growing SQL Server's BI services, and SQL Server 2005 brings to fruition years of planning and development. From the enterprise-grade ETL tool to the rich and easy to build cubes, to the slick reporting interface, SQL Server 2005 is more than ready to help you conquer your BI requirements. The chapters in this part show you how.

Part VI: Optimization Strategies

If SQL Server is the box, then Part VI is about making the box fly. This book opens with the Information Architecture Principle, which states that information must be made readily available. Optimization theory explains the dependencies between various optimization techniques. This final part puts optimization theory into action with practical optimization strategies.

Part VII: Appendixes

The appendixes provide some lookup information that applies to nearly every chapter: SQL Server specifications and details of the book's sample databases.

How to Use This Book

You should understand some important conventions and icons before embarking on your SQL Server 2005 Bible journey.

Conventions and Features

There are many different organizational and typographical features throughout this book designed to help you get the most of the information:

• New terms are italicized as they are defined in the text.

• When code is referenced within the text, the code words are set in monospace type. Sometimes, those same SQL keywords are used as concepts (for example: inner join is used both as SQL code and in referring to the concept of a type of join).

• Some of the code samples are long. To draw attention to the main point of the code, important keywords in code are highlighted in bold.

• I'm not fond of page-length query results, so if a result set is particularly long, it is abbreviated with an ellipsis in the listing.

Icons

Whenever the authors want to bring something important to your attention, the information appears in a special type treatment called an icon. This book utilizes quite a few icons: Note, Tip, Caution, Cross-Reference, On the Web, New in 2005, Best Practice, and .sys.

Cautions provide information about things to watch out for, from the simply inconvenient to the potentially hazardous, regarding your data or systems.

Tips generally are used to provide information that can make your work easier—special shortcuts or methods for doing something easier than the norm.

What's on the Companion Website

On the publisher-sponsored companion website (www.wiley.com), you will find the following:

• Sample code—Each chapter has it's own subfolder on the website, where you'll find all the code output that was discussed in each chapter, organized accordingly.

• Errata

On the author-sponsored website (www.SQLServerBible.com) you'll find the preceding and more:

• Sample code and sample databases

• Errata, updates, and additional articles

• SQL Server–related resources and links

• Task-specific ScreenCasts

• PowerPoint and code from conference and PASS Chapter presentations

• Information on workshops

Where to Go from Here

As you begin reading the book, I have three suggestions for you:

Now, let me introduce you to the Information Architecture Principle, which sets the stage for why we do what we do with our data.