Learn T-SQL querying : a guide to developing efficient and elegant T-SQL code
(eBook)
Author
Contributors
Lahoud, Pam, author.
Published
Birmingham, UK : Packt Publishing Ltd., 2024.
Format
eBook
Edition
Second edition.
Physical Desc
1 online resource (456 pages) : illustrations
Status
Description
Loading Description...
Also in this Series
Checking series information...
More Details
Language
English
Notes
Bibliography
Includes bibliographical references and index.
Description
Data professionals seeking to excel in Transact-SQL (T-SQL) for Microsoft SQL Server and Azure SQL Database often lack comprehensive resources. This updated second edition of Learn T-SQL Querying focuses on indexing queries and crafting elegant T-SQL code, catering to all data professionals seeking mastery in modern SQL Server versions and Azure SQL Database. Starting with query processing fundamentals, this book lays a solid foundation for writing performant T-SQL queries. You'll explore the mechanics of the Query Optimizer and Query Execution Plans, learning how to analyze execution plans for insights into current performance and scalability. Through dynamic management views (DMVs) and dynamic management functions (DMFs), you'll build diagnostic queries. This book thoroughly covers indexing for T-SQL performance and provides insights into SQL Server's built-in tools for expedited resolution of query performance and scalability issues. Further, hands-on examples will guide you through implementing features such as avoiding UDF pitfalls, understanding predicate SARGability, Query Store, and Query Tuning Assistant. By the end of this book, you'll have developed the ability to identify query performance bottlenecks, recognize anti-patterns, and skillfully avoid such pitfalls.
Citations
APA Citation, 7th Edition (style guide)
Lopes, P., & Lahoud, P. (2024). Learn T-SQL querying: a guide to developing efficient and elegant T-SQL code (Second edition.). Packt Publishing Ltd..
Chicago / Turabian - Author Date Citation, 17th Edition (style guide)Lopes, Pedro and Pam, Lahoud. 2024. Learn T-SQL Querying: A Guide to Developing Efficient and Elegant T-SQL Code. Packt Publishing Ltd.
Chicago / Turabian - Humanities (Notes and Bibliography) Citation, 17th Edition (style guide)Lopes, Pedro and Pam, Lahoud. Learn T-SQL Querying: A Guide to Developing Efficient and Elegant T-SQL Code Packt Publishing Ltd, 2024.
MLA Citation, 9th Edition (style guide)Lopes, Pedro,, and Pam Lahoud. Learn T-SQL Querying: A Guide to Developing Efficient and Elegant T-SQL Code Second edition., Packt Publishing Ltd., 2024.
Note! Citations contain only title, author, edition, publisher, and year published. Citations should be used as a guideline and should be double checked for accuracy. Citation formats are based on standards as of August 2021.
Staff View
Grouped Work ID
6cc8146b-03d8-96c4-9d01-4edcee22ee17-eng
Grouping Information
| Grouped Work ID | 6cc8146b-03d8-96c4-9d01-4edcee22ee17-eng |
|---|---|
| Full title | learn t sql querying a guide to developing efficient and elegant t sql code |
| Author | lopes pedro |
| Grouping Category | book |
| Last Update | 2024-10-01 16:51:55PM |
| Last Indexed | 2024-10-01 17:25:23PM |
Marc Record
| First Detected | Jul 29, 2024 04:09:02 PM |
|---|---|
| Last File Modification Time | Oct 01, 2024 05:25:23 PM |
MARC Record
| LEADER | 07617cam a22003977i 4500 | ||
|---|---|---|---|
| 001 | on1424938286 | ||
| 003 | OCoLC | ||
| 005 | 20240927105016.0 | ||
| 006 | m o d | ||
| 007 | cr cnu|||unuuu | ||
| 008 | 240305s2024 enka ob 001 0 eng d | ||
| 020 | |z 9781837638994 | ||
| 035 | |a (OCoLC)1424938286 | ||
| 037 | |a 9781837638994|b O'Reilly Media | ||
| 040 | |a ORMDA|b eng|e rda|e pn|c ORMDA|d OCLCO|d DXU | ||
| 049 | |a FMGA | ||
| 050 | 4 | |a QA76.9.C55 | |
| 082 | 0 | 4 | |a 005.75/85|2 23/eng/20240305 |
| 100 | 1 | |a Lopes, Pedro,|e author. | |
| 245 | 1 | 0 | |a Learn T-SQL querying :|b a guide to developing efficient and elegant T-SQL code /|c Pedro Lopes, Pam Lahoud. |
| 250 | |a Second edition. | ||
| 264 | 1 | |a Birmingham, UK :|b Packt Publishing Ltd.,|c 2024. | |
| 300 | |a 1 online resource (456 pages) :|b illustrations | ||
| 336 | |a text|b txt|2 rdacontent | ||
| 337 | |a computer|b c|2 rdamedia | ||
| 338 | |a online resource|b cr|2 rdacarrier | ||
| 504 | |a Includes bibliographical references and index. | ||
| 505 | 0 | |a Cover -- Title Page -- Copyright and Credits -- Foreword -- Contributors -- Table of Contents -- Preface -- Part 1: Query Processing Fundamentals -- Chapter 1: Understanding Query Processing -- Technical requirements -- Logical statement processing flow -- Query compilation essentials -- Query optimization essentials -- Query execution essentials -- Plan caching and reuse -- Stored procedures -- Ad hoc plan caching -- Parameterization -- The sp_executesql procedure -- Prepared statements -- How query processing impacts plan reuse -- The importance of parameters -- Security -- Performance -- Parameter sniffing -- To cache or not to cache -- Summary -- Chapter 2: Mechanics of the Query Optimizer -- Technical requirements -- Introducing the Cardinality Estimator -- Understanding the query optimization workflow -- The Trivial Plan stage -- The Exploration stage -- The Transaction Processing phase -- The Quick Plan phase -- The Full Optimization phase -- Knobs for query optimization -- Summary -- Part 2: Dos and Don'ts of T-SQL -- Chapter 3: Exploring Query Execution Plans -- Technical requirements -- What is a query plan? -- Accessing a query plan -- Navigating a query plan -- Query plan operators of interest -- Blocking versus non-blocking operators -- Data access operators -- Joins -- Spools -- Sort and aggregation operators -- Query plan properties of interest -- Plan-level properties -- Operator-level properties -- Summary -- Chapter 4: Indexing for T-SQL Performance -- Technical requirements -- Understanding predicate SARGability -- Data access using indexes -- Structure of a rowstore index -- Data access using rowstore indexes -- Inserting and updating data in a rowstore index -- Indexing strategy using rowstore indexes -- Best practices for clustered indexes -- Best practices for non-clustered indexes -- Index maintenance -- Summary. | |
| 505 | 8 | |a Chapter 5: Writing Elegant T-SQL Queries -- Technical requirements -- Best practices for T-SQL querying -- Referencing objects -- Joining tables -- Using NOLOCK -- Using cursors -- The perils of SELECT * -- Functions in our predicate -- Deconstructing table-valued functions -- Complex expressions -- Optimizing OR logic -- NULL means unknown -- Fuzzy string matching -- Inequality logic -- EXECUTE versus sp_executesql -- Composable logic -- Summary -- Chapter 6: Discovering T-SQL Anti- Patterns in Depth -- Technical requirements -- Implicit conversions -- Avoiding unnecessary sort operations -- UNION ALL versus UNION -- SELECT DISTINCT -- Avoiding UDF pitfalls -- Avoiding unnecessary overhead with stored procedures -- Pitfalls of complex views -- Pitfalls of correlated sub-queries -- Properly storing intermediate results -- Using table variables and temporary tables -- Using Common Table Expressions (CTEs) -- Summary -- Part 3: Assembling Our Query Troubleshooting Toolbox -- Chapter 7: Building Diagnostic Queries Using DMVs and DMFs -- Technical requirements -- Introducing DMVs -- Exploring query execution DMVs -- sys.dm_exec_sessions -- sys.dm_exec_requests -- sys.dm_exec_sql_text -- sys.dm_os_waiting_tasks -- Exploring query plan cache DMVs -- sys.dm_exec_query_stats -- sys.dm_exec_procedure_stats -- sys.dm_exec_query_plan -- sys.dm_exec_cached_plans -- Troubleshooting common scenarios with DMV queries -- Investigating blocking -- Cached query plan issues -- Single-use plans (query fingerprints) -- Finding resource-intensive queries -- Queries with excessive memory grants -- Mining XML query plans -- Plans with missing indexes -- Plans with warnings -- Plans with implicit conversions -- Plans with lookups -- Summary -- Chapter 8: Building XEvent Profiler Traces -- Technical requirements -- Introducing XEvents. | |
| 505 | 8 | |a Getting up and running with XEvent Profiler -- Remote collection with SQL LogScout -- Analyzing traces with RML Utilities -- Summary -- Chapter 9: Comparative Analysis of Query Plans -- Technical requirements -- Query plan analyzer -- Summary -- Chapter 10: Tracking Performance History with Query Store -- Technical requirements -- Introducing the Query Store -- Inner workings of the Query Store -- Configuring the Query Store -- Tracking expensive queries -- Fixing regressed queries -- Features that rely on the Query Store -- Query Store for readable secondary replicas -- Query Store hinting -- Parameter Sensitive Plan Optimization -- Automatic Plan Correction -- Degree of parallelism feedback -- Optimized plan forcing -- Summary -- Chapter 11: Troubleshooting Live Queries -- Technical requirements -- Using Live Query Statistics -- Understanding the need for lightweight profiling -- Diagnostics available with Lightweight Profiling -- Activity Monitor gets new life -- Summary -- Chapter 12: Managing Optimizer Changes -- Technical requirements -- Understanding where QTA and CE feedback are needed -- Understanding QTA fundamentals -- Exploring the QTA workflow -- Summary -- Index -- Other Books You May Enjoy. | |
| 520 | |a Data professionals seeking to excel in Transact-SQL (T-SQL) for Microsoft SQL Server and Azure SQL Database often lack comprehensive resources. This updated second edition of Learn T-SQL Querying focuses on indexing queries and crafting elegant T-SQL code, catering to all data professionals seeking mastery in modern SQL Server versions and Azure SQL Database. Starting with query processing fundamentals, this book lays a solid foundation for writing performant T-SQL queries. You'll explore the mechanics of the Query Optimizer and Query Execution Plans, learning how to analyze execution plans for insights into current performance and scalability. Through dynamic management views (DMVs) and dynamic management functions (DMFs), you'll build diagnostic queries. This book thoroughly covers indexing for T-SQL performance and provides insights into SQL Server's built-in tools for expedited resolution of query performance and scalability issues. Further, hands-on examples will guide you through implementing features such as avoiding UDF pitfalls, understanding predicate SARGability, Query Store, and Query Tuning Assistant. By the end of this book, you'll have developed the ability to identify query performance bottlenecks, recognize anti-patterns, and skillfully avoid such pitfalls. | ||
| 630 | 0 | 0 | |a SQL server.|0 http://id.loc.gov/authorities/names/n90684343 |
| 650 | 0 | |a SQL (Computer program language)|0 http://id.loc.gov/authorities/subjects/sh86006628 | |
| 650 | 0 | |a Client/server computing.|0 http://id.loc.gov/authorities/subjects/sh93000502 | |
| 700 | 1 | |a Lahoud, Pam,|e author. | |
| 856 | 4 | 0 | |u https://www.aclib.us/OReilly |