OK,

Here we go.

The test as promissed.

Starting with creating a table:

CREATE TABLE [dbo].[UsedRedemptionCodesTest](

[RedemptionCodeID] [int] IDENTITY(1,1) NOT NULL,

[RedemptionCode] [char](36) NULL,

[IsActive] [bit] NOT NULL,

CONSTRAINT [PK_UsedRedemptionCodesTest_RedemptionCodeID] PRIMARY KEY CLUSTERED ([IsActive], [RedemptionCodeID] ASC))

CREATE UNIQUE NONCLUSTERED INDEX [AK_UsedRedemptionCodesTest_RedemptionCode] ON [dbo].[UsedRedemptionCodesTest] ([RedemptionCode] ASC)

GO

Not much of a difference from the OP, only different indexing approach.

And I do not have the code generator, so I intend to use NEW_ID() instead, so I have increased the length of the RedemptionCode.

Now populate it:

DECLARE @N INT

SET @N = 1E7

INSERT INTO dbo.UsedRedemptionCodesTest

([RedemptionCode], [IsActive])

SELECT NEWID(), CASE WHEN N < @N * 0.99 THEN 0 ELSE 1 END IsActive

FROM dbo.TallyGenerator(0, @N, 1, 1)

ORDER BY N

10 million +1 records. Not an Amazon scale, but should be enough to get the picture.

1% of the records (100k rows) are marked as active, the rest - inactive.

Should be pretty close to a real life scenario.

Now we run the RedemtionCode generator:

DECLARE @RedemptionSeed INT

DECLARE @RedemptionCode CHAR(36)

SET STATISTICS IO ON

WHILE((@RedemptionCode IS NULL) OR EXISTS(SELECT RedemptionCode FROM dbo.UsedRedemptionCodesTest

WHERE RedemptionCode = @RedemptionCode))

BEGIN

SET @RedemptionSeed = (SELECT ABS(CHECKSUM(NEWID())))

SET @RedemptionCode = NEWID() -- dbo.GenerateRandomBase32(@RedemptionSeed)

END

INSERT INTO dbo.UsedRedemptionCodesTest (RedemptionCode, [IsActive])

VALUES (@RedemptionCode, 1)

SET STATISTICS IO OFF

GO 10

Here is what statistics indicate:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

Table 'UsedRedemptionCodesTest'. Scan count 0, logical reads 4, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Table 'UsedRedemptionCodesTest'. Scan count 0, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

I'm not posting timing stats, as they're gonna be different on every other machine, but the 10 runs above took less than a second on my laptop.

Now it's time to retrieve the codes.

Here is the code:

SET STATISTICS IO ON

SELECT TOP 10 RedemptionCode, [RedemptionCodeID]

INTO #Out

FROM dbo.UsedRedemptionCodesTest

WHERE isActive = 1

ORDER BY [RedemptionCodeID] -- selecting in the order of rows in the clustered index, and in the same time in a random order of codes appearance

SELECT * FROM #Out

UPDATE dbo.UsedRedemptionCodesTest

SET IsActive = 0

WHERE IsActive = 1

AND [RedemptionCodeID] <= (SELECT MAX([RedemptionCodeID]) FROM #Out)

SET STATISTICS IO OFF

-- we know that the selected ID's are all sequential and they are all first ID's in sequence of still active ones.

-- only records within a single data page or within 2 consecutive ones are updated,

-- and their order within clustered index remain the same after update - no rewriting records, no fragmentation.

DROP TABLE #Out

GO

And stats:

Table 'UsedRedemptionCodesTest'. Scan count 1, logical reads 4, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(10 row(s) affected)

(1 row(s) affected)

(10 row(s) affected)

Table '#Out________________________________________________________________________________________________________________00000000003D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Table 'UsedRedemptionCodesTest'. Scan count 1, logical reads 175, physical reads 2, read-ahead reads 23, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#Out________________________________________________________________________________________________________________00000000003D'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If we need a bigger bunches of codes:SELECT TOP 100 RedemptionCode, [RedemptionCodeID]

the stats will look like this:

Table 'UsedRedemptionCodesTest'. Scan count 1, logical reads 7, physical reads 5, read-ahead reads 589, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(100 row(s) affected)

(1 row(s) affected)

(100 row(s) affected)

Table '#Out________________________________________________________________________________________________________________00000000003E'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Table 'UsedRedemptionCodesTest'. Scan count 1, logical reads 1538, physical reads 1, read-ahead reads 196, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#Out________________________________________________________________________________________________________________00000000003E'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(100 row(s) affected)

Again, it does not change a digit on my laptop's clock while completed.

For the reference:

SET STATISTICS IO ON

SELECT COUNT(*) FROM dbo.UsedRedemptionCodesTest

SET STATISTICS IO OFF

takes about 30 seconds to complete and shows these stats:

Table 'UsedRedemptionCodesTest'. Scan count 1, logical reads 62360, physical reads 182, read-ahead reads 62356, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Conclusion:

Appropriate clustered index exclude historical records with inactive cards from the execution plan.

Those records are not accessed in routine calls, they do not create any overhead and do not slow down queries at all.

The test confirms that there is no any reason to partition the data in the table. It won't add anything to the queries performance, but will create an overhead of maintaining 2 tables instead of 1.

If anybody can show how a separate table for inactive codes or any other form of partitioning provides better performance counters for this task - I'd really appreciate that.