Readers found five ways to simulate LEAD() and LAG() without the SQL Server 2012 analytic functions. This post shows each solution with its author, runs all five on SQL Server 2025 and compares the rows with the real functions. Four return the same ten rows and one adds a rule of its own.
Moving Averages in T-SQL
Moving averages in T-SQL use ROWS BETWEEN for rolling windows, but date gaps and duplicate dates decide what the result actually means.
NTILE Groups: How Uneven Rows and Ties Land in Each Tile
NTILE groups your sorted rows into the tiles you ask for, but the groups aren’t always the same size. This post uses ten hiking trails to show the sizes when rows do not divide evenly, how tied rows land in different tiles, and what happens with more tiles than rows. It also shows how to pull the top 2 percent of a list.
RANK( ), DENSE_RANK( ), and ROW_NUMBER( ): How Each Handles Ties
RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) return the same numbers until your data has a tie. This post ranks eight teas with all three functions and shows how each one handles tied scores. It also shows how to list the top three distinct scores and why ROW_NUMBER needs a tiebreaker.
Whole Unit Allocation: Keep a Proportional Split Exact
Whole unit allocation keeps a fixed budget intact. I’d calculate base shares first, then distribute residual units using a stable tie rule. Check equal weights, a zero budget and zero-weight members.
Finding Duplicates Before Adding a Unique Constraint
A unique constraint fails on the first duplicate it meets and tells you about only one. Here is how to list them all and clean up safely.
FIFO Inventory Costing in T-SQL: Matching Sales to Purchase Lots
Which purchase lot paid for which sale? Two running totals and one join answer it, with no cursor and no loop.