T-SQL Querying

T-SQL Querying

4.46 (13 ratings by Goodreads)
By (author)  , By (author)  , By (author)  , By (author) 

Free delivery worldwide

Available. Dispatched from the UK in 2 business days
When will my order arrive?


T-SQL insiders help you tackle your toughest queries and query-tuning problemsSqueeze maximum performance and efficiency from every T-SQL query you write or tune. Four leading experts take an in-depth look at T-SQL's internal architecture and offer advanced practical techniques for optimizing response time and resource usage. Emphasizing a correct understanding of the language and its foundations, the authors present unique solutions they have spent years developing and refining. All code and techniques are fully updated to reflect new T-SQL enhancements in Microsoft SQL Server 2014 and SQL Server 2012.Write faster, more efficient T-SQL code:Move from procedural programming to the language of sets and logicMaster an efficient top-down tuning methodologyAssess algorithmic complexity to predict performanceCompare data aggregation techniques, including new grouping setsEfficiently perform data-analysis calculationsMake the most of T-SQL's optimized bulk import toolsAvoid date/time pitfalls that lead to buggy, poorly performing codeCreate optimized BI statistical queries without additional softwareUse programmable objects to accelerate queriesUnlock major performance improvements with In-Memory OLTPMaster useful and elegant approaches to manipulating graphsAbout This BookFor experienced T-SQL practitionersIncludes coverage updated from Inside Microsoft SQL Server 2008 T-SQL Querying and Inside Microsoft SQL Server 2008 T-SQL ProgrammingValuable to developers, DBAs, BI professionals, and data scientistsCovers many MCSE 70-464 and MCSA/MCSE 70-461 exam topicsshow more

Product details

  • Paperback | 864 pages
  • 185.42 x 226.06 x 45.72mm | 1,428.81g
  • Microsoft Press,U.S.
  • Redmond, United States
  • English
  • 3rd ed.
  • 0735685045
  • 9780735685048
  • 104,409

About Itzik Ben-Gan

Itzik 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.show more

Table of contents

Foreword xvIntroduction xviiChapter 1: Logical query processing 1Logical query-processing phases 3Logical query-processing phases in brief 4Sample query based on customers/orders scenario 6Logical query-processing phase details 8Step 1: The FROM phase 8Step 2: The WHERE phase 14Step 3: The GROUP BY phase 15Step 4: The HAVING phase 16Step 5: The SELECT phase 17Step 6: The ORDER BY phase 20Step 7: Apply the TOP or OFFSET-FETCH filter 22Further aspects of logical query processing 26Table operators 26Window functions 35The UNION, EXCEPT, and INTERSECT operators 38Conclusion 39Chapter 2: Query tuning 41Internals 41Pages and extents 42Table organization 43Tools to measure query performance 53Access methods 57Table scan/unordered clustered index scan 57Unordered covering nonclustered index scan 60Ordered clustered index scan 62Ordered covering nonclustered index scan 63The storage engine's treatment of scans 65Nonclustered index seek + range scan + lookups 81Unordered nonclustered index scan + lookups 91Clustered index seek + range scan 93Covering nonclustered index seek + range scan 94Cardinality estimates 97Legacy estimator vs. 2014 cardinality estimator 98Implications of underestimations and overestimations 99Statistics 101Estimates for multiple predicates 104Ascending key problem 107Unknowns 110Indexing features 115Descending indexes 115Included non-key columns 119Filtered indexes and statistics 120Columnstore indexes 123Inline index definition 130Prioritizing queries for tuning with extended events 131Index and query information and statistics 134Temporary objects 139Set-based vs. iterative solutions 149Query tuning with query revisions 153Parallel query execution 158How intraquery parallelism works 158Parallelism and query optimization 175The parallel APPLY query pattern 181Conclusion 186Chapter 3: Multi-table queries 187Subqueries 187Self-contained subqueries 187Correlated subqueries 189The EXISTS predicate 194Misbehaving subqueries 201Table expressions 204Derived tables 205CTEs 207Views 211Inline table-valued functions 215Generating numbers 215The APPLY operator 218The CROSS APPLY operator 219The OUTER APPLY operator 221Implicit APPLY 221Reuse of column aliases 222Joins 224Cross join 224Inner join 228Outer join 229Self join 230Equi and non-equi joins 230Multi-join queries 231Semi and anti semi joins 237Join algorithms 239Separating elements 245The UNION, EXCEPT, and INTERSECT operators 249The UNION ALL and UNION operators 250The INTERSECT operator 253The EXCEPT operator 255Conclusion 257Chapter 4: Grouping, pivoting, and windowing 259Window functions 259Aggregate window functions 260Ranking window functions 281Offset window functions 285Statistical window functions 288Gaps and islands 291Pivoting 299One-to-one pivot 300Many-to-one pivot 304Unpivoting 307Unpivoting with CROSS JOIN and VALUES 308Unpivoting with CROSS APPLY and VALUES 310Using the UNPIVOT operator 312Custom aggregations 313Using a cursor 314Using pivoting 315Specialized solutions 316Grouping sets 327GROUPING SETS subclause 328CUBE and ROLLUP clauses 331Grouping sets algebra 333Materializing grouping sets 334Sorting 337Conclusion 339Chapter 5: TOP and OFFSET-FETCH 341The TOP and OFFSET-FETCH filters 341The TOP filter 341The OFFSET-FETCH filter 345Optimization of filters demonstrated through paging 346Optimization of TOP 346Optimization of OFFSET-FETCH 354Optimization of ROW_NUMBER 358Using the TOP option with modifications 360TOP with modifications 360Modifying in chunks 361Top N per group 363Solution using ROW_NUMBER 364Solution using TOP and APPLY 365Solution using concatenation (a carry-along sort) 366Median 368Solution using PERCENTILE_CONT 369Solution using ROW_NUMBER 369Solution using OFFSET-FETCH and APPLY 370Conclusion 371Chapter 6: Data modification 373Inserting data 373SELECT INTO 373Bulk import 376Measuring the amount of logging 377BULK rowset provider 378Sequences 381Characteristics and inflexibilities of the identity property 381The sequence object 382Performance considerations 387Summarizing the comparison of identity with sequence 394Deleting data 395TRUNCATE TABLE 395Deleting duplicates 399Updating data 401Update using table expressions 402Update using variables 403Merging data 404MERGE examples 405Preventing MERGE conflicts 408ON isn't a filter 409USING is similar to FROM 410The OUTPUT clause 411Example with INSERT and identity 412Example for archiving deleted data 413Example with the MERGE statement 414Composable DML 417Conclusion 417Chapter 7: Working with date and time 419Date and time data types 419Date and time functions 422Challenges working with date and time 434Literals 434Identifying weekdays 436Handling date-only or time-only data with DATETIME and SMALLDATETIME 439First, last, previous, and next date calculations 440Search argument 445Rounding issues 447Querying date and time data 449Grouping by the week 449Intervals 450Conclusion 471Chapter 8: T-SQL for BI practitioners 473Data preparation 473Sales analysis view 474Frequencies 476Frequencies without window functions 476Frequencies with window functions 477Descriptive statistics for continuous variables 479Centers of a distribution 479Spread of a distribution 482Higher population moments 487Linear dependencies 495Two continuous variables 495Contingency tables and chi-squared 501Analysis of variance 505Definite integration 509Moving averages and entropy 512Moving averages 512Entropy 518Conclusion 522Chapter 9: Programmable objects 525Dynamic SQL 525Using the EXEC command 525Using the sp_executesql procedure 529Dynamic pivot 530Dynamic search conditions 535Dynamic sorting 542User-defined functions 546Scalar UDFs 546Multistatement TVFs 550Stored procedures 553Compilations, recompilations, and reuse of execution plans 554Table type and table-valued parameters 571EXECUTE WITH RESULT SETS 573Triggers 575Trigger types and uses 575Efficient trigger programming 581SQLCLR programming 585SQLCLR architecture 586CLR scalar functions and creating your first assembly 588Streaming table-valued functions 597SQLCLR stored procedures and triggers 605SQLCLR user-defined types 617SQLCLR user-defined aggregates 628Transaction and concurrency 632Transactions described 633Locks and blocking 636Lock escalation 641Delayed durability 643Isolation levels 645Deadlocks 657Error handling 662The TRY-CATCH construct 662Errors in transactions 666Retry logic 669Conclusion 670Chapter 10: In-Memory OLTP 671In-Memory OLTP overview 671Data is always in memory 672Native compilation 673Lock and latch-free architecture 673SQL Server integration 674Creating memory-optimized tables 675Creating indexes in memory-optimized tables 676Clustered vs. nonclustered indexes 677Nonclustered indexes 677Hash indexes 680Execution environments 690Query interop 690Natively compiled procedures 699Surface-area restrictions 703Table DDL 703DML 704Conclusion 705Chapter 11: Graphs and recursive queries 707Terminology 707Graphs 707Trees 708Hierarchies 709Scenarios 709Employee organizational chart 709Bill of materials (BOM) 711Road system 715Iteration/recursion 718Subgraph/descendants 719Ancestors/path 730Subgraph/descendants with path enumeration 733Sorting 736Cycles 740Materialized path 742Maintaining data 743Querying 749Materialized path with the HIERARCHYID data type 754Maintaining data 756Querying 763Further aspects of working with HIERARCHYID 767Nested sets 778Assigning left and right values 778Querying 784Transitive closure 787Directed acyclic graph 787Conclusion 801Index 803show more

Rating details

13 ratings
4.46 out of 5 stars
5 62% (8)
4 23% (3)
3 15% (2)
2 0% (0)
1 0% (0)
Book ratings by Goodreads
Goodreads is the world's largest site for readers with over 50 million reviews. We're featuring millions of their reader ratings on our book pages to help you find your new favourite book. Close X