Length of integer in SQL (i.e. length of decimal string)

On my machine versions 2 and 3 come out about equal and beat the other two.

Edit: Though it has just occurred to me that my original test was a bit unfair on CASE as ordering the statements in ascending numerical order means that only 10 possible numbers would meet the first condition and exit early. I've added an additional test below. You might also try nesting CASE statements to do a binary search.

SET NOCOUNT ONSET STATISTICS TIME ON  PRINT 'Test 1';   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),        E02(N) AS (SELECT 1 FROM E00 a, E00 b),        E04(N) AS (SELECT 1 FROM E02 a, E02 b),        E08(N) AS (SELECT 1 FROM E04 a, E04 b),        E16(N) AS (SELECT 1 FROM E08 a, E08 b),        E32(N) AS (SELECT 1 FROM E16 a, E16 b),   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32) SELECT MAX(FLOOR(LOG10(N))+1)   FROM cteTally  WHERE N <= 10000000;  PRINT 'Test 2';     WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),        E02(N) AS (SELECT 1 FROM E00 a, E00 b),        E04(N) AS (SELECT 1 FROM E02 a, E02 b),        E08(N) AS (SELECT 1 FROM E04 a, E04 b),        E16(N) AS (SELECT 1 FROM E08 a, E08 b),        E32(N) AS (SELECT 1 FROM E16 a, E16 b),   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32) SELECT MAX(LEN(CONVERT(VARCHAR, N)))   FROM cteTally  WHERE N <= 10000000;  PRINT 'Test 3';     WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),        E02(N) AS (SELECT 1 FROM E00 a, E00 b),        E04(N) AS (SELECT 1 FROM E02 a, E02 b),        E08(N) AS (SELECT 1 FROM E04 a, E04 b),        E16(N) AS (SELECT 1 FROM E08 a, E08 b),        E32(N) AS (SELECT 1 FROM E16 a, E16 b),   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32) SELECT MAX(LEN(CAST(N AS VARCHAR(10))))   FROM cteTally  WHERE N <= 10000000;  PRINT 'Test 4';     WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),        E02(N) AS (SELECT 1 FROM E00 a, E00 b),        E04(N) AS (SELECT 1 FROM E02 a, E02 b),        E08(N) AS (SELECT 1 FROM E04 a, E04 b),        E16(N) AS (SELECT 1 FROM E08 a, E08 b),        E32(N) AS (SELECT 1 FROM E16 a, E16 b),   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)SELECT MAX(CASE             WHEN N < 10 THEN 1             WHEN N < 100 THEN 2             WHEN N < 1000 THEN 3             WHEN N < 10000 THEN 4             WHEN N < 100000 THEN 5             WHEN N < 1000000 THEN 6             WHEN N < 10000000 THEN 7             WHEN N < 100000000 THEN 8           END)FROM   cteTallyWHERE  N <= 10000000;     PRINT 'Test 5';     WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),        E02(N) AS (SELECT 1 FROM E00 a, E00 b),        E04(N) AS (SELECT 1 FROM E02 a, E02 b),        E08(N) AS (SELECT 1 FROM E04 a, E04 b),        E16(N) AS (SELECT 1 FROM E08 a, E08 b),        E32(N) AS (SELECT 1 FROM E16 a, E16 b),   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32) SELECT MAX(CASE              WHEN N >= 100000000 THEN NULL             WHEN N >= 10000000 THEN 8             WHEN N >= 1000000  THEN 7             WHEN N >= 100000   THEN 6             WHEN N >= 10000    THEN 5             WHEN N >= 1000     THEN 4             WHEN N >= 100      THEN 3             WHEN N >= 10       THEN 2                ELSE                    1            END   )   FROM cteTally  WHERE N <= 10000000;

Results from an example run on my machine are

Test 1   CPU time = 9422 ms,  elapsed time = 9523 ms.Test 2   CPU time = 7021 ms,  elapsed time = 7130 ms.Test 3   CPU time = 6864 ms,  elapsed time = 7006 ms.Test 4   CPU time = 9328 ms,  elapsed time = 9456 ms.Test 5   CPU time = 6989 ms,  elapsed time = 7358 ms.    

To answer your question, the second version is clearer about what you actually want. Think about what someone looking at this code in six months will think: will they realize that the first version is trying obtain the length of a number represented in decimal, or will they think that you're performing some obscure mathematical operation that they can't find documentation requiring?

More generally, though, you should probably consider storing these values as character data anyway, since they aren't representing real "numbers" to you (you aren't comparing based upon relative value, you aren't performing arithmetic, etc.). You can use CHECK constraints to ensure that only numeric digits are in the field.

I'm not clear on why storing them as character data would require conversions in your queries, assuming that you're consistent. There's also no reason to assume that dealing with ints would be faster than varchar, especially if there's conversion involved in both cases.