Cadavre (8/6/2012)

---

Mark-101232 (8/6/2012)

---

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

Celko forgot to account for the REPLACE "bug".

Change his code to this: -

DECLARE @T INT = 985;

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

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

Then take another look at your test results: -

--===== 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;

GO

PRINT '========== IMPROVED 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)) COLLATE Latin1_General_BIN2,'0', '') ,'1', '#')

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

FROM #Test;

SET STATISTICS TIME OFF;

GO

========== SomewhereSomehow ===========================================

SQL Server Execution Times:

CPU time = 28155 ms, elapsed time = 8756 ms.

========== CELKO ===========================================

SQL Server Execution Times:

CPU time = 24343 ms, elapsed time = 24385 ms.

========== Mark =====================================================

SQL Server Execution Times:

CPU time = 19921 ms, elapsed time = 6877 ms.

========== IMPROVED CELKO ===========================================

SQL Server Execution Times:

CPU time = 3594 ms, elapsed time = 3619 ms.

---

--edit--

Found the link to the replace "bug"