Beginning T-SQL : A Step-by-Step Approach

Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark.The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. Table of Contents About the Authors About the Technical Reviewer Acknowledgments Introduction Chapter 1: Getting Started Installing SQL Server Developer Edition Finding Help for SQL Server Download Sample Databases Installing Tools for SQL Server Using Azure Data Studio Summary Chapter 2: Exploring Database Concepts What Is SQL Server? Service vs. Application Database as Container Data Is Stored in Tables Data Types Normalization Understanding Indexes Database Schemas Summary Chapter 3: Writing Simple SELECT Queries Using the SELECT Statement Selecting a Literal Value Retrieving from a Table Generating a SELECT List Mixing Literals and Column Names IntelliSense Formatting T-SQL Code Can the Statement Be Written on One Line? Must Keywords Be Uppercase? Are Column and Table Names Case Sensitive? Is the Semicolon Important? Why Use Aliases? When to Use Single Quotes, Double Quotes, or Square Brackets? Filtering Data Adding a WHERE Clause Using WHERE Clauses with Alternate Operators Using BETWEEN Using BETWEEN with NOT Filtering on Date and Time Using WHERE Clauses with Two Predicates Using the IN Operator Working with NULL Sorting Data Thinking About Performance Taking Advantage of Indexes Viewing Execution Plans Summary Answers to the Exercises Solutions to Exercise 3-1: Using the SELECT Statement Solutions to Exercise 3-2: Filtering Data Solutions to Exercise 3-3: Using WHERE Clauses with Two Predicates Solutions to Exercise 3-4: Working with NULL Solutions to Exercise 3-5: Sorting Data Chapter 4: Using Built-in Functions and Expressions Expressions Using Operators Concatenating Strings Concatenating Strings and NULL CONCAT ISNULL and COALESCE Concatenating Other Data Types to Strings Using Mathematical Operators Using String Functions RTRIM, LTRIM, and TRIM LEFT and RIGHT LEN and DATALENGTH CHARINDEX SUBSTRING CHOOSE REVERSE UPPER and LOWER REPLACE STRING_SPLIT and STRING_AGG Nesting Functions Using Date and Time Functions GETDATE and SYSDATETIME DATEADD DATEDIFF DATENAME and DATEPART DAY, MONTH, and YEAR CONVERT FORMAT DATEFROMPARTS EOMONTH Using Mathematical Functions ABS POWER SQUARE and SQRT ROUND RAND Logical Functions and Expressions The CASE Expression Simple CASE Searched CASE Listing a Column as the Return Value IIF COALESCE Administrative Functions Using Functions in the WHERE and ORDER BY Clauses The TOP Keyword Thinking About Performance Summary Answers to the Exercises Solutions to Exercise 4-1: Expressions Using Operators Solutions to Exercise 4-2: Using Mathematical Operators Solutions to Exercise 4-3: Using Functions Solutions to Exercise 4-4: Using Date and Time Functions Solutions to Exercise 4-5: Using Mathematical Functions Solutions to Exercise 4-6: Using Logical and System Functions Solutions to Exercise 4-7: Using Functions in the WHERE and ORDER BY Clauses Chapter 5: Joining Tables Using INNER JOIN Joining Two Tables Avoiding an Incorrect Join Condition Joining on a Different Column Name Joining on More Than One Column Joining Three or More Tables Using OUTER JOIN Using LEFT OUTER JOIN Using RIGHT OUTER JOIN Using OUTER JOIN to Find Rows with No Match Adding a Table to the Right Side of a LEFT JOIN Adding a Table to the Main Table of a LEFT JOIN FULL OUTER JOIN CROSS JOIN Self-Joins Thinking About Performance Merge Join Nested Loop Hash Match Summary Answers to the Exercises Solutions to Exercise 5-1: Using INNER JOIN Solutions to Exercise 5-2: Using OUTER JOIN Chapter 6: Building on Subqueries, Common Table Expressions, and Unions Writing Subqueries Using a Subquery in an IN List Using a Subquery and NOT IN Using a Subquery Containing NULL with NOT IN Using EXISTS Using CROSS APPLY and OUTER APPLY Writing UNION Queries Using EXCEPT and INTERSECT Using Derived Tables and Common Table Expressions Using Derived Tables Using Common Table Expressions Using a Common Table Expression to Solve a Complicated Join Problem Thinking About Performance Summary Answers to the Exercises Solutions to Exercise 6-1: Using Subqueries Solutions to Exercise 6-2: Using Derived Tables and Common Table Expressions Chapter 7: Grouping and Summarizing Data Aggregate Functions The GROUP BY Clause Grouping on Columns Grouping on Expressions The ORDER BY Clause The WHERE Clause The HAVING Clause Order of Operations DISTINCT Keyword Using DISTINCT vs. GROUP BY DISTINCT Within an Aggregate Expression Aggregate Queries with More Than One Table Aggregate Functions and NULL Thinking About Performance Summary Answers to the Exercises Solutions to Exercise 7-1: Aggregate Functions Solutions to Exercise 7-2: The GROUP BY Clause Solutions to Exercise 7-3: The HAVING Clause Solutions to Exercise 7-4: DISTINCT Keyword Solutions to Exercise 7-5: Aggregate Queries with More Than One Table Chapter 8: Discovering Windowing Functions What Is a Windowing Function? Ranking Functions and the OVER Clause Defining the Window Using NTILE Dividing the Window into Partitions Summarizing Results with Window Aggregates Defining the Window with Framing Calculating Running Totals Understanding the Difference Between ROWS and RANGE Using Window Analytic Functions LAG and LEAD FIRST_VALUE and LAST_VALUE PERCENT_RANK and CUME_DIST PERCENTILE_CONT and PERCENTILE_DISC Applying Windowing Functions Removing Duplicates Solving an Islands Problem Thinking About Performance Indexing The Trouble with Window Aggregates Framing Summary Answers to the Exercises Solutions to Exercise 8-1: Ranking Functions Solutions to Exercise 8-2: Summarizing Results with Window Aggregates Solutions to Exercise 8-3: Understanding the Difference Between ROWS and RANGE Solutions to Exercise 8-4: Using Window Analytic Functions Chapter 9: Advanced WHERE Clauses Pattern Matching Using LIKE Restricting the Characters in Pattern Matches Searching for Wildcards Combining Wildcards Using PATINDEX Using SOME, ANY, and ALL Using WHERE Clauses with Three or More Predicates Using NOT with Parentheses Performing a Full-Text Search Using CONTAINS Using Multiple Terms with CONTAINS Searching Multiple Columns Using FREETEXT Using FREETEXTTABLE Thinking About Performance Summary Answers to the Exercises Solutions to Exercise 9-1: Using LIKE Solutions to Exercise 9-2: Using WHERE Clauses with Three or More Predicates Solutions to Exercise 9-3: Performing a Full-Text Search Chapter 10: Manipulating Data Inserting New Rows Adding One Row with Literal Values Avoiding Common Insert Errors Inserting Multiple Rows with One Statement Inserting Rows from Another Table Inserting Missing Rows Creating and Populating a Table in One Statement Inserting Rows into Tables with Default Column Values Inserting Rows into Tables with Automatically Populating Columns Deleting Rows Using DELETE Deleting from a Table in a JOIN Truncating Updating Existing Rows Using the UPDATE Statement Updating Data with Expressions and Columns Updating with a Join Thinking About Performance Database Cleanup Summary Answers to the Exercises Solutions to Exercise 10-1: Inserting New Rows Solutions to Exercise 10-2: Deleting Rows Solutions to Exercise 10-3: Updating Existing Rows Chapter 11: Managing Transactions ACID Properties Writing an Explicit Transaction Rolling Back a Transaction Using the XACT_ABORT Setting Error Handling Using TRY . . . CATCH Viewing Untrappable Errors Using RAISERROR Using TRY . . . CATCH with Transactions Using THROW Instead of RAISERROR Thinking About Performance Summary Answers to the Exercises Solutions to Exercise 11-1: Writing an Explicit Transaction Solutions to Exercise 11-2: Error Handling Chapter 12: Understanding T-SQL Programming Logic Variables Declaring and Initializing a Variable Using Expressions and Functions with Variables Using Variables in WHERE and HAVING Clauses The IF...ELSE Construct Using IF Using ELSE Using Multiple Conditions Nesting IF...ELSE Using IF with a Query WHILE Using a WHILE Loop Nesting WHILE Loops Exiting a Loop Early Using CONTINUE Temporary Tables and Table Variables Creating Local Temp Tables Creating Global Temp Tables Creating Table Variables Using a Temp Table or Table Variable Using a Temp Table or Table Variable Like an Array Using a Cursor Thinking About Performance Summary Answers to the Exercises Solutions to Exercise 12-1: Variables Solutions to Exercise 12-2: The IF...ELSE Construct Solutions to Exercise 12-3: WHILE Solutions to Exercise 12-4: Temporary Tables and Table Variables Chapter 13: Implementing Logic in the Database Tables Adding Check Constraints to a Table Adding UNIQUE Constraints Adding a Primary Key to a Table Creating Foreign Keys Creating Foreign Keys with Delete and Update Rules Defining Automatically Populated Columns Views Creating Views Avoiding Common Problems with Views Manipulating Data with Views User-Defined Functions Creating User-Defined Scalar Functions Using Table-Valued User-Defined Functions Stored Procedures Using Default Values with Parameters Using the OUTPUT Parameter Saving the Results of a Stored Procedure in a Table Using a Logic in Stored Procedures User-Defined Data Types Table Types Triggers Thinking About Performance Database Cleanup Summary Answers to the Exercises Solutions to Exercise 13-1: Tables Solutions to Exercise 13-2: Views Solutions to Exercise 13-3: User-Defined Functions Solutions to Exercise 13-4: Stored Procedures Chapter 14: Expanding on Data Type Concepts Large-Value String Data Types (MAX) Large-Value Binary Data Types Creating VARBINARY(MAX) Data Using FILESTREAM FileTables Enhanced Date and Time Using DATE, TIME, and DATETIME2 Using DATETIMEOFFSET HIERARCHYID Viewing HIERARCHYID Creating a Hierarchy Using Stored Procedures to Manage Hierarchical Data Spatial Data Types Using GEOMETRY Using GEOGRAPHY Viewing the Spatial Results Tab Circular Arcs Sparse Columns Graph Databases Thinking About Performance Summary Chapter 15: Working with XML and JSON The Parts of XML Converting XML Using OPENXML Retrieving Data as XML Using the FOR XML Clause FOR XML RAW FOR XML AUTO FOR XML EXPLICIT FOR XML PATH The XML Data Type XML Methods The QUERY Method The VALUE Method The EXIST Method The MODIFY Method The NODES Method JSON Data Returning JSON Data Inserting JSON Data Shredding JSON Data Summary Chapter 16: Writing Advanced Queries Advanced CTE Queries Alternate CTE Syntax Using Multiple CTEs Referencing a CTE Multiple Times Joining a CTE to Another CTE Writing a Recursive Query Data Manipulation with CTEs Isolating Aggregate Query Logic Correlated Subqueries in the SELECT List Using Derived Tables Common Table Expressions Using CROSS APPLY and OUTER APPLY The OUTPUT Clause Using OUTPUT to View Data Saving OUTPUT Data to a Table The MERGE Statement GROUPING SETS CUBE and ROLLUP Pivoted Queries Pivoting Data with CASE Using the PIVOT Function Using the UNPIVOT Function Temporal Tables Paging Summary Chapter 17: Where to Go Next? Online Resources Conferences User Groups Vendors Books Classes SQL Server Documentation Practice, Practice, and More Practice Teach Someone Else Appendix A: SQL Server for Linux and macOS Installing a SQL Server Instance in a Container Copy Sample Databases to the Container Restore Sample Databases Appendix B: Using SSMS Installing SQL Server Management Studio Launching SQL Server Management Studio Installing the Sample Databases Get Started with SSMS Summary Appendix C: SQL Notebooks Opening a SQL Notebook Adding Code to the Notebook Adding a Text Cell Organizing Your Notebooks Summary Index Get a performance-oriented introduction to the T-SQL language underlying the Microsoft SQL Server and Azure SQL database engines. This fourth edition is updated to include SQL Notebooks as well as up-to-date syntax and features for T-SQL on-premises and in the Azure cloud. Exercises and examples now include the WideWorldImporters database, the newest sample database from Microsoft for SQL Server. Also new in this edition is coverage of JSON from T-SQL, news about performance enhancements called Intelligent Query Processing, and an appendix on running SQL Server in a container on macOS or Linux. Beginning T-SQL starts you on the path to mastering T-SQL with an emphasis on best practices. Using the sound coding techniques taught in this book will lead to excellent performance in the queries that you write in your daily work. Important techniques such as windowing functions are covered to help you write fast-executing queries that solve real business problems.The book begins with an introduction to databases, normalization, and to setting up your learning environment. You will learn about the tools you need to use such as SQL Server Management Studio, Azure Data Studio, and SQL Notebooks. Each subsequent chapter teaches an aspect of T-SQL, building on the skills learned in previous chapters. Exercises in most chapters provide an opportunity for the hands-on practice that leads to true learning and distinguishes the competent professional. A stand-out feature in this book is that most chapters end with a Thinking About Performance section. These sections cover aspects of query performance relative to the content just presented, including the new Intelligent Query Processing features that make queries faster without changing code. They will help you avoid beginner mistakes by knowing about and thinking about performance from day 1. What You Will Learn Install a sandboxed SQL Server instance for learning Understand how relational databases are designed Create objects such as tables and stored procedures Query a SQL Server table Filter and order the results of a query Query and work with specialized data types such as XML and JSON Apply modern features such as window functions Choose correct techniques so that your queries perform well Who This Book Is For Anyone who wants to learn T-SQL from the beginning or improve their T-SQL skills; those who need T-SQL as an additional skill; and those who write queries such as application developers, database administrators, business intelligence developers, and data scientists. The book is also helpful for anyone who must retrieve data from a SQL Server database. Get a performance-oriented introduction to the T-SQL language underlying the Microsoft SQL Server and Azure SQL database engines. This fourth edition is updated to include SQL Notebooks as well as up-to-date syntax and features for T-SQL on-premises and in the Azure cloud. Exercises and examples now include the WideWorldImporters database, the newest sample database from Microsoft for SQL Server. Also new in this edition is coverage of JSON from T-SQL, news about performance enhancements called Intelligent Query Processing, and an appendix on running SQL Server in a container on macOS or Linux. Beginning T-SQL starts you on the path to mastering T-SQL with an emphasis on best practices. Using the sound coding techniques taught in this book will lead to excellent performance in the queries that you write in your daily work. Important techniques such as windowing functions are covered to help you write fast-executing queries that solve real business problems. The book begins with an introduction to databases, normalization, and to setting up your learning environment. You will learn about the tools you need to use such as SQL Server Management Studio, Azure Data Studio, and SQL Notebooks. Each subsequent chapter teaches an aspect of T-SQL, building on the skills learned in previous chapters. Exercises in most chapters provide an opportunity for the hands-on practice that leads to true learning and distinguishes the competent professional. A stand-out feature in this book is that most chapters end with a Thinking About Performance section. These sections cover aspects of query performance relative to the content just presented, including the new Intelligent Query Processing features that make queries faster without changing code. They will help you avoid beginner mistakes by knowing about and thinking about performance from day 1. What You Will Learn Install a sandboxed SQL Server instance for learning Understand how relational databases are designed Create objects such as tables and stored procedures Query a SQL Server table Filter and order the results of a query Query and work with specialized data types such as XML and JSON Apply modern features such as window functions Choose correct techniques so that your queries perform well Who This Book Is For Anyone who wants to learn T-SQL from the beginning or improve their T-SQL skills; those who need T-SQL as an additional skill; and those who write queries such as application developers, database a ..