T-SQL Fundamentals 2008

This book walks you through your first steps in T-SQL (also known as Transact-SQL), which is the Microsoft SQL Server dialect of the standard ANSI-SQL language. You’ll learn the theory behind T-SQL querying and programming, how to develop T-SQL code to query and modify data, and get an overview of programmable objects.

Although this book is intended for beginners, it is not merely a step-by-step book. It goes beyond the syntactical elements of T-SQL and explains the logic behind the language and its elements.

Occasionally the book covers subjects that may be considered advanced for readers who are new to T-SQL; therefore, those sections are optional reading. If you already feel comfortable with the material discussed in the book up to that point, you may want to tackle the more advanced subjects; otherwise, feel free to skip those sections and return to them after you’ve gained more experience. The text will indicate when a section may be considered more advanced and is provided as optional reading.

Many aspects of SQL are unique to the language, and are very different from other programming languages. This book helps you adopt the right state of mind and gain a true understanding of the language elements. You learn how to think in terms of sets and follow good SQL programming practices.

The book is not version-specific; it does, however, cover language elements that were introduced in recent versions of SQL Server, including SQL Server 2008. When I discuss language elements that were introduced recently, I specify the version in which they were added.

To complement the learning experience, the book provides exercises that enable you to practice what you’ve learned. The book occasionally provides optional exercises that are more advanced. Those exercises are intended for readers who feel very comfortable with the material and want to challenge themselves with more difficult problems. The optional exercises for advanced readers are labeled as such.

Who This Book Is For

This book is intended for T-SQL programmers, DBAs, architects, analysts, and SQL Server power users who just started working with SQL Server and need to write queries and develop code using Transact-SQL.

What This Book Is About

The book starts with both a theoretical background to T-SQL querying and programming (Chapter 1) laying the foundations for the rest of the book, and also coverage of creating tables and defining data integrity. The book moves on to various aspects of querying and modifying data (Chapters 2 through 8), then to a discussion of concurrency and transactions (Chapter 9), and finally provides an overview of programmable objects (Chapter 10). The following section lists the chapter titles along with a short description:

Chapter 1, “Background to T-SQL Querying and Programming,” provides a theoretical background about SQL, set theory, and predicate logic; examines the relational model and more; describes SQL Server’s architecture; and explains how to create tables and define data integrity.

Chapter 2, “Single-Table Queries,” covers various aspects of querying a single table using the SELECT statement.

Chapter 3, “Joins,” covers querying multiple tables using joins, including cross joins, inner joins, and outer joins.

Chapter 4, “Subqueries,” covers queries within queries, otherwise known as subqueries.

Chapter 5, “Table Expressions,” covers derived tables, CTEs, views, inline table-valued functions, and the APPLY operator.

Chapter 6, “Set Operations,” covers the set operations UNION, INTERSECT, and EXCEPT.

Chapter 7, “Pivot, Unpivot, and Grouping Sets,” covers data-rotation techniques and working with grouping sets.

Chapter 8, “Data Modification,” covers inserting, updating, deleting, and merging data.

Chapter 9, “Transactions and Concurrency,” covers concurrency of users that work with the same data simultaneously; it covers concepts including transactions, locks, blocking, isolation levels, and deadlocks.

Chapter 10, “Overview of Programmable Objects,” provides an overview to the T-SQL programming capabilities in SQL Server.

The book also provides an appendix, “Getting Started,” to help you set up your environment, download the book’s source code, install the sample database TSQLFundamentals2008, start writing code against SQL Server, and learn how to get help by working with SQL Server Books Online.

Sincerely,
Itzik