• Million row test for three of the solutions. Results are interesting/surpising. Perhaps some of you folks could check this and run it.

    --===== Conditionally drop the test table to make reruns in SSMS easier.

    -- This is NOT a part of the solution.

    IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL

    DROP TABLE #Test

    ;

    --===== Create and populate the test table.

    -- This is NOT a part of the solution.

    SELECT TOP (1000000)

    TestID = IDENTITY(INT,1,1),

    TestNumber = ABS(CHECKSUM(NEWID()))

    INTO #Test

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    --===== Add the expected PK

    ALTER TABLE #Test

    ADD PRIMARY KEY CLUSTERED (TestID)

    ;

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

    PRINT '========== SomewhereSomehow ==========================================='

    SET STATISTICS TIME ON;

    DECLARE @T INT;

    with nums(n) as(select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))nums(n))

    select @T = sum(convert(int,substring(convert(varchar(10),TestNumber),n,1)))

    from nums

    CROSS JOIN #Test

    where n <= len(convert(varchar(10),TestNumber))

    GROUP BY TestNumber;

    SET STATISTICS TIME OFF;

    GO

    PRINT '========== CELKO ==========================================='

    SET STATISTICS TIME ON;

    DECLARE @T INT

    SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) ,'0', '') ,'1', '#')

    ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

    FROM #Test;

    SET STATISTICS TIME OFF;

    GO

    PRINT '========== Mark ====================================================='

    SET STATISTICS TIME ON;

    DECLARE @T INT;

    WITH Tens(Pos,Val) AS (

    SELECT 1, 1 UNION ALL

    SELECT 2, 10 UNION ALL

    SELECT 3, 100 UNION ALL

    SELECT 4, 1000 UNION ALL

    SELECT 5, 10000 UNION ALL

    SELECT 6, 100000 UNION ALL

    SELECT 7, 1000000 UNION ALL

    SELECT 8, 10000000 UNION ALL

    SELECT 9, 100000000 UNION ALL

    SELECT 10,1000000000)

    SELECT @T = SUM((TestNumber / Val) % 10)

    FROM Tens

    CROSS JOIN #Test

    WHERE Val<=TestNumber

    GROUP BY TestNumber;

    SET STATISTICS TIME OFF;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537