Consider these sponsored items
- £34.21Was:£47.99Free postage
All listings for this product
About this product
- Author(s)Adam Machanic,Dejan Sarka,Itzik Ben-Gan,Kevin Farlee
- PublisherMicrosoft Press,U.S.
- Date of Publication06/03/2015
- GenreComputing: Professional & Programming
- Place of PublicationRedmond
- Country of PublicationUnited States
- First Published2015
- ImprintMicrosoft Press,U.S.
- Weight1394 g
- Width189 mm
- Height229 mm
- Spine43 mm
- Table Of ContentsForeword 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 numbers 215 The APPLY operator 218 The CROSS APPLY operator 219 The OUTER APPLY operator 221 Implicit APPLY 221 Reuse of column aliases 222 Joins 224 Cross join 224 Inner join 228 Outer join 229 Self join 230 Equi and non-equi joins 230 Multi-join queries 231 Semi and anti semi joins 237 Join algorithms 239 Separating elements 245 The UNION, EXCEPT, and INTERSECT operators 249 The UNION ALL and UNION operators 250 The INTERSECT operator 253 The EXCEPT operator 255 Conclusion 257 Chapter 4: Grouping, pivoting, and windowing 259 Window functions 259 Aggregate window functions 260 Ranking window functions 281 Offset window functions 285 Statistical window functions 288 Gaps and islands 291 Pivoting 299 One-to-one pivot 300 Many-to-one pivot 304 Unpivoting 307 Unpivoting with CROSS JOIN and VALUES 308 Unpivoting with CROSS APPLY and VALUES 310 Using the UNPIVOT operator 312 Custom aggregations 313 Using a cursor 314 Using pivoting 315 Specialized solutions 316 Grouping sets 327 GROUPING SETS subclause 328 CUBE and ROLLUP clauses 331 Grouping sets algebra 333 Materializing grouping sets 334 Sorting 337 Conclusion 339 Chapter 5: TOP and OFFSET-FETCH 341 The TOP and OFFSET-FETCH filters 341 The TOP filter 341 The OFFSET-FETCH filter 345 Optimization of filters demonstrated through paging 346 Optimization of TOP 346 Optimization of OFFSET-FETCH 354 Optimization of ROW_NUMBER 358 Using the TOP option with modifications 360 TOP with modifications 360 Modifying in chunks 361 Top N per group 363 Solution using ROW_NUMBER 364 Solution using TOP and APPLY 365 Solution using concatenation (a carr
- Author BiographyItzik Ben-Gan is a mentor for and co-founder of SolidQ. A SQL Server Microsoft MVP (Most Valuable Professional) since 1999, Itzik has delivered numerous training events around the world focused on T-SQL querying, query tuning, and programming. Itzik has authored several T-SQL books as well as articles for SQL Server Pro, SolidQ Journal, and MSDN. Itzik's speaking activities include TechEd, SQLPASS, SQL Server Connections, SolidQ events, and various user groups around the world. Itzik is the author of SolidQ's Advanced T-SQL Querying, Programming and Tuning, and T-SQL Fundamentals courses, along with being a primary resource within the company for its T-SQL-related activities. Dejan Sarka, MCT and SQL Server MVP, is an independent consultant, trainer, and developer focusing on database and business intelligence applications. His specialties are advanced topics like data modeling, data mining, and data quality. On these tough topics, he works and researches together with SolidQ and the Data Quality Institute. He is the founder of the Slovenian SQL Server and .NET Users Group. Dejan Sarka is the main author or coauthor of 11 books about databases and SQL Server, with more to come. He also has developed and is continuing to develop many courses and seminars for SolidQ and Microsoft. He has been a regular speaker at many conferences worldwide for more than 15 years, including Microsoft TechEd, PASS Summit, and others. Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to several books on SQL Server, including T-SQL Querying (Microsoft Press, 2015) and SQL Server 2008 Internals (Microsoft Press, 2009). He regularly speaks at international conferences and training events on a variety of SQL Server topics. Kevin Farlee has over 25 years in the industry, in both database and storage-management software. In his current role as a Storage Engine Program Manager on the Microsoft SQL Server team, he brings these threads together. His current projects include the SQL Server Project Hekaton In-Memory OLTP feature.
Best-selling in Adult Learning & University
Save on Adult Learning & University
- £15.08Trending at £15.66
- £17.29Trending at £28.12
- £23.56Trending at £40.17
- £11.91Trending at £20.08
- £15.17Trending at £24.10
- £8.63Trending at £16.54
- £17.28Trending at £22.32
This item doesn't belong on this page.
Thanks, we'll look into this.