OK,

Turns out, Scott does not want to discredit his religious beliefs by publishing results of scientific tests.

So I decided to wrap the discussion with doing my own test using Active-Inactive tables.

I've done it in the most efficient way (to my best understanding).

I appreciate that somebody can offer a better way, so any corrections are welcome.

Here is my setup:

CREATE TABLE [dbo].[UsedRedemptionCodes_Active](

[RedemptionCode] [char](36) NOT NULL

CONSTRAINT [PK_UsedRedemptionCodes_Active] PRIMARY KEY CLUSTERED ([RedemptionCode])

)

GO

CREATE TABLE [dbo].[UsedRedemptionCodes_Inactive](

[RedemptionCode] [char](36) NOT NULL,

CONSTRAINT [PK_UsedRedemptionCodes_Inactive] PRIMARY KEY CLUSTERED ([RedemptionCode])

)

GO

INSERT INTO dbo.UsedRedemptionCodes_Active

([RedemptionCode])

SELECT RedemptionCode

FROM dbo.UsedRedemptionCodesTest

WHERE IsActive = 1

ORDER BY RedemptionCode

INSERT INTO dbo.UsedRedemptionCodes_Inactive

([RedemptionCode])

SELECT RedemptionCode

FROM dbo.UsedRedemptionCodesTest

WHERE IsActive = 0

ORDER BY RedemptionCode

I used the records from the same table which I populated in the previous test. So, it's exactly the same recordset.

Now, let's generate new codes:

DECLARE @RedemptionSeed INT

DECLARE @RedemptionCode CHAR(36)

SET STATISTICS IO ON

WHILE((@RedemptionCode IS NULL)

OR EXISTS(SELECT RedemptionCode FROM dbo.UsedRedemptionCodes_Active WHERE RedemptionCode = @RedemptionCode)

OR EXISTS(SELECT RedemptionCode FROM dbo.UsedRedemptionCodes_Inactive WHERE RedemptionCode = @RedemptionCode)

)

BEGIN

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

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

END

INSERT INTO dbo.UsedRedemptionCodes_Active (RedemptionCode)

VALUES (@RedemptionCode)

SET STATISTICS IO OFF

GO 10

Output statistics:

Beginning execution loop

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.

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

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

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

... repeated 8 times ...

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.

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

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

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

Batch execution completed 10 times.

Aggregated Numbers are pretty much the same as in the first test.

Now - giving away the random codes:

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

SELECT TOP 100 RedemptionCode

INTO #Out

FROM dbo.UsedRedemptionCodes_Active

ORDER BY NEWID() -- we must make sure that the codes are given away in the random order

-- as we cannot allow codes presented in a sequence which would be easy to guess

SELECT * FROM #Out

INSERT INTO UsedRedemptionCodes_Inactive (RedemptionCode)

SELECT RedemptionCode FROM #Out

DELETE FROM dbo.UsedRedemptionCodes_Active

WHERE EXISTS (SELECT * FROM #Out

WHERE #Out.RedemptionCode = dbo.UsedRedemptionCodes_Active.RedemptionCode)

SET STATISTICS IO OFF

SET STATISTICS PROFILE OFF

DROP TABLE #Out

And the stats:

Table 'UsedRedemptionCodes_Active'. Scan count 1, logical reads 605, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#Out________________________________________________________________________________________________________________0000000000CA'. 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.

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

Table '#Out________________________________________________________________________________________________________________0000000000CA'. 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.

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

Table '#Out________________________________________________________________________________________________________________0000000000CA'. 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.

Aggregated numbers are on the same level as in the test with a single table, only actually "a little" worse.

Conclusion:

Partitioning of a table not only does not improve performance, it actually makes it worse.