T-SQL Fundamentals, Fourth Edition

Query and modify data effectively with the latest T-SQL features Master Transact-SQL's fundamentals, and write correct, robust code for querying and modifying data with modern Microsoft data technologies, including SQL Server 2022, Azure SQL Database, and Azure SQL Managed Instance. Long-time Microsoft Data Platform MVP Itzik Ben-Gan explains key T-SQL concepts, helping you apply your knowledge with hands-on exercises. Ben-Gan first introduces T-SQL’s theory and underlying logic, illuminating it as both a language and a way of thinking. Next, he walks through core topics, including logical query processing, single table queries, joins, subqueries, table expressions, set operators, data analysis, data modifications, temporal tables, and transactions and concurrency. Building on this foundation, you'll enhance your coding capabilities, from programmatic constructs to the powerful new SQL Graph. Throughout, Ben-Gan presents reusable T-SQL sample code that works in cloud, on-premises, and hybrid environments. Microsoft Data Platform MVP Itzik Ben-Gan helps you: Understand why T-SQL works as it does, so you can write better code Review relational theory elements and modern SQL Server architecture Create tables and defi ne data integrity Build single-table SELECT queries, multiple-table joins, and subqueries Utilize derived tables, Common Table Expressions, views, inline table-valued functions, and APPLY Make the most of UNION, INTERSECT, and EXCEPT set operators Perform data analysis with window functions, pivoting, grouping sets, and time series Insert, update, delete, and merge data Get started with system-versioned temporal tables Understand and apply transactions and concurrency, including concurrent user connections, locks, blocking, isolation levels, and more Use SQL Graph to model, create, modify, and query graph-based data Explore SQL Server programmable objects and programming capabilities Set up your environment, install sample databases, and fi nd even more help For IT Professionals and Consultants For T-SQL developers, DBAs, BI practitioners, data scientists, report writers, analysts, architects, and SQL Server power users For anyone else who needs to write queries or develop T-SQL code for SQL Server, Azure SQL Database, or Azure SQL Managed Instance Cover Title Page Copyright Page Contents at a Glance Contents Acknowledgments About the Author Introduction Chapter 1: Background to T-SQL querying and programming Theoretical background SQL Set theory Predicate logic The relational model Types of database workloads SQL Server architecture On-premises and cloud RDBMS flavors SQL Server instances Databases Schemas and objects Creating tables and defining data integrity Creating tables Defining data integrity Conclusion Chapter 2: Single-table queries Elements of the SELECT statement The FROM clause The WHERE clause The GROUP BY clause The HAVING clause The SELECT clause The ORDER BY clause The TOP and OFFSET-FETCH filters A quick look at window functions Predicates and operators CASE expressions NULLs The GREATEST and LEAST functions All-at-once operations Woring with character data Data types Collation Operators and functions The LIKE predicate Woring with date and time data Date and time data types Literals Woring with date and time separately Filtering date ranges Date and time functions Querying metadata Catalog views Information schema views System stored procedures and functions Conclusion Exercises Exercise 1 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Exercise 7 Exercise 8 Exercise 9 Exercise 10 Solutions Exercise 1 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Exercise 7 Exercise 8 Exercise 9 Exercise 10 Chapter 3: Joins Cross joins SQL-92 syntax SQL-89 syntax Self cross joins Producing tables of numbers Inner joins SQL-92 syntax SQL-89 syntax Inner join safety More join examples Composite joins Non-equi joins Multi-join queries Outer joins Outer joins, described Including missing values Filtering attributes from the nonpreserved side of an outer join Using outer joins in a multi-join query Using the COUNT aggregate with outer joins Conclusion Exercises Exercise 1-1 Exercise 1-2 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Exercise 7 Exercise 8 Exercise 9 Solutions Exercise 1-1 Exercise 1-2 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Exercise 7 Exercise 8 Exercise 9 Chapter 4: Subqueries Self-contained subqueries Self-contained scalar subquery examples Self-contained multivalued subquery examples Correlated subqueries The EXISTS predicate Returning previous or next values Using running aggregates Dealing with misbehaving subqueries NULL trouble Substitution errors in subquery column names Conclusion Exercises Exercise 1 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Exercise 7 Exercise 8 Exercise 9 Exercise 10 Solutions Exercise 1 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Exercise 7 Exercise 8 Exercise 9 Exercise 10 Chapter 5: Table expressions Derived tables Assigning column aliases Using arguments Nesting Multiple references Common table expressions Assigning column aliases in CTEs Using arguments in CTEs Defining multiple TEs Multiple references in CTEs Recursive CTEs Views Views and the ORDER BY clause View options Inline table-valued functions The APPLY operator Conclusion Exercises Exercise 1 Exercise 2-1 Exercise 2-2 Exercise 3-1 Exercise 3-2 Exercise 4 Exercise 5-1 Exercise 5-2 Exercise 6-1 Exercise 6-2 Solutions Exercise 1 Exercise 2-1 Exercise 2-2 Exercise 3-1 Exercise 3-2 Exercise 4 Exercise 5-1 Exercise 5-2 Exercise 6-1 Exercise 6-2 Chapter 6: Set operators The UNION operator The UNION ALL operator The UNION (DISTINCT) operator The INTERSECT operator The INTERSECT (DISTINCT) operator The INTERSECT ALL operator The EXCEPT operator The EXCEPT (DISTINCT) operator The EXCEPT ALL operator Precedence Circumventing unsupported logical phases Conclusion Exercises Exercise 1 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Solutions Exercise 1 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Chapter 7: T-SQL for data analysis Window functions Ranking window functions Offset window functions Aggregate window functions The WINDOW clause Pivoting data Pivoting with a grouped query Pivoting with the PIVOT operator Unpivoting data Unpivoting with the APPLY operator Unpivoting with the UNPIVOT operator Grouping sets The GROUPING SETS subclause The CUBE subclause The ROLLUP subclause The GROUPING and GROUPING_ID functions Time series Sample data The DATE_BUCKET function Custom computation of start of containing bucket Applying bucket logic to sample data Gap filling Conclusion Exercises Exercise 1 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Exercise 7 Exercise 8 Solutions Exercise 1 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Exercise 7 Exercise 8 Chapter 8: Data modification Inserting data The INSERT VALUES statement The INSERT SELECT statement The INSERT EXEC statement The SELECT INTO statement The BULK INSERT statement The identity property and the sequence object Deleting data The DELETE statement The TRUNCATE statement DELETE based on a join Updating data The UPDATE statement UPDATE based on a join Assignment UPDATE Merging data Modifying data through table expressions Modifications with TOP and OFFSET-FETCH The OUTPUT clause INSERT with OUTPUT DELETE with OUTPUT UPDATE with OUTPUT MERGE with OUTPUT Nested DML Conclusion Exercises Exercise 1 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Solutions Exercise 1 Exercise 2 Exercise 3 Exercise 4 Exercise 5 Exercise 6 Chapter 9: Temporal tables Creating tables Modifying data Querying data Conclusion Exercises Exercise 1 Exercise 2 Exercise 3 Exercise 4 Solutions Exercise 1 Exercise 2 Exercise 3 Exercise 4 Chapter 10: Transactions and concurrency Transactions Locks and blocking Locks Troubleshooting blocking Isolation levels The READ UNCOMMITTED isolation level The READ COMMITTED isolation level The REPEATABLE READ isolation level The SERIALIZABLE isolation level Isolation levels based on row versioning Summary of isolation levels Deadlocks Conclusion Exercises Exercise 1 Exercise 2 Exercise 3 Chapter 11: SQL Graph Creating tables Traditional modeling Graph modeling Querying data Using the MATCH clause Recursive queries Using the SHORTEST_PATH option SQL Graph querying features that are still missing Data modification considerations Deleting and updating data Merging data Conclusion Exercises Exercise 1 Exercise 2 Exercise 3 Exercise 4 Solutions Exercise 1 Exercise 2 Exercise 3 Exercise 4 Cleanup Chapter 12: Programmable objects Variables Batches A batch as a unit of parsing Batches and variables Statements that cannot be combined in the same batch A batch as a unit of resolution The GO n option Flow elements The IF . . . ELSE flow element The WHILE flow element Cursors Temporary tables Local temporary tables Global temporary tables Table variables Table types Dynamic SQL The EXEC command The sp_executesql stored procedure Using PIVOT with Dynamic SQL Routines User-defined functions Stored procedures Triggers Error handling Conclusion Appendix: Getting started Index A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Starting with the background to T-SQL querying and programming, including: logical query processing, book-querying constructs (single table queries, joins, subqueries, table expressions, set operators, data analysis), data modifications, temporal tables, transactions and concurrency, SQL Graph (completely new to this edition), as well as programmatic T-SQL constructs. The book includes extensive exercises and solutions with explanations, allowing the reader to practice what they've learned. This book is widely considered as the authoritative guide on T-SQL fundamentals. It focuses on understanding why things work the way they do, and not just how to make them work. When people understand the "why" the code they write tends to be more correct and more meaningful. This edition of the book includes coverage of the newest T-SQL additions up to and including SQL Server 2022.