book

T-SQL Querying

Name: T-SQL Querying
ISBN: 9780133986631

by Itzik Ben-Gan, Adam Machanic, Dejan Sarka, Kevin Farlee

March 2015

Intermediate to advanced

864 pages

31h 12m

English

Microsoft Press

Read now

Unlock full access

Title Page
Copyright Page
Dedication Page
Contents at a glance
Contents
Foreword
Introduction
Who should read this bookAssumptionsThis book might not be for you if...Organization of this bookSystem requirementsDownloads: Code samplesAcknowledgmentsErrata, updates, & book supportFree ebooks from Microsoft PressWe want to hear from youStay in touch
Chapter 1. Logical query processing
Logical query-processing phasesLogical query-processing phases in briefSample query based on customers/orders scenarioLogical query-processing phase detailsStep 1: The FROM phaseStep 2: The WHERE phaseStep 3: The GROUP BY phaseStep 4: The HAVING phaseStep 5: The SELECT phaseStep 6: The ORDER BY phaseStep 7: Apply the TOP or OFFSET-FETCH filterFurther aspects of logical query processingTable operatorsWindow functionsThe UNION, EXCEPT, and INTERSECT operatorsConclusion
Chapter 2. Query tuning
InternalsPages and extentsTable organizationTools to measure query performanceAccess methodsTable scan/unordered clustered index scanUnordered covering nonclustered index scanOrdered clustered index scanOrdered covering nonclustered index scanThe storage engine’s treatment of scansNonclustered index seek + range scan + lookupsUnordered nonclustered index scan + lookupsClustered index seek + range scanCovering nonclustered index seek + range scanCardinality estimatesLegacy estimator vs. 2014 cardinality estimatorImplications of underestimations and overestimationsStatisticsEstimates for multiple predicatesAscending key problemUnknownsIndexing featuresDescending indexesIncluded non-key columnsFiltered indexes and statisticsColumnstore indexesInline index definition
Prioritizing queries for tuning with extended events
Index and query information and statisticsTemporary objectsSet-based vs. iterative solutionsQuery tuning with query revisionsParallel query executionHow intraquery parallelism worksParallelism and query optimizationThe parallel APPLY query patternConclusion

