# 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 `int`

s would be faster than `varchar`

, especially if there's conversion involved in both cases.