T-SQL Fundamentals Third Edition

Chapter 01: Background to T-SQL Querying and Programming

Chapter 02: Single-Table Queries

Chapter 03: Joins

Chapter 04: Subqueries

Chapter 05: Table Expressions

Chapter 06: Set Operations

Chapter 07: Beyond the Fundamentals of Querying

Chapter 08: Data Modification

Chapter 09: Temporal Tables

Chapter 10: Transactions and Concurrency

Chapter 11: Programmable Objects

Appendix: Getting Started

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 ISO and ANSI standards for SQL. You’ll learn the theory behind T-SQL querying and programming and how to develop T-SQL code to query and modify data, and you’ll get an overview of programmable objects. Although this book is intended for beginners, it is not merely a set of procedures for readers to follow. 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 might 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 relational terms 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 2016. When I discuss language elements that were introduced recently, I specify the version in which they were added. Besides being available in an on-premises flavor, SQL Server is also available as a cloud-based service called Microsoft Azure SQL Database, or in short, just SQL Database. The code samples in this book were tested against both on-premises SQL Server and SQL Database. The book’s companion content (available at http://tsql.solidq.com/books/tf3) provides information about compatibility issues between the flavors. 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 should read this book This book is intended for T-SQL developers, DBAs, BI practitioners, data scientists, report writers, analysts, architects, and SQL Server power users who just started working with SQL Server and need to write queries and develop code using Transact-SQL. Assumptions To get the most out of this book you should have working experience with Windows and with applications based on Windows. You should also be familiar with basic concepts concerning relational database management systems. This book might not be for you if… Not every book is aimed at every possible audience. This book covers fundamentals. It is mainly aimed at T-SQL practitioners with little or no experience. This book might not be for you if you’re an advanced T-SQL practitioner with many years of experience. With that said, several readers of the previous editions of this book have mentioned that—even though they already had years of experience—they still found the book useful for filling gaps in their knowledge. Organization of this book This book starts with both a theoretical background to T-SQL querying and programming in 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, in Chapters 2 through 9, then to a discussion of concurrency and transactions in Chapter 10, and finally provides an overview of programmable objects in Chapter 11. 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 of 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 by using the SELECT statement. Chapter 3, “Joins,” covers querying multiple tables by 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, Common Table Expressions (CTEs), views, inline table-valued functions, and the APPLY operator. Chapter 6, “Set Operators,” covers the set operators UNION, INTERSECT, and EXCEPT. Chapter 7, “Beyond the Fundamentals of Querying,” covers window functions, pivoting, unpivoting, and working with grouping sets. Chapter 8, “Temporal Tables,” covers system-versioned temporal tables. Chapter 9, “Data Modification,” covers inserting, updating, deleting, and merging data. Chapter 10, “Transactions and Concurrency,” covers concurrency of user connections that work with the same data simultaneously; it covers concepts including transactions, locks, blocking, isolation levels, and deadlocks. Chapter 11, “Programmable Objects,” provides an overview of 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 TSQLV4 sample database, start writing code against SQL Server, and learn how to get help by working with SQL Server Books Online. Cheers, Itzik

Chapter 01: Background to T-SQL Querying and Programming – Online

Chapter 01: Background to T-SQL Querying and Programming – Download

Copyright notice: you’re only authorized to download the source code if you own a legal copy of the book.

Source code for T-SQL Fundamentals

T-SQL Fundamentals – Submit Errata

Update regarding URLs referenced in the book:
Wherever you encounter a URL pointing to the website tsql.solidq.com, please replace solidq with lucient. For example, instead of URL https://tsql.solidq.com/booksdisplay/tf3/ use URL https://tsql.lucient.com/booksdisplay/tf3/