Learn T-SQL Querying
A guide to developing efficient and elegant T-SQL code
Pedro Lopes, Pam Lahoud
- 484 pagine
- English
- ePUB (disponibile sull'app)
- Disponibile su iOS e Android
Learn T-SQL Querying
A guide to developing efficient and elegant T-SQL code
Pedro Lopes, Pam Lahoud
Informazioni sul libro
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.
]]>
Domande frequenti
Informazioni
Section 1: Query Processing Fundamentals
- Chapter 1, Anatomy of a Query
- Chapter 2, Understanding Query Processing
- Chapter 3, Mechanics of the Query Optimizer
Anatomy of a Query
- 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.
- Building blocks of a T-SQL statement
- Logical statement-processing flow
Building blocks of a T-SQL statement
- Express the intended operation, such as reading or changing data
- Provide a target or source list of affected tables
- Provide a condition that filters the affected records
- 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
- 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 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
SELECT 1;
SELECT Name AS ProductName, LEFT(ProductNumber, 2) AS ProductCode, ISNULL(color, 'No Color') AS Color [...]
DISTINCT
SELECT DISTINCT Name AS ProductName, LEFT(ProductNumber, 2) AS ProductCode, ISNULL(color, 'No Color') AS Color [...]
TOP
SELECT TOP 25 Name AS ProductN...