Lynn Pettis (7/22/2014)
ChrisM@home (7/22/2014)
@wBob, you might find a TOP() -limited IBG-style inline tally even more efficient.Sort of like this:
IF OBJECT_ID('dbo.numbers') IS NULL
BEGIN
CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY KEY ( x ) );
WITH e1(n) as (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (SELECT 1 FROM e1 a CROSS JOIN e1 b),
e4(n) as (SELECT 1 FROM e2 a CROSS JOIN e2 b),
eTally(n) as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
INSERT INTO dbo.numbers ( x )
SELECT TOP 1000 n
FROM eTally;
END
You may want to compare the actual execution plans between your code and this code.
Almost. I meant inline as in the tally table is built on-the-fly:
-- Demonstrate a solution using the sample data
WITH
e1(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) dt (n)),
e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b),
e4(n) AS (SELECT 1 FROM e2 a CROSS JOIN e2 b)
SELECT
d.[Claim number],
[sequence number] = d.LastSequenceNumber + x.n
FROM (
SELECT
[Claim number],
RowsToConstruct = 20-(MAX([sequence number])%20),
LastSequenceNumber = MAX([sequence number])
FROM #SampleTable
GROUP BY [Claim number]
) d
CROSS APPLY (
SELECT TOP (RowsToConstruct)
n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM e4
) x
No more rows are constructed than are needed.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden