• Jeff/Jacob:

    first - Jacob - very nice. Good solid method for handling a common request.

    Jeff - I'll chalk this up to being the forever contrarian... But the comparison gets more interesting when you "right-size" the Itzik method. Notice what one tiny little change does (since we KNOW how many results we want....)

    SET STATISTICS TIME ON

    GO

    DECLARE @Bitbucket INT

    --=============================================================================

    PRINT REPLICATE('=',100)

    PRINT 'Itzek''s method:'

    ;WITH

    L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows

    L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows

    L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows

    L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows

    L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows

    L5 AS (SELECT 1 AS C FROM L4 AS A, L0 AS B),--131072 rows

    num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)

    SELECT @Bitbucket = N FROM NUM WHERE N <= 1000000;

    --=============================================================================

    PRINT REPLICATE('=',100)

    PRINT 'Jeff Moden''s Method'

    ; WITH cTally AS

    (-----------------------------------------------------------------------------

    --==== High performance CTE equivalent of a Tally or Numbers table

    SELECT TOP (1000000)

    ROW_NUMBER() OVER (ORDER BY t1.ID) AS N

    FROM Master.sys.SysColumns t1

    CROSS JOIN Master.sys.SysColumns t2

    )-----------------------------------------------------------------------------

    SELECT @Bitbucket = N FROM cTally --Do your outer join with table being checked here

    PRINT REPLICATE('=',100)

    Can you spot the difference (it's in bold....hehe)?

    Of course - we could debate why we're fighting so hard to reclaim 200ms or less, but - what's the fun in that?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?