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