Chapter 3. Multi-table queries
SubqueriesSelf-contained subqueriesCorrelated subqueriesThe EXISTS predicateMisbehaving subqueriesTable expressionsDerived tablesCTEsViewsInline table-valued functionsGenerating numbersThe APPLY operatorThe CROSS APPLY operatorThe OUTER APPLY operatorImplicit APPLYReuse of column aliasesJoinsCross joinInner joinOuter joinSelf joinEqui and non-equi joinsMulti-join queriesSemi and anti semi joinsJoin algorithmsSeparating elementsThe UNION, EXCEPT, and INTERSECT operatorsThe UNION ALL and UNION operatorsThe INTERSECT operatorThe EXCEPT operatorConclusion
Chapter 4. Grouping, pivoting, and windowing
Window functionsAggregate window functionsRanking window functionsOffset window functionsStatistical window functionsGaps and islandsPivotingOne-to-one pivotMany-to-one pivotUnpivotingUnpivoting with CROSS JOIN and VALUESUnpivoting with CROSS APPLY and VALUESUsing the UNPIVOT operatorCustom aggregationsUsing a cursorUsing pivotingSpecialized solutionsGrouping setsGROUPING SETS subclauseCUBE and ROLLUP clausesGrouping sets algebraMaterializing grouping setsSortingConclusion
Chapter 5. TOP and OFFSET-FETCH
The TOP and OFFSET-FETCH filtersThe TOP filterThe OFFSET-FETCH filterOptimization of filters demonstrated through pagingOptimization of TOPOptimization of OFFSET-FETCHOptimization of ROW_NUMBERUsing the TOP option with modificationsTOP with modificationsModifying in chunksTop N per groupSolution using ROW_NUMBERSolution using TOP and APPLYSolution using concatenation (a carry-along sort)MedianSolution using PERCENTILE_CONTSolution using ROW_NUMBERSolution using OFFSET-FETCH and APPLYConclusion
Chapter 6. Data modification
Inserting dataSELECT INTOBulk importMeasuring the amount of loggingBULK rowset providerSequencesCharacteristics and inflexibilities of the identity propertyThe sequence objectPerformance considerationsSummarizing the comparison of identity with sequenceDeleting dataTRUNCATE TABLEDeleting duplicatesUpdating dataUpdate using table expressionsUpdate using variablesMerging dataMERGE examplesPreventing MERGE conflictsON isn’t a filterUSING is similar to FROMThe OUTPUT clauseExample with INSERT and identityExample for archiving deleted dataExample with the MERGE statementComposable DMLConclusion
Chapter 7. Working with date and time
Date and time data typesDate and time functionsChallenges working with date and timeLiteralsIdentifying weekdaysHandling date-only or time-only data with DATETIME and SMALLDATETIMEFirst, last, previous, and next date calculationsSearch argumentRounding issuesQuerying date and time dataGrouping by the weekIntervalsConclusion
Chapter 8. T-SQL for BI practitioners
Data preparationSales analysis viewFrequenciesFrequencies without window functionsFrequencies with window functionsDescriptive statistics for continuous variablesCenters of a distributionSpread of a distributionHigher population momentsLinear dependenciesTwo continuous variablesContingency tables and chi-squaredAnalysis of varianceDefinite integrationMoving averages and entropyMoving averagesEntropyConclusion
Chapter 9. Programmable objects
Dynamic SQLUsing the EXEC commandUsing the sp_executesql procedureDynamic pivotDynamic search conditionsDynamic sortingUser-defined functionsScalar UDFsMultistatement TVFsStored proceduresCompilations, recompilations, and reuse of execution plansTable type and table-valued parametersEXECUTE WITH RESULT SETSTriggersTrigger types and usesEfficient trigger programming
SQLCLR programming
SQLCLR architectureCLR scalar functions and creating your first assemblyStreaming table-valued functionsSQLCLR stored procedures and triggersSQLCLR user-defined typesSQLCLR user-defined aggregatesTransaction and concurrencyTransactions describedLocks and blockingLock escalationDelayed durabilityIsolation levelsDeadlocksError handlingThe TRY-CATCH constructErrors in transactionsRetry logicConclusion
Chapter 10. In-Memory OLTP
In-Memory OLTP overviewData is always in memoryNative compilationLock and latch-free architectureSQL Server integrationCreating memory-optimized tablesCreating indexes in memory-optimized tablesClustered vs. nonclustered indexesNonclustered indexesHash indexesExecution environmentsQuery interopNatively compiled proceduresSurface-area restrictionsTable DDLDMLConclusion
Chapter 11. Graphs and recursive queries
TerminologyGraphsTreesHierarchiesScenariosEmployee organizational chartBill of materials (BOM)Road systemIteration/recursionSubgraph/descendantsAncestors/pathSubgraph/descendants with path enumerationSortingCyclesMaterialized pathMaintaining dataQueryingMaterialized path with the HIERARCHYID data typeMaintaining dataQueryingFurther aspects of working with HIERARCHYID
Nested sets
Assigning left and right valuesQueryingTransitive closureDirected acyclic graphConclusion
Index
Code Snippets

Content preview from T-SQL Querying

Chapter 11. Graphs and recursive queries

This chapter covers the treatment of specialized data structures called graphs in Microsoft SQL Server using T-SQL. People use different terms when they want to discuss a graph, like hierarchy and tree, but those terms are often used incorrectly. For this reason, the chapter starts with a terminology section describing graphs and their properties to clear up the confusion.

The treatment of graphs in a relational database management system (RDBMS) is far from trivial. I’ll discuss the two main approaches. One is based on iterative or recursive logic—for example, using recursive queries. Another is based on materializing extra information in the database that describes the data structure—for example, using ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780133986631Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

T-SQL Querying

by Itzik Ben-Gan, Adam Machanic, Dejan Sarka, Kevin Farlee

Chapter 11. Graphs and recursive queries

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.