wBob (7/23/2014)

---

I did trial that and honestly there is not a lot in it, with solution 1 (mine) slightly faster 5-10% on average at scale of 1 million. My test rig:

------------------------------------------------------------------------------------------------

-- Setup START

-- http://www.sqlservercentral.com/Forums/Topic1594823-2799-2.aspx?Update=1

------------------------------------------------------------------------------------------------

USE tempdb

GO

IF OBJECT_ID('#claims') IS NOT NULL DROP TABLE #claims

CREATE TABLE #claims

(

claimNumberINT,

maxSequenceINT,

PRIMARY KEY ( claimNumber, maxSequence )

)

GO

;WITH cte AS

(

SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn

FROM master.sys.columns c1

CROSS JOIN master.sys.columns c2

CROSS JOIN master.sys.columns c3

)

INSERT INTO #claims ( claimNumber, maxSequence )

--VALUES ( 111, 17 ), ( 222, 38 ), ( 333, 20 )

SELECT rn, rn % 33

FROM cte

GO

IF OBJECT_ID('dbo.numbers') IS NULL

BEGIN

CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY KEY ( x ) )

INSERT INTO dbo.numbers ( x )

SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) x

FROM master.sys.columns c1

CROSS JOIN master.sys.columns c2

CROSS JOIN master.sys.columns c3

END

GO

------------------------------------------------------------------------------------------------

-- Solution 1 START

------------------------------------------------------------------------------------------------

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

-- This was good at scale

SELECT claimNumber, maxSequence + x.x AS maxSequence

INTO #tmp1

FROM (

SELECT

claimNumber,

MAX(maxSequence) maxSequence,

( ( MAX(maxSequence) / 20 ) + 1 ) * 20 [rollupTo]

FROM #claims

GROUP BY claimNumber

) c

CROSS JOIN dbo.numbers x

WHERE x.x <= [rollupTo] - maxSequence

-- Solution 1 END

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

-- Solution 2 START

-- ChrisM@Work

------------------------------------------------------------------------------------------------

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

-- 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.claimNumber,

maxSequence = d.LastSequenceNumber + x.n

INTO #tmp2

FROM (

SELECT

claimNumber,

RowsToConstruct = 20-(MAX(maxSequence)%20),

LastSequenceNumber = MAX(maxSequence)

FROM #claims

GROUP BY claimNumber

) d

CROSS APPLY (

SELECT TOP (RowsToConstruct)

n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM e4

) x

GO

-- Solution 2 END

------------------------------------------------------------------------------------------------

See attachment for results. Call it a draw? : )