SmartSellTM - The New Way to Sell Online

Shop over a million Toys in our Huge New Range

T-SQL Querying

New or Used: $97.25
T-SQL insiders help you tackle your toughest queries and query-tuning problems Squeeze 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 logic Master an efficient top-down tuning methodology Assess algorithmic complexity to predict performance Compare data aggregation techniques, including new grouping sets Efficiently perform data-analysis calculations Make the most of T-SQL's optimized bulk import tools Avoid date/time pitfalls that lead to buggy, poorly performing code Create optimized BI statistical queries without additional software Use programmable objects to accelerate queries Unlock major performance improvements with In-Memory OLTP Master useful and elegant approaches to manipulating graphs About This Book For experienced T-SQL practitioners Includes coverage updated from Inside Microsoft SQL Server 2008 T-SQL Querying and Inside Microsoft SQL Server 2008 T-SQL Programming Valuable to developers, DBAs, BI professionals, and data scientists Covers many MCSE 70-464 and MCSA/MCSE 70-461 exam topics
Product Details

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 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 carry-along sort) 366 Median 368 Solution using PERCENTILE_CONT 369 Solution using ROW_NUMBER 369 Solution using OFFSET-FETCH and APPLY 370 Conclusion 371 Chapter 6: Data modification 373 Inserting data 373 SELECT INTO 373 Bulk import 376 Measuring the amount of logging 377 BULK rowset provider 378 Sequences 381 Characteristics and inflexibilities of the identity property 381 The sequence object 382 Performance considerations 387 Summarizing the comparison of identity with sequence 394 Deleting data 395 TRUNCATE TABLE 395 Deleting duplicates 399 Updating data 401 Update using table expressions 402 Update using variables 403 Merging data 404 MERGE examples 405 Preventing MERGE conflicts 408 ON isn't a filter 409 USING is similar to FROM 410 The OUTPUT clause 411 Example with INSERT and identity 412 Example for archiving deleted data 413 Example with the MERGE statement 414 Composable DML 417 Conclusion 417 Chapter 7: Working with date and time 419 Date and time data types 419 Date and time functions 422 Challenges working with date and time 434 Literals 434 Identifying weekdays 436 Handling date-only or time-only data with DATETIME and SMALLDATETIME 439 First, last, previous, and next date calculations 440 Search argument 445 Rounding issues 447 Querying date and time data 449 Grouping by the week 449 Intervals 450 Conclusion 471 Chapter 8: T-SQL for BI practitioners 473 Data preparation 473 Sales analysis view 474 Frequencies 476 Frequencies without window functions 476 Frequencies with window functions 477 Descriptive statistics for continuous variables 479 Centers of a distribution 479 Spread of a distribution 482 Higher population moments 487 Linear dependencies 495 Two continuous variables 495 Contingency tables and chi-squared 501 Analysis of variance 505 Definite integration 509 Moving averages and entropy 512 Moving averages 512 Entropy 518 Conclusion 522 Chapter 9: Programmable objects 525 Dynamic SQL 525 Using the EXEC command 525 Using the sp_executesql procedure 529 Dynamic pivot 530 Dynamic search conditions 535 Dynamic sorting 542 User-defined functions 546 Scalar UDFs 546 Multistatement TVFs 550 Stored procedures 553 Compilations, recompilations, and reuse of execution plans 554 Table type and table-valued parameters 571 EXECUTE WITH RESULT SETS 573 Triggers 575 Trigger types and uses 575 Efficient trigger programming 581 SQLCLR programming 585 SQLCLR architecture 586 CLR scalar functions and creating your first assembly 588 Streaming table-valued functions 597 SQLCLR stored procedures and triggers 605 SQLCLR user-defined types 617 SQLCLR user-defined aggregates 628 Transaction and concurrency 632 Transactions described 633 Locks and blocking 636 Lock escalation 641 Delayed durability 643 Isolation levels 645 Deadlocks 657 Error handling 662 The TRY-CATCH construct 662 Errors in transactions 666 Retry logic 669 Conclusion 670 Chapter 10: In-Memory OLTP 671 In-Memory OLTP overview 671 Data is always in memory 672 Native compilation 673 Lock and latch-free architecture 673 SQL Server integration 674 Creating memory-optimized tables 675 Creating indexes in memory-optimized tables 676 Clustered vs. nonclustered indexes 677 Nonclustered indexes 677 Hash indexes 680 Execution environments 690 Query interop 690 Natively compiled procedures 699 Surface-area restrictions 703 Table DDL 703 DML 704 Conclusion 705 Chapter 11: Graphs and recursive queries 707 Terminology 707 Graphs 707 Trees 708 Hierarchies 709 Scenarios 709 Employee organizational chart 709 Bill of materials (BOM) 711 Road system 715 Iteration/recursion 718 Subgraph/descendants 719 Ancestors/path 730 Subgraph/descendants with path enumeration 733 Sorting 736 Cycles 740 Materialized path 742 Maintaining data 743 Querying 749 Materialized path with the HIERARCHYID data type 754 Maintaining data 756 Querying 763 Further aspects of working with HIERARCHYID 767 Nested sets 778 Assigning left and right values 778 Querying 784 Transitive closure 787 Directed acyclic graph 787 Conclusion 801 Index 803

About the Author

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.

Look for similar items by category
How Fishpond Works
Fishpond works with suppliers all over the world to bring you a huge selection of products, really great prices, and delivery included on over 25 million products that we sell. We do our best every day to make Fishpond an awesome place for customers to shop and get what they want — all at the best prices online.
Webmasters, Bloggers & Website Owners
You can earn a 5% commission by selling T-SQL Querying on your website. It's easy to get started - we will give you example code. After you're set-up, your website can earn you money while you work, play or even sleep! You should start right now!
Authors / Publishers
Are you the Author or Publisher of a book? Or the manufacturer of one of the millions of products that we sell. You can improve sales and grow your revenue by submitting additional information on this title. The better the information we have about a product, the more we will sell!
Item ships from and is sold by Fishpond World Ltd.
Back to top