text.skipToContent text.skipToNavigation
  1. Home
  2. Computer Science & IT
  3. T-SQL Querying

T-SQL Querying, 1st edition

  • Itzik Ben-Gan
  • Adam Machanic
  • Dejan Sarka
  • Kevin Farlee

Published by Microsoft Press (March 6th 2015) - Copyright © 2015

1st edition

Chosen format
View all
T-SQL Querying

ISBN-13: 9780133986648

Includes: Instant Access

This product is not available in your country

What's included

  • Instant Access

    You'll get instant access to the digital content.

Overview

Table of contents

Foreword     xv
Introduction     xvii
Chapter 1: Logical query processing     1

Logical query-processing phases     3
Logical query-processing phases in brief     4
Sample query based on customers/orders scenario     6
Logical query-processing phase details     8
Step 1: The FROM phase     8
Step 2: The WHERE phase     14
Step 3: The GROUP BY phase     15
Step 4: The HAVING phase     16
Step 5: The SELECT phase     17
Step 6: The ORDER BY phase     20
Step 7: Apply the TOP or OFFSET-FETCH filter     22
Further aspects of logical query processing     26
Table operators     26
Window functions     35
The UNION, EXCEPT, and INTERSECT operators     38
Conclusion     39
Chapter 2: Query tuning     41
Internals     41
Pages and extents     42
Table organization     43
Tools to measure query performance     53
Access methods     57
Table scan/unordered clustered index scan     57
Unordered covering nonclustered index scan     60
Ordered clustered index scan     62
Ordered covering nonclustered index scan     63
The storage engine’s treatment of scans     65
Nonclustered index seek + range scan + lookups     81
Unordered nonclustered index scan + lookups     91
Clustered index seek + range scan     93
Covering nonclustered index seek + range scan     94
Cardinality estimates     97
Legacy estimator vs. 2014 cardinality estimator     98
Implications of underestimations and overestimations     99
Statistics     101
Estimates for multiple predicates     104
Ascending key problem     107
Unknowns     110
Indexing features     115
Descending indexes     115
Included non-key columns     119
Filtered indexes and statistics     120
Columnstore indexes     123
Inline index definition     130
Prioritizing queries for tuning with extended events     131
Index and query information and statistics     134
Temporary objects     139
Set-based vs. iterative solutions     149
Query tuning with query revisions     153
Parallel query execution     158
How intraquery parallelism works     158
Parallelism and query optimization     175
The parallel APPLY query pattern     181
Conclusion     186
Chapter 3: Multi-table queries     187
Subqueries     187
Self-contained subqueries     187
Correlated subqueries     189
The EXISTS predicate     194
Misbehaving subqueries     201
Table expressions     204
Derived tables     205
CTEs     207
Views     211
Inline table-valued functions     215
Generating

For teachers

All the material you need to teach your courses.

Discover teaching material