Learn T-SQL Querying

A guide to developing efficient and elegant T-SQL code

Pedro Lopes, Pam Lahoud

• Publisher: Packt Publishing
• Year: 2019
• ISBN:
  9781789342970
• Topic: Informatica
• Subtopic: Elaborazione di dati

Troubleshoot query performance issues, identify anti-patterns in code, and write efficient T-SQL queries

Key Features

• Discover T-SQL functionalities and services that help you interact with relational databases
• Understand the roles, tasks, and responsibilities of a T-SQL developer
• Explore solutions for carrying out database querying tasks, database administration, and troubleshooting

Book Description

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL language used with Microsoft SQL Server and Azure SQL Database. This book will be a usefu to learning the art of writing efficient T-SQL code in modern SQL Server versions as well as the Azure SQL Database.The book will get you started with query processing fundamentals to help you write powerful, performant T-SQL queries. You will then focus on query execution plans and leverage them for troubleshooting. In later chapters, you will explain how to identify various T-SQL patterns and anti-patterns. This will help you analyze execution plans to gain insights into current performance, and determine whether or not a query is scalable. You will also build diagnostic queries using dynamic management views (DMVs) and dynamic management functions (DMFs) to address various challenges in T-SQL execution. Next, you will work with the built-in tools of SQL Server to shorten the time taken to address query performance and scalability issues. In the concluding chapters, this will guide you through implementing various features, such as Extended Events, Query Store, and Query Tuning Assistant, using hands-on examples.By the end of the book, you will have developed the skills to determine query performance bottlenecks, avoid pitfalls, and discover the anti-patterns in use.

What you will learn

• Use Query Store to understand and easily change query performance
• Recognize and eliminate bottlenecks that lead to slow performance
• Deploy quick fixes and long-term solutions to improve query performance
• Implement best practices to minimize performance risk using T-SQL
• Achieve optimal performance by ensuring careful query and index design
• Use the latest performance optimization features in SQL Server 2017 and SQL Server 2019
• Protect query performance during upgrades to newer versions of SQL Server

Who this book is for

This book is for database administrators, database developers, data analysts, data scientists, and T-SQL practitioners who want to get started with writing T-SQL code and troubleshooting query performance issues with the help of practical examples. Previous knowledge of T-SQL querying is not required to get started with this book.

]]>

Section 1: Query Processing Fundamentals

To understand how to write solid, performant T-SQL, users should know how SQL Server runs T-SQL syntax to deliver the intended result sets in a scalable fashion. This section introduces the reader to concepts that are used throughout the remaining sections of this book to explain most patterns and anti-patterns, as well as mitigation strategies.

The following chapters are included in this section:

• Chapter 1, Anatomy of a Query
• Chapter 2, Understanding Query Processing
• Chapter 3, Mechanics of the Query Optimizer

Anatomy of a Query

Transact-SQL, or T-SQL, as it has come to be commonly known, is the language that is used to communicate with Microsoft SQL Server. Any actions a user wishes to perform in a server, such as retrieving or modifying data in a database, creating objects, changing server configurations, and so on, are all done via a T-SQL command.

In this chapter, we will be introduced to the typical components of a T-SQL statement, including the logical order with which SQL Server processes a statement. This is essential for introducing the reader to why certain query writing patterns work best and to provide a fundamental reference for better understanding the other chapters.

There are four main groups of T-SQL statements that we can have in a Relational Database Management System (RDBMS) like SQL Server:

• Data Control Language statements, also known as DCL, are used to handle control access to a database or parts of the database. T-SQL commands such as GRANT and REVOKE are used to change permissions on objects (known as securables), or to add users to SQL Server.
• Transactional Control Language statements, also known as TCL, are used to control transactions in SQL Server with T-SQL commands such as BEGIN TRANSACTION, COMMIT TRANSACTION, or ROLLBACK.
• Data Definition Language statements, also known as DDL, are used to create, change, or delete the database and any objects contained within such as tables or indexes. Examples of DDL include CREATE, ALTER, CREATE OR ALTER, or DROP T-SQL commands.
• Data Manipulation Language statements, also known as DML, can be distilled into 4 logical operations on a database:
  • Retrieving data via the SELECT statement.
  • Updating and Inserting data, also known as UPSERTs, via the UPDATE and INSERT statements.
  • Deleting data via the DELETE statement.
  • There is also a MERGE statement. This is a conditional structure that combines UPDATEs, INSERTs and/or DELETEs into a single statement, which together with SELECTs, make up the fundamental DML operations available in SQL Server.

While all these types of statements must be parsed and validated by the Database Engine before execution, with very few exceptions only DML statements are optimized. This means that the way DML statements are constructed can have an impact on their resulting performance, so care must be taken to write them efficiently. For this reason, we will focus on DML statements throughout the course of this book.

In this chapter we will cover the following topics:

• Building blocks of a T-SQL statement
• Logical statement-processing flow

Building blocks of a T-SQL statement

When writing a T-SQL statement, the following three actions are required:

1. Express the intended operation, such as reading or changing data
2. Provide a target or source list of affected tables
3. Provide a condition that filters the affected records

The intended operation is determined by the presence of the following clauses:

• The SELECT clause lists columns or expressions that will be displayed in the result set
• The DELETE, INSERT, or UPDATE clauses state the target table or view for these logical operations

As for the affected tables and filters, they are determined by the following clauses:

• The FROM clause lists the source tables, views and/or sub-queries that contain the data to be queried
• The WHERE clause states one or more conditions that will serve to filter the result set to the desired rows

The preceding clauses determine which data will be manipulated. The formatting of the results can be further modified by adding any of the following parts:

• The ORDER BY clause defines the order in which the rows will be returned
• The GROUP BY clause aggregates rows together based on the criteria provided (typically combined with aggregate functions in the SELECT clause)
• The HAVING clause applies a predicate to the results (different than the WHERE clause, which applies a predicate to the source rows)

SELECT

The SELECT clause defines the columns and expressions that will be returned in the results and is the only element that is required to form a valid T-SQL data retrieval statement. Elements in the SELECT statement can be as simple as a single constant value, or as complex as a full T-SQL sub-query, but generally it is a comma-separated list of columns from tables and views in a database.

The following query will return a single row with a single column:

 SELECT 1;

In the following screenshot we can see the result:

The SELECT clause can also be used to format the results by providing column aliases or using expressions to modify the data. Aliases are created with the optional keyword AS, followed by the intended column name to be displayed in the result set:

SELECT Name AS ProductName, LEFT(ProductNumber, 2) AS ProductCode, ISNULL(color, 'No Color') AS Color [...]

Note that, in the results, any row that has a value for Color will display that value, whereas any row that has a null color will display No Color instead:

DISTINCT

DISTINCT specifies that repeated rows in the result set are collapsed into a single row.

SELECT DISTINCT Name AS ProductName, LEFT(ProductNumber, 2) AS ProductCode, ISNULL(color, 'No Color') AS Color [...]

TOP

The TOP clause specifies that from the applicable rows, the results set only produces a predetermined number of rows, set in percentage or absolute number.

SELECT TOP 25 Name AS ProductN...