book

Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions

by Itzik Ben-Gan

April 2012

Intermediate to advanced

244 pages

6h 33m

English

Microsoft Press

Read now

Unlock full access

Who Should Read This BookOrganization of This BookSystem RequirementsCode SamplesAcknowledgmentsErrata & Book SupportWe Want to Hear from YouStay in Touch
Background of Window FunctionsWindow Functions DescribedSet-Based vs. Iterative/Cursor ProgrammingDrawbacks of Alternatives to Window FunctionsA Glimpse of Solutions Using Window FunctionsElements of Window FunctionsPartitioningOrderingFramingQuery Elements Supporting Window FunctionsLogical Query ProcessingClauses Supporting Window FunctionsCircumventing the LimitationsPotential for Additional FiltersReuse of Window DefinitionsSummary
Window Aggregate FunctionsWindow Aggregate Functions DescribedSupported Windowing ElementsPartitioningOrdering and FramingFurther Filtering IdeasDistinct AggregatesNested AggregatesRanking FunctionsSupported Windowing ElementsROW_NUMBERDeterminismNTILERANK and DENSE_RANKDeterminismDistribution FunctionsSupported Windowing ElementsRank Distribution FunctionsInverse Distribution FunctionsOffset FunctionsSupported Windowing ElementsLAG and LEADFIRST_VALUE, LAST_VALUE, and NTH_VALUESummary
Hypothetical Set FunctionsRANKDENSE_RANKPERCENT_RANKCUME_DISTGeneral SolutionInverse Distribution FunctionsOffset FunctionsString ConcatenationSummary
Sample DataIndexing GuidelinesPOC IndexBackward ScansColumnstore IndexesRanking FunctionsROW_NUMBERNTILERANK and DENSE_RANKImproved Parallelism with APPLYAggregate and Offset FunctionsWithout Ordering and FramingWith Ordering and FramingUNBOUNDED PRECEDING: The Fast-Track CaseExpanding All Frame RowsComputing Two Cumulative ValuesDistribution FunctionsRank Distribution FunctionsInverse Distribution FunctionsSummary
Virtual Auxiliary Table of NumbersSequences of Date and Time ValuesSequences of KeysUpdate a Column with Unique ValuesApplying a Range of Sequence ValuesPagingRemoving DuplicatesPivotingTOP N Per GroupModeRunning TotalsSet-Based Solution Using Window FunctionsSet-Based Solutions Using Subqueries or JoinsCursor-Based SolutionCLR-Based SolutionNested IterationsMultirow UPDATE with VariablesPerformance BenchmarkMax Concurrent IntervalsTraditional Set-Based SolutionCursor-Based SolutionSolutions Based on Window FunctionsPerformance BenchmarkPacking IntervalsTraditional Set-Based SolutionSolutions Based on Window FunctionsGaps and IslandsGapsIslandsMedianConditional AggregateSorting HierarchiesSummary

Overview

Apply powerful window functions in T-SQL—and increase the performance and speed of your queries

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Discover how to:

Go beyond traditional query approaches to express set calculations more efficiently

Delve into ordered set functions such as rank, distribution, and offset

Implement hypothetical set and inverse distribution functions in standard SQL

Use strategies for improving sequencing, paging, filtering, and pivoting

Increase query speed using partitioning, ordering, and coverage indexing

Apply new optimization iterators such as Window Spool

Handle common issues such as running totals, intervals, medians, and gaps