I know I must be doing something wrong here so will somebody please check me?

SELECT TestNum, Jeff=CASE

WHEN FLOOR(REVERSE(ABS(TestNum))) = 0.0

THEN 0

ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1) END

,Markus=CASE

WHEN FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 < 0

THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 END

,Dwain=CASE

WHEN FLOOR(TestNum) = TestNum THEN 0

ELSE LEN(CAST(REVERSE(ABS(TestNum) % 1) AS DECIMAL(38,0))) END

FROM (

SELECT CAST(99 AS DECIMAL(38,15))

UNION ALL SELECT CAST(99.1 AS DECIMAL(38,15))

UNION ALL SELECT CAST(99.11 AS DECIMAL(38,15))

UNION ALL SELECT CAST(99.111 AS DECIMAL(38,15))

UNION ALL SELECT CAST(99.1111 AS DECIMAL(38,15))

UNION ALL SELECT CAST(99.11111 AS DECIMAL(38,15))

UNION ALL SELECT CAST(99.111111 AS DECIMAL(38,15))

UNION ALL SELECT CAST(99.1111111111111111 AS DECIMAL(38,15))

UNION ALL SELECT CAST(0.1 AS DECIMAL(38,15))) Nums(TestNum)

CREATE TABLE #BigNums (TestNum DECIMAL(38,15))

INSERT INTO #BigNums

SELECT 1.* CHECKSUM(NEWID()) / POWER(10, ABS(CHECKSUM(NEWID())) % 10)

FROM (

SELECT TOP 1000000 1

FROM sys.all_columns a, sys.all_columns b

)Tally(n)

DECLARE @Hold DECIMAL(38,15)

PRINT 'Jeff'

SET STATISTICS TIME ON

SELECT @Hold=CASE

WHEN FLOOR(REVERSE(ABS(TestNum))) = 0.0

THEN 0

ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)))+1)

END

FROM #BigNums

SET STATISTICS TIME OFF

PRINT 'Markus'

SET STATISTICS TIME ON

SELECT @Hold=CASE

WHEN FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 < 0

THEN 0 ELSE FLOOR(LOG10(REVERSE(ABS(TestNum)+1)))+1 END

FROM #BigNums

SET STATISTICS TIME OFF

PRINT 'Dwain'

SET STATISTICS TIME ON

SELECT @Hold=CASE

WHEN FLOOR(TestNum) = TestNum THEN 0

ELSE LEN(CAST(REVERSE(ABS(TestNum) % 1) AS DECIMAL(38,0))) END

FROM #BigNums

SET STATISTICS TIME OFF

DROP TABLE #BigNums

I get these timing results which just can't possibly be right. :w00t:

Jeff

SQL Server Execution Times:

CPU time = 3339 ms, elapsed time = 3411 ms.

Markus

SQL Server Execution Times:

CPU time = 4227 ms, elapsed time = 4291 ms.

Dwain

SQL Server Execution Times:

CPU time = 2028 ms, elapsed time = 2036 ms.

Edit: Fixed the Tally table I used to set up the test harness to be SQL 2000 compatible (I think).