Update cookies preferences

E-book: T-SQL Querying

4.58/5 (68 ratings by Goodreads)
, , ,
  • Format: 864 pages
  • Series: Developer Reference
  • Pub. Date: 18-Feb-2015
  • Publisher: Microsoft Press,U.S.
  • Language: eng
  • ISBN-13: 9780133986617
Other books in subject:
  • Format - PDF+DRM
  • Price: 34,61 €*
  • * the price is final i.e. no additional discount will apply
  • Add to basket
  • Add to Wishlist
  • This ebook is for personal use only. E-Books are non-refundable.
T-SQL QueryingLarger Image
  • Format: 864 pages
  • Series: Developer Reference
  • Pub. Date: 18-Feb-2015
  • Publisher: Microsoft Press,U.S.
  • Language: eng
  • ISBN-13: 9780133986617
Other books in subject:

DRM restrictions

  • Copying (copy/paste):

    not allowed

  • Printing:

    not allowed

  • Usage:

    Digital Rights Management (DRM)
    The publisher has supplied this book in encrypted form, which means that you need to install free software in order to unlock and read it.  To read this e-book you have to create Adobe ID More info here. Ebook can be read and downloaded up to 6 devices (single user with the same Adobe ID).

    Required software
    To read this ebook on a mobile device (phone or tablet) you'll need to install this free app: PocketBook Reader (iOS / Android)

    To download and read this eBook on a PC or Mac you need Adobe Digital Editions (This is a free app specially developed for eBooks. It's not the same as Adobe Reader, which you probably already have on your computer.)

    You can't read this ebook with Amazon Kindle

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