{"id":96899,"date":"2023-05-29T00:00:32","date_gmt":"2023-05-29T00:00:32","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96899"},"modified":"2026-05-08T10:15:40","modified_gmt":"2026-05-08T10:15:40","slug":"t-sql-gap-filling-challenge","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/t-sql-gap-filling-challenge\/","title":{"rendered":"T-SQL Gap-Filling: Filling Missing Dates with LAG, IGNORE NULLS, and Range Matching"},"content":{"rendered":"<b>Gap-filling a time series &#8211; taking a sparse record of values-at-some-dates and producing a dense record of values-at-every-date-in-the-range, with missing values carried forward from the prior known value (LOCF imputation) &#8211; is a common data-prep task in financial reporting, inventory management, and time-series analysis. <\/b>\n\n<b>This article presents a specific gap-filling challenge (filling a balance table with workday-level rows, carrying the last-known balance forward) and walks through three T-SQL solutions with measured performance: (1) TOP-based lookup using APPLY, which works on any version but has scan-heavy execution on large data; (2) IGNORE NULLS in SQL Server 2022+ combined with FIRST_VALUE, which is the cleanest modern solution; (3) range matching between the sparse values table and a workdays table using gaps-and-islands techniques, which is the most complex to write but best-performing at scale. Each solution is shown complete with performance numbers across several dataset sizes.<\/b>\n\nA student of mine sent me a T-SQL challenge involving gap-filling of missing dates and balances. I found the challenge interesting and common enough and figured that my readers would probably find it interesting to work on as well.\n\nI\u2019ll present the challenge and three possible solutions, as well as the results of a performance test. AS always, I urge you to try and come up with your own solutions before looking at mine.\n\n<em>If you would like to download the code including the solutions, you can find it in a .zip file <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/T-SQL-Gap-Filling-Challenge-Code.zip\">here<\/a>.<\/em>\n<h2>The Challenge<\/h2>\nThe challenge involves two tables called <code>Workdays<\/code> and <code>Balances<\/code>. The <code>Workdays<\/code> table holds the dates of all workdays going back a few years. The <code>Balances<\/code> table holds account IDs, dates of workdays when the account balance changed, and the balance on the current date. There\u2019s a foreign key on the date column in the <code>Balances<\/code> table referencing the date column in the <code>Workdays<\/code> table.\n\nUse the following code to create the Workdays and Balances tables:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SET NOCOUNT ON;\n\nUSE tempdb;\n\nDROP TABLE IF EXISTS dbo.Balances, dbo.Workdays;\nGO\n\nCREATE TABLE dbo.Workdays\n(\n  dt DATE NOT NULL,\n  CONSTRAINT PK_Workdays PRIMARY KEY(dt)\n);\n\nCREATE TABLE dbo.Balances\n(\n  accountid INT NOT NULL,\n  dt DATE NOT NULL\n    CONSTRAINT FK_Balances_Workdays REFERENCES dbo.Workdays,\n  balance NUMERIC(12, 2) NOT NULL,\n  CONSTRAINT PK_Balances PRIMARY KEY(accountid, dt)\n);<\/pre>\nNote that the definition of the primary key constraint on <code>Workdays<\/code> results in a clustered index with <code>dt<\/code> as the key. Similarly, the definition of the primary key constraint on <code>Balances<\/code> results in a clustered index with (<code>accountid<\/code>, <code>dt<\/code>) as the key.\n\nTo generate the sample data for this challenge, you will need a helper function that returns a sequence of integers in a desired range. You can use the <code>GetNums<\/code> function for this purpose, which you create using the following code:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">CREATE OR ALTER FUNCTION dbo.GetNums\n      (@low AS BIGINT = 1, @high AS BIGINT)\n  RETURNS TABLE\nAS\nRETURN\n  WITH\n    L0 AS ( SELECT 1 AS c \n            FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),\n                        (1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),\n    L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),\n    L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),\n    L3 AS ( SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B ),\n    Nums AS ( SELECT ROW_NUMBER() \n                       OVER(ORDER BY (SELECT NULL)) AS rownum\n              FROM L3 )\n  SELECT TOP(@high - @low + 1)\n     rownum AS rn,\n     @high + 1 - rownum AS op,\n     @low - 1 + rownum AS n\n  FROM Nums\n  ORDER BY rownum;<\/pre>\n<em>Alternatively, if you\u2019re using SQL Server 2022 or Azure SQL Database, you can use the built-in <\/em><code><em>GENERATE_SERIES<\/em><\/code><em> function instead. In my examples I\u2019ll use the <\/em><code><em>GetNums<\/em><\/code><em> function.<\/em>\n\nUse the following code to populate the <code>Workdays<\/code> table with dates in 2013 through 2022 excluding weekends as sample data:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DECLARE @start AS DATE = '20130101', @end AS DATE = '20221231';\n\nINSERT INTO dbo.Workdays(dt)\n  SELECT A.dt\n  FROM dbo.GetNums(0, DATEDIFF(day, @start, @end)) AS N\n    CROSS APPLY (VALUES(DATEADD(day, N.n, @start))) AS A(dt)\n  -- exclude Saturday and Sunday\n  WHERE DATEDIFF(day, '19000107', dt) % 7 + 1 NOT IN (1, 7); \n<\/pre>\nNormally, dates of holidays would also be excluded from the table, but for our testing purposes we can keep things simple and just exclude weekends.\n\nAs for the <code>Balances<\/code> table; to check the logical correctness of your solutions, you can use the following code, which populates the table with a small set of sample data:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">TRUNCATE TABLE dbo.Balances;\n\nINSERT INTO dbo.Balances(accountid, dt, balance)\n  VALUES(1, '20220103', 8000.00),\n        (1, '20220322', 10000.00),\n        (1, '20220607', 15000.00),\n        (1, '20221115', 7000.00),\n        (1, '20221230', 4000.00),\n        (2, '20220118', 12000.00),\n        (2, '20220218', 14000.00),\n        (2, '20220318', 16000.00),\n        (2, '20220418', 18500.00),\n        (2, '20220518', 20400.00),\n        (2, '20220620', 19000.00),\n        (2, '20220718', 21000.00),\n        (2, '20220818', 23200.00),\n        (2, '20220919', 25500.00),\n        (2, '20221018', 23400.00),\n        (2, '20221118', 25900.00),\n        (2, '20221219', 28000.00);<\/pre>\nThe task involves developing a stored procedure called <code>GetBalances<\/code> that accepts a parameter called <code>@accountid<\/code> representing an account ID. The stored procedure should return a result set with all existing dates and balances for the input account, but also gap-filled with the dates of the missing workdays between the existing minimum and maximum dates for the account, along with the last known balance up to that point. The result should be ordered by the date.\n\nOnce you have written your code, you can use the following code to test your solution for correctness with account ID 1 as input:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXEC dbo.GetBalances @accountid = 1;<\/pre>\nWith the small set of sample data you should get an output with the following 260 rows, shown here in abbreviated form:\n\n<code>dt\u00a0 \u00a0 \u00a0 \u00a0 \u00a0balance\n---------- --------\n2022-01-03 8000.00\n2022-01-04 8000.00\n2022-01-05 8000.00\n2022-01-06 8000.00\n2022-01-07 8000.00\n...\n2022-03-21 8000.00\n2022-03-22 10000.00\n2022-03-23 10000.00\n2022-03-24 10000.00\n2022-03-25 10000.00\n2022-03-28 10000.00\n...\n2022-06-06 10000.00\n2022-06-07 15000.00\n2022-06-08 15000.00\n2022-06-09 15000.00\n2022-06-10 15000.00\n2022-06-13 15000.00\n...\n2022-11-14 15000.00\n2022-11-15 7000.00\n2022-11-16 7000.00\n2022-11-17 7000.00\n2022-11-18 7000.00\n2022-11-21 7000.00\n...\n2022-12-29 7000.00\n2022-12-30 4000.00<\/code>\n\nTest your solution with account ID 2 as input:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EXEC dbo.GetBalances @accountid = 2;<\/pre>\nYou should get an output with the following 240 rows, shown here in abbreviated form:\n\n<code>dt\u00a0 \u00a0 \u00a0 \u00a0 \u00a0balance<\/code>\n<code>---------- --------<\/code>\n<code>2022-01-18 12000.00<\/code>\n<code>2022-01-19 12000.00<\/code>\n<code>2022-01-20 12000.00<\/code>\n<code>2022-01-21 12000.00<\/code>\n<code>2022-01-24 12000.00<\/code>\n<code>...<\/code>\n<code>2022-02-17 12000.00\n<\/code><code>2022-02-18 14000.00<\/code>\n<code>2022-02-21 14000.00<\/code>\n<code>2022-02-22 14000.00<\/code>\n<code>2022-02-23 14000.00<\/code>\n<code>2022-02-24 14000.00<\/code>\n<code>...<\/code>\n<code>2022-03-17 14000.00<\/code>\n<code>2022-03-18 16000.00<\/code>\n<code>2022-03-21 16000.00<\/code>\n<code>2022-03-22 16000.00<\/code>\n<code>2022-03-23 16000.00<\/code>\n<code>2022-03-24 16000.00<\/code>\n<code>...<\/code>\n<code>2022-04-15 16000.00<\/code>\n<code>2022-04-18 18500.00<\/code>\n<code>2022-04-19 18500.00<\/code>\n<code>2022-04-20 18500.00<\/code>\n<code>2022-04-21 18500.00<\/code>\n<code>2022-04-22 18500.00<\/code>\n<code>...<\/code>\n<code>2022-05-17 18500.00<\/code>\n<code>2022-05-18 20400.00<\/code>\n<code>2022-05-19 20400.00<\/code>\n<code>2022-05-20 20400.00<\/code>\n<code>2022-05-23 20400.00<\/code>\n<code>2022-05-24 20400.00<\/code>\n<code>...<\/code>\n<code>2022-06-17 20400.00<\/code>\n<code>2022-06-20 19000.00<\/code>\n<code>2022-06-21 19000.00<\/code>\n<code>2022-06-22 19000.00<\/code>\n<code>2022-06-23 19000.00<\/code>\n<code>2022-06-24 19000.00<\/code>\n<code>...<\/code>\n<code>2022-07-15 19000.00<\/code>\n<code>2022-07-18 21000.00<\/code>\n<code>2022-07-19 21000.00<\/code>\n<code>2022-07-20 21000.00<\/code>\n<code>2022-07-21 21000.00<\/code>\n<code>2022-07-22 21000.00<\/code>\n<code>...<\/code>\n<code>2022-08-17 21000.00<\/code>\n<code>2022-08-18 23200.00<\/code>\n<code>2022-08-19 23200.00<\/code>\n<code>2022-08-22 23200.00\n<\/code><code>2022-08-23 23200.00<\/code>\n<code>2022-08-24 23200.00<\/code>\n<code>...<\/code>\n<code>2022-09-16 23200.00<\/code>\n<code>2022-09-19 25500.00<\/code>\n<code>2022-09-20 25500.00<\/code>\n<code>2022-09-21 25500.00<\/code>\n<code>2022-09-22 25500.00<\/code>\n<code>2022-09-23 25500.00<\/code>\n<code>...<\/code>\n<code>2022-10-17 25500.00<\/code>\n<code>2022-10-18 23400.00<\/code>\n<code>2022-10-19 23400.00<\/code>\n<code>2022-10-20 23400.00<\/code>\n<code>2022-10-21 23400.00<\/code>\n<code>2022-10-24 23400.00<\/code>\n<code>...<\/code>\n<code>2022-11-17 23400.00<\/code>\n<code>2022-11-18 25900.00<\/code>\n<code>2022-11-21 25900.00<\/code>\n<code>2022-11-22 25900.00<\/code>\n<code>2022-11-23 25900.00<\/code>\n<code>2022-11-24 25900.00<\/code>\n<code>...<\/code>\n<code>2022-12-16 25900.00<\/code>\n<code>2022-12-19 28000.00<\/code>\n<h2>Performance Testing Technique<\/h2>\nTo test the performance of your solutions you will need to populate the <code>Balances<\/code> table with a larger set of sample data. You can use the following code for this purpose:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">DECLARE\n  @numaccounts AS INT = 100000,\n  @maxnumbalances AS INT = 24,\n  @maxbalanceval AS INT = 500000,\n  @mindate AS DATE = '20220101',\n  @maxdate AS DATE = '20221231';\n\nTRUNCATE TABLE dbo.Balances;\n\nINSERT INTO dbo.Balances WITH (TABLOCK) (accountid, dt, balance)\n  SELECT \n      A.accountid, \n      B.dt, \n    CAST(ABS(CHECKSUM(NEWID())) % @maxbalanceval AS NUMERIC(12, 2)) \n                                                          AS balance\n  FROM (SELECT n AS accountid, \n               ABS(CHECKSUM(NEWID())) % @maxnumbalances + 1 AS numbalances\n        FROM dbo.GetNums(1, @numaccounts)) AS A\n    CROSS APPLY (SELECT TOP (A.numbalances) W.dt                   \n                 FROM dbo.Workdays AS W\n                 WHERE W.dt BETWEEN @mindate AND @maxdate\n                 ORDER BY CHECKSUM(NEWID())) AS B;<\/pre>\nThis code sets parameters for the sample data before filling the table. The performance results that I\u2019ll share in this article were based on sample data created with the following parameter values:\n<ul>\n \t<li>Number of accounts: 100,000<\/li>\n \t<li>Number of balances per account: random number between 1 and 24<\/li>\n \t<li>Balance dates: chosen randomly from <code>Workdays<\/code> within the range 2022-01-01 to 2022-12-31<\/li>\n<\/ul>\nUsing the above parameters, the sample data resulted in about 1,250,000 rows in the <code>Balances<\/code> table. That\u2019s an average of about 12.5 balances per account <em>before<\/em> gap filling. <em>After<\/em> gap filling the average number of balances per account was about 200. That\u2019s the average number of rows returned from the stored procedure.\n\nIf you need to handle a similar task in your environment, of course you can feel free to alter the input parameters to reflect your needs.\n\nThe requirement from our stored procedure is to have a sub-millisecond runtime as it\u2019s expected to be executed very frequently from many concurrent sessions.\n\nIf you\u2019re planning to test your solutions from SSMS, you\u2019d probably want to run them in a loop with many iterations, executing the stored procedure with a random input account ID in each iteration. You can measure the total loop execution time and divide it by the number of iterations to get the average duration per proc execution.\n\nKeep in mind though that looping in T-SQL is quite expensive. To account for this, you can measure the time it takes the loop to run without executing the stored procedure and subtract it from the time it takes it to run including the execution of the stored procedure.\n\nMake sure to check &#8220;Discard results after execution&#8221; in the SSMS Query Options&#8230; dialog to eliminate the time it takes SSMS to print the output rows. Your test will still measure the procedure\u2019s execution time including the time it takes SQL Server to transmit the result rows to the SSMS client, just without printing them. Also, make sure to <em>not<\/em> turn on Include Actual Execution Plan in SSMS.\n\nBased on all of the above, you can use the following code to test your solution proc with 100,000 iterations, and compute the average duration per proc execution in microseconds:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DECLARE \n  @numaccounts AS INT = 100000, \n  @numiterations AS INT = 100000,\n  @i AS INT, \n  @curaccountid AS INT,\n  @p1 AS DATETIME2, @p2 AS DATETIME2, @p3 AS DATETIME2,\n  @duration AS INT;\n\n-- Run loop without proc execution to measure loop overhead\nSET @p1 = SYSDATETIME();\nSET @i = 1;\nWHILE @i &lt;= @numiterations\nBEGIN\n  SET @curaccountid = ABS(CHECKSUM(NEWID())) % @numaccounts + 1;\n  SET @i += 1;\nEND;\nSET @p2 = SYSDATETIME();\n\n-- Run loop with proc execution\nSET @i = 1;\nWHILE @i &lt;= @numiterations\nBEGIN\n  SET @curaccountid = ABS(CHECKSUM(NEWID())) % @numaccounts + 1;\n  EXEC dbo.GetBalances @accountid = @curaccountid;\n  SET @i += 1;\nEND;\n\nSET @p3 = SYSDATETIME();\n-- Compute average proc execution time, accounting for \n-- loop overhead\nSET @duration = (DATEDIFF_BIG(microsecond, @p2, @p3) - \n               DATEDIFF_BIG(microsecond, @p1, @p2)) \/ @numiterations;\n\nPRINT \n  'Average runtime across '\n    + CAST(@numiterations AS VARCHAR(10)) + ' executions: '\n    + CAST(@duration AS VARCHAR(10)) + ' microseconds.';<\/pre>\nIf you also want to measure the number of logical reads, you can run an extended events session with the <code>sql_batch_completed<\/code> event and a filter based on the target session ID. You can divide the total number of reads by the number of iterations in your test to get the average per proc execution.\n\nYou can use the following code to create such an extended event session, after replacing the session ID 56 in this example with the session ID of your session:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE EVENT SESSION [Batch Completed for Session] ON SERVER \nADD EVENT sqlserver.sql_batch_completed(\n    WHERE ([sqlserver].[session_id]=(56)));<\/pre>\nOne thing that a test from SSMS won\u2019t do is emulate a concurrent workload. To achieve this you can use the <a href=\"https:\/\/learn.microsoft.com\/en-us\/troubleshoot\/sql\/tools\/replay-markup-language-utility\">ostress tool<\/a>, which allows you to run a test where you indicate the number of concurrent threads and the number of iterations per thread. As an example, the following code tests the solution proc with 100 threads, each iterating 1,000 times, in quiet mode (suppressing the output):\n\n<code>ostress.exe -SYourServerName -E -dtempdb -Q\"DECLARE @numaccounts AS INT = 100000; DECLARE @curaccountid AS INT = ABS(CHECKSUM(NEWID())) % @numaccounts + 1; EXEC dbo.GetBalances @accountid = @curaccountid;\" -n100 -r1000 -q<\/code>\n\nOf course, you\u2019ll need to change \u201c<code>YourServerName<\/code>\u201d with your server\u2019s name. The <code>ostress<\/code> tool reports the total execution time of the test. You can divide it by (<code>numthreads x numiterations<\/code>) to get the average proc execution time.\n\nNext, I\u2019ll present three solutions that I came up with, and report their average runtimes based on both the SSMS and the <code>ostress<\/code> testing techniques, as well their average number of logical reads.\n<h2>Solution 1, using TOP<\/h2>\nFollowing is my first solution, which relies primarily on the TOP filter:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">CREATE OR ALTER PROC dbo.GetBalances\n  @accountid AS INT\nAS\n\nSET NOCOUNT ON;\n\nSELECT W.dt,\n   (SELECT TOP (1) B.balance\n    FROM dbo.Balances AS B\n    WHERE B.accountid = @accountid\n      AND B.dt &lt;= W.dt\n    ORDER BY B.dt DESC) AS balance\nFROM dbo.Workdays AS W\nWHERE W.dt BETWEEN (SELECT MIN(dt) \n                    FROM dbo.Balances \n                    WHERE accountid = @accountid)\n               AND (SELECT MAX(dt) \n                    FROM dbo.Balances \n                    WHERE accountid = @accountid)\nORDER BY W.dt;<\/pre>\nThis is probably the most obvious solution that most people will come up with intuitively.\n\nThe code queries the <code>Workdays<\/code> table (aliased as <code>W<\/code>), filtering the dates that exist between the minimum and maximum balance dates for the account. In the <code>SELECT<\/code> list the code returns the current workday date (<code>W.dt<\/code>), as well as the result of a <code>TOP (1)<\/code> subquery against <code>Balances<\/code> (aliased as <code>B<\/code>) to obtain the last known balance. That\u2019s the most recent balance associated with a balance date (<code>B.dt<\/code>) that is less than or equal to the current workday date (<code>W.dt<\/code>). The code finally orders the result by <code>W.dt<\/code>.\n\nThe plan for Solution 1 is shown in Figure 1.\n\n<img loading=\"lazy\" decoding=\"async\" width=\"1428\" height=\"428\" class=\"wp-image-96900\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/word-image-96899-1.png\" \/>\n\n<strong>Figure 1: Plan for Solution 1<\/strong>\n\nThe top two seeks against the <code>Balances<\/code> clustered index obtain the minimum and maximum balance dates for the account. Each involves as many logical reads as the depth of the index (3 levels in our case). These two seeks represents a small portion of the cost of the plan (12%).\n\nThe bottom right seek against the <code>Workdays<\/code> clustered index obtains the dates of the workdays between those minimum and maximum. This seek involves just a couple of logical reads since each leaf page holds hundreds of dates, and recall that in our scenario, each account has an average of about 200 qualifying workdays. This seek also represents a small portion of the cost of the plan (6%).\n\nThe bulk of the cost of this plan is associated with the bottom left seek against the Balances clustered index (77%). This seek represents the <code>TOP<\/code> subquery obtaining the last known balance, and is executed per qualifying workday. With an average of 200 qualifying workdays and 3 levels in the index, this seek performs about 600 logical reads across all executions.\n\nNotice that there\u2019s no explicit sorting in the plan since the query ordering request is satisfied by retrieving the data based on index order.\n\nHere are the performance numbers that I got for this solution in average per proc execution:\n\n<code>Duration with SSMS test: 698 microseconds<\/code>\n\n<code>Duration with ostress test: 491 microseconds<\/code>\n\n<code>Logical reads: 672<\/code>\n<h2>Solution 2, using IGNORE NULLS<\/h2>\nSQL Server 2022 introduces a number of T-SQL features to support queries involving time-series data. Once specific feature that is relevant to our challenge is the standard <code>NULL<\/code> treatment clause for offset-window functions (<code>FIRST_VALUE<\/code>, <code>LAST_VALUE<\/code>, <code>LAG<\/code> and <code>LEAD<\/code>). This feature allows you to specify the <code>IGNORE NULLS<\/code> clause if you want the function to continue looking for a non-<code>NULL<\/code> value when the value in the requested position is missing (is <code>NULL<\/code>). You can find more details about the <code>NULL<\/code> treatment clause <a href=\"https:\/\/sqlperformance.com\/2022\/05\/t-sql-queries\/windowing-improvements-sql-server-2022\">here<\/a>.\n\nFollowing is my second solution, which uses the <code>NULL<\/code> treatment clause:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE OR ALTER PROC dbo.GetBalances\n  @accountid AS INT\nAS\nSET NOCOUNT ON;\nSELECT W.dt, \n       LAST_VALUE(B.balance) IGNORE NULLS \n           OVER(ORDER BY W.dt ROWS UNBOUNDED PRECEDING) AS balance\nFROM dbo.Workdays AS W\n  LEFT OUTER JOIN dbo.Balances AS B\n    ON B.accountid = @accountid\n    AND W.dt = B.dt\nWHERE W.dt BETWEEN (SELECT MIN(dt) \n                    FROM dbo.Balances \n                    WHERE accountid = @accountid)\n               AND (SELECT MAX(dt) \n                    FROM dbo.Balances \n                    WHERE accountid = @accountid)\nORDER BY W.dt;\nGO<\/pre>\nThe code performs a left outer join between <code>Workdays<\/code> and <code>Balances<\/code>. It matches workday dates that appear between the minimum and maximum balance dates for the account (obtained with subqueries like in Solution 1) with the balance dates for the account. When a match is found, the outer join returns the respective balance on that workday date. When a match isn\u2019t found, the outer join produces a <code>NULL<\/code> value as the balance.\n\nThe <code>SELECT<\/code> list then computes the last known balance using the <code>LAST_VALUE<\/code> function with the <code>IGNORE NULLS<\/code> clause.\n\nLike in Solution 1, this solution\u2019s code returns the data ordered by <code>W.dt<\/code>.\n\nThe plan for Solution 2 is shown in Figure 2.\n\n<img loading=\"lazy\" decoding=\"async\" width=\"1429\" height=\"609\" class=\"wp-image-96901\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/word-image-96899-2.png\" \/>\n\n<strong>Figure 2: Plan for Solution 2<\/strong>\n\nThe topmost seek in the plan against the Balances clustered index retrieves the balances for the account. The cost here is 3 logical reads.\n\nThe two seeks in the middle of the plan against the Balances clustered index retrieve the minimum and maximum balance dates for the account. Each costs 3 logical reads.\n\nThe bottom seek against the <code>Workdays<\/code> clustered index retrieves the workday dates in the desired range. It costs a couple of logical reads.\n\nThe Merge Join (Right Outer Join) operator merges the workday dates with the respective balance dates based on index order, preserving the workday dates.\n\nFinally, the operators to the left of the Merge Join operator compute the <code>LAST_VALUE<\/code> function\u2019s result, applying the logic relevant to the <code>IGNORE NULLS<\/code> option.\n\nLike with Solution 1\u2019s plan, this solution\u2019s plan has no explicit sorting since the query ordering request is satisfied by retrieving the data based on index order.\n\nHere are the performance numbers that I got for this solution in average per proc execution:\n\n<code>Duration with SSMS test: 395 microseconds<\/code>\n\n<code>Duration with ostress test: 282 microseconds<\/code>\n\n<code>Logical reads: 11<\/code>\n\nAs you can see, that\u2019s a significant improvement in runtime and a dramatic improvement in I\/O footprint compared to Solution 1.\n<h2>Solutions 3a and 3b, Matching Balance Date Ranges with Workdays<\/h2>\nThe characteristics of our data are that there are very few balances per account (an average of 12.5), and many more applicable workdays (an average of about 200). With this in mind, a solution that starts with <code>Balances<\/code> and then looks for matches in <code>Workdays<\/code> could potentially be quite optimal, as long as it also gets optimized this way. Here&#8217;s solution 3a implements this approach:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE OR ALTER PROC dbo.GetBalances\n  @accountid AS INT\nAS\nSET NOCOUNT ON;\nWITH C AS\n(\n  SELECT B.dt, B.balance,\n         LEAD(B.dt) OVER(ORDER BY B.dt) AS nextdt\n  FROM dbo.Balances AS B\n  WHERE accountid = @accountid\n)\nSELECT ISNULL(W.dt, C.dt) AS dt, C.balance\nFROM C\n  LEFT OUTER JOIN dbo.Workdays AS W\n    ON W.dt &gt;= C.dt AND W.dt &lt; C.nextdt\nORDER BY dt;\nGO<\/pre>\nThe query in the CTE called <code>C<\/code> uses the <code>LEAD<\/code> function to create current-next balance date ranges.\n\nThe outer query then uses a left outer join between <code>C<\/code> and <code>Workdays<\/code> (aliased as <code>W<\/code>), matching each balance date range with all workday dates that fall into that date range (<code>W.dt &gt;= C.dt AND W.dt &lt; C.nextdt<\/code>).\n\nThe <code>SELECT<\/code> list returns the first non-<code>NULL<\/code> value among <code>W.dt<\/code> and <code>C.dt<\/code> as the result <code>dt<\/code> column, and <code>C.balance<\/code> as the effective balance on that date.\n\nThe outer query returns the result ordered by the result column dt.\n\nThe plan for Solution 3a in Figure 3.\n\n<img loading=\"lazy\" decoding=\"async\" width=\"1432\" height=\"227\" class=\"wp-image-96902\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/word-image-96899-3.png\" \/>\n\n<strong>Figure 3: Plan for Solution 3a<\/strong>\n\nThe top seek against the clustered index on balances retrieves the balances for the account. This seek costs 3 logical reads. The remaining operators to the left of the seek and before the <code>Nested Loops<\/code> operator compute the <code>LEAD<\/code> function\u2019s result, relying on index order. Then, a seek is applied against the clustered index on <code>Workdays<\/code> per balance date range. In average, you get 12.5 seeks, each costing a couple of reads.\n\nThe one tricky thing in this plan is that there\u2019s a <code>Sort<\/code> operator used to handles the query\u2019s presentation ordering request. The explicit sorting is needed since the ordering expression is a result of a computation with the <code>ISNULL<\/code> function. That\u2019s similar to breaking SARGability when applying manipulation on the filtered column. Since we\u2019re talking about a couple hundred rows that need to be sorted in average, the sort is not a disaster, but it does require a memory grant request, and it does cost a bit.\n\nHere are the performance numbers that I got for this solution in average per proc execution:\n\n<code>Duration with SSMS test: 278 microseconds<\/code>\n\n<code>Duration with ostress test: 211 microseconds<\/code>\n\n<code>Logical reads: 26<\/code>\n\nEven though the number of logical reads is a bit higher than for Solution 2, the runtime is an improvement.\n\nIf we could just get rid of the explicit sorting though! There is a neat trick to achieve this. Remember, the sorting is required due to the fact that the ordering expression is the result of a computation with the <code>ISNULL<\/code> function. You can still get the correct ordering without manipulation by ordering by the composite column list <code>C.dt<\/code>, <code>W.dt<\/code>.\n\nHere&#8217;s solution 3b implements this approach:\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE OR ALTER PROC dbo.GetBalances\n  @accountid AS INT\nAS\nSET NOCOUNT ON;\nWITH C AS\n(\n  SELECT B.dt, B.balance,\n         LEAD(B.dt) OVER(ORDER BY B.dt) AS nextdt\n  FROM dbo.Balances AS B\n  WHERE accountid = @accountid\n)\nSELECT ISNULL(W.dt, C.dt) AS dt, C.balance\nFROM C\n  LEFT OUTER JOIN dbo.Workdays AS W\n    ON W.dt &gt;= C.dt AND W.dt &lt; C.nextdt\nORDER BY C.dt, W.dt;\nGO<\/pre>\nThe plan for Solution 3b in Figure 4.\n\n<img loading=\"lazy\" decoding=\"async\" width=\"1430\" height=\"247\" class=\"wp-image-96903\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/word-image-96899-4.png\" \/>\n\n<strong>Figure 4: Plan for Solution 3b<\/strong>\n\nVoila! The plan is similar to that of Solution 3a, but the sort is gone.\n\nHere are the performance numbers I got for this solution:\n\n<code>Duration with SSMS test: 196 microseconds<\/code>\n\n<code>Duration with ostress test: 165 microseconds<\/code>\n\n<code>Logical reads: 26<\/code>\n\nThat\u2019s a pretty nice improvement compared to the performance numbers that we started with for Solution 1.\n<h2>Performance Summary<\/h2>\nFigure 5 shows the performance test results. It has the average runtimes using both the SSMS test and the <code>ostress<\/code> test summarized as bar charts corresponding to the left Y axis, and the line chart representing the average logical reads corresponding to the right Y axis.\n\n<img loading=\"lazy\" decoding=\"async\" width=\"790\" height=\"574\" class=\"wp-image-96904\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/word-image-96899-5.png\" \/>\n\n<strong>Figure 5: Performance Test Summary<\/strong>\n<h2>Conclusion<\/h2>\nIn this article I covered a gap-filling challenge that involved gap-filling balances with missing workday dates, and for each reporting the last known balance. I showed three different solutions including one that uses a new time-series related feature called the <code>NULL<\/code> treatment clause.\n\nAs you can imagine, indexing and reuse of cached query plans were paramount here, and both were handled optimally. Using a good representative sample data that adequately represents the production environment, and an adequate testing technique are also important.\n\nThe performance expectations were for a sub-millisecond runtime in a concurrent workload, and this requirement was achieved. In fact, all solutions satisfied this requirement, with the last taking less than 200 microseconds to run.\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. How do I fill gaps in a time series in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Three common approaches depending on SQL Server version and performance requirements: (1) TOP-based lookup &#8211; for each target date, APPLY a query selecting TOP 1 from the sparse values table where value_date &lt;= target_date ORDER BY value_date DESC. Works on any SQL Server version but can be slow because the APPLY runs once per target row. (2) IGNORE NULLS with FIRST_VALUE &#8211; on SQL Server 2022+, join the sparse values to the dense target range (producing NULLs where values are missing), then use FIRST_VALUE(value) IGNORE NULLS OVER (ORDER BY date) to carry the last-known value forward. This is the cleanest modern solution. (3) Range matching &#8211; use gaps-and-islands techniques to compute each sparse value&#8217;s &#8216;valid range&#8217; (until the next sparse value), then join those ranges against the dense target list. Most complex to write but best-performing on large data.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. What is IGNORE NULLS in SQL Server 2022?<\/h3>\n            <div class=\"faq-answer\">\n                <p>IGNORE NULLS is a modifier for the FIRST_VALUE, LAST_VALUE, LAG, and LEAD window functions introduced in SQL Server 2022. By default, these functions return NULL when the referenced row has a NULL value; with IGNORE NULLS, they skip NULL values and return the nearest non-NULL value in the specified direction. Syntax: FIRST_VALUE(col IGNORE NULLS) OVER (ORDER BY sort_col). The feature enables clean LOCF (last-observation-carried-forward) imputation in a single window function call &#8211; previously requiring multi-step CTEs or APPLY joins. Available in SQL Server 2022, Azure SQL Database, and Azure SQL Managed Instance; not available in SQL Server 2019 or earlier.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. What is LOCF imputation in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>LOCF (Last Observation Carried Forward) is a statistical imputation method for filling missing values in a time series by using the most recent non-missing value as the fill. If you have balance observations on Monday, Wednesday, and Friday but need a daily series, LOCF imputes Monday&#8217;s balance for Tuesday, Wednesday&#8217;s balance for Thursday, and so on. In SQL Server, LOCF is implemented either with FIRST_VALUE &#8230; IGNORE NULLS (SQL 2022+), with LAG-based windowing that propagates the last non-NULL value, or with APPLY-based TOP 1 lookups. Use LOCF when a conservative fill is appropriate (finance, inventory); use alternatives like linear interpolation when the underlying process is continuous and smooth.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. Which gap-filling technique performs best in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>It depends on data characteristics. For small-to-medium data (tens of thousands of rows) where the sparse table has few observations per key, the TOP-based APPLY approach is often good enough. For SQL Server 2022+, IGNORE NULLS is the simplest to write and performs well in most cases. For large data (millions of rows) where the sparse table has many observations per key, range-matching against a workdays table is typically the fastest because it replaces per-row lookups with a single join. The article&#8217;s benchmarks show range-matching pulling ahead at larger scales. Test on representative data &#8211; performance ordering can shift based on row counts, index availability, and the ratio of sparse-to-dense rows.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Three T-SQL approaches for filling gaps in a time series with last-known values (LOCF imputation): TOP-based lookup, IGNORE NULLS (SQL Server 2022+), and range matching against a workdays table. Includes a complete challenge setup, three working solutions, and performance benchmarks across dataset sizes.&hellip;<\/p>\n","protected":false},"author":341753,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143531],"tags":[159009],"coauthors":[158990],"class_list":["post-96899","post","type-post","status-publish","format-standard","hentry","category-featured","category-t-sql-programming-sql-server","tag-summitspeaker2023itzikbengan"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96899","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341753"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=96899"}],"version-history":[{"count":14,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96899\/revisions"}],"predecessor-version":[{"id":110398,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96899\/revisions\/110398"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=96899"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=96899"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=96899"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=96899"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}