Introduction

Welcome to the world of Transact-Structured Query Language programming with SQL Server 2005 and 2008. Transact-SQL, or T-SQL, is Microsoft Corporation's powerful implementation of the ANSI standard SQL database query language, which was designed to retrieve, manipulate, and add data to relational database management systems (RDBMS).

You may already have a basic idea of what SQL is used for, but you may not have a good understanding of the concepts behind relational databases and the purpose of SQL. This book will help you build a solid foundation of understanding, beginning with core relational database concepts and continuing to reinforce those concepts with real-world T-SQL query applications.

If you are familiar with relational database concepts but are new to Microsoft SQL Server or the T-SQL language, this book will teach you the basics from the ground up. If you're familiar with earlier versions of SQL Server, it will get you up to speed on the newest features. And if you know SQL Server 2005, you'll learn about some exciting new capabilities in SQL Server 2008.

A popular online encyclopedia lists about 800 distinct programming languages in use today. These languages are used to develop different types of applications for different types of computer systems and specialized devices. Needless to say, we have a lot of software in our information-rich society. Programming languages rapidly evolve and come and go, but one of few constants in the industry is that most business applications read, store, and manipulate data—data stored in relational databases. If you use Microsoft SQL Server in any capacity, the need to learn and use T-SQL is inescapable. Amazing things are possible with just a few keystrokes of powerful SQL script.

Indeed, SQL is one of the few standard languages in the industry that doesn't come and go and has remained constant over the decades. The capabilities of T-SQL expand as features are added to each version of the SQL Server product. The concepts and exercises in this book will help you to understand and use the core language and its latest features.

Who This Book Is For

Information Technology professionals in many different roles use T-SQL. Our goal is to provide a guide and a reference for IT pros across the spectrum of operational database solution design, database application development, and reporting and business intelligence solutions.

Database solution designers will find this book to be a thorough introduction and comprehensive reference for all aspects of database modeling, design, object management, query design, and advanced query concepts.

Application developers who write code to manage and consume SQL Server data will benefit from our thorough coverage of basic data management and simple and advanced query design. Several examples of ready-to-use code are provided to get you started and to continue to support applications with embedded T-SQL queries.

Report designers will find this book to be a go-to reference for report query design. You will build on a thorough introduction to basic query concepts and learn to write efficient queries to support business reports and advanced analytics.

Finally, database administrators who are new to SQL Server will find this book to be an all-inclusive introduction and reference of mainstream topics. This can assist you as you support the efforts of other team members. Beyond the basics of database object management and security concepts, we recommend Beginning SQL Server 2005 Administration and Beginning SQL Server 2008 Administration from Wrox, co-authored in part by the same authors.

What This Book Covers

This book introduces the T-SQL language and its many uses, and serves as a comprehensive guide at a beginner through intermediate level. Our goal in writing this book was to cover all the basics thoroughly and to cover the most common applications of T-SQL at a deeper level. Depending on your role and skill level, this book will serve as a companion to the other Wrox books in the Microsoft SQL Server Beginning and Professional series. Check the back cover of this book for a road map of other complementary books in the Wrox series.

This book will help you to learn:

• How T-SQL provides you with the means to create tools for managing databases of different size, scope, and purpose

• Various programming techniques that use views, user-defined functions, and stored procedures

• Ways to optimize query performance

• How to create databases that will be an essential foundation to applications you develop later

How This Book Is Structured

Each section of this book organizes topics into logical groups so the book can be read cover-to-cover or used as a reference guide for specific topics.

We start with an introduction to the T-SQL language and data management systems, and then continue with the SQL Server product fundamentals. This first section teaches the essentials of the SQL Server product architecture and relational database design principles. This section (Chapters 1-3) concludes with an introduction to the SQL Server administrator and developer tools.

The next section, encompassing Chapters 4 through 9, introduces the T-SQL language and teaches the core components of data retrieval, SQL functions, aggregation and grouping, and multi-table queries. We start with the basics and build on the core structure of the SQL SELECT statement, progressing to advanced forms of SELECT queries.

Chapter 10 introduces transactions and data manipulation. You will learn how the INSERT, UPDATE, and DELETE statements interact with the relational database engine and transaction log to lock and modify data rows with guaranteed consistency. You will not only learn to use correct SQL syntax but will understand how this process works in simple terms.

More advanced topics in the concluding section will teach you to create and manage T-SQL programming objects, including views, functions, and stored procedures. You learn to optimize query performance and use T-SQL in application design, applying the query design basics to real-world business solutions. Chapter 15 contains a complete tutorial on using SQL Server 2008 Reporting Services to visualize data from the T-SQL queries you create.

The book concludes with a comprehensive set of reference appendixes for command syntax, system stored procedures, information schema views, file system commands, and system management commands.

What You Need to Use This Book

The material in this book applies to all editions of Microsoft SQL Server 2005 and 2008. To use all the features discussed, we recommend that you install the Developer Edition, although you can also use the Enterprise, Standard, or Workgroup editions.

SQL Server 2005 Developer Edition or SQL Server 2008 Developer Edition can be installed on a desktop computer running Windows 2000, Windows XP, or Windows Vista. You can also use Windows 2000 Server, Windows Server 2003, or Windows Server 2008 with the Enterprise or Standard edition. The SQL Server client tools must be installed on your desktop computer and the SQL Server relational database server must be installed on either your desktop computer or on a remote server with network connectivity and permission to access.

Consult www.microsoft.com/sql for information about the latest service packs, specific compatibilities, and minimum recommend system requirements.

The examples throughout this book use the following sample databases, which are available to download from Microsoft: the sample database for SQL Server 2005 is called AdventureWorks, and the sample database for SQL Server 2008 is called AdventureWorks2008. Because the structure of these databases differs significantly, separate code samples are provided throughout the book for these two version-specific databases

An example using the AdventureWorks2008DW database for SQL Server 2008 is also used in Chapter 15.

To download and install these sample databases, browse www.codeplex.com.

Conventions

To help you get the most from the text and keep track of what's happening, we've used a number of conventions throughout the book.

Try It Out The Try It Out is an exercise you should work through, following the text in the book. They usually consist of a set of steps. Each step has a number. Follow the steps through with your copy of the database.

Notes, tips, hints, tricks, and asides to the current discussion are offset and placed in italics like this.

As for styles in the text:

• We highlight new terms and important words when we introduce them.

• We show keyboard strokes like this: Ctrl+A.

• We show filenames, URLs, and code within the text like so: persistence.properties

• We present code in two different ways:

  Code View: Scroll / Show All

  We use a monofont type with no highlighting for most code examples.
  We use gray highlighting to emphasize code that's particularly important in the present context.
  
  					  

  
  

Source Code

As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All the source code used in this book is available for download at www.wrox.com. Once at the site, simply locate the book's title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book's detail page to obtain all the source code for the book.

Because many books have similar titles, you may find it easiest to search by ISBN; this book's ISBN is 978-0-470-25703-6.

Once you download the code, just decompress it with your favorite compression tool. Alternatively, you can go to the main Wrox code download page at www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

Errata

We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, like a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata you may save another reader from hours of frustration and at the same time you will be helping us provide even higher quality information.

To find the errata page for this book, go to www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list including links to each book's errata is also available at www.wrox.com/misc-pages/booklist.shtml.

If you don't spot "your" error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We'll check the information and, if appropriate, post a message to the book's errata page and fix the problem in subsequent editions of the book.

p2p.wrox.com

For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a Web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At http://p2p.wrox.com you will find a number of different forums that will help you not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:

You can read messages in the forums without joining P2P but in order to post your own messages, you must join.

Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the Web. If you would like to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.