product iamge

T-SQL Fundamentals

This document was uploaded by one of our users. The uploader already confirmed that they had the permission to publish it. If you are author/publisher or own the copyright of this documents, please report to us by using this DMCA report form.

Download
Search on Amazon

Simply click on the Download Book button.

Yes, Book downloads on Ebookily are 100% Free.

Sometimes the book is free on Amazon As well, so go ahead and hit "Search on Amazon"

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

Author(s): Itzik Ben-Gan
Series: Developer Reference
Edition: 4
Publisher: Microsoft Press
Year: 2023

Language: English
Commentary: True PDF
Pages: 608

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