SQL Server is throwing arithmetic error when joining 3 tables

Question

When I'm trying to join 3 system tables, I get an error:

Arithmetic overflow error converting numeric to data type numeric

But if I'm trying to join only 2 of them, it works fine.

Why?

Code 1: (doesn't work)

SELECT 
    database_id = (D.database_id),
    last_user_lookup = MAX(U.last_user_seek),
    last_user_update = MAX(U.last_user_seek),
    total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)),
    create_date = MAX(D.create_date)
FROM 
    sys.databases D
JOIN 
    sys.dm_db_index_usage_stats U ON (D.database_id = U.database_id)
JOIN 
    sys.master_files F ON (D.database_id = F.database_id)
GROUP BY 
    D.database_id;

Code 2: (does work)

SELECT 
    database_id = (D.database_id),
    -- last_user_lookup = MAX(U.last_user_seek),
    -- last_user_update = MAX(U.last_user_seek),
    total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)),
    create_date = MAX(D.create_date)
FROM 
    sys.databases D
-- JOIN 
--    sys.dm_db_index_usage_stats U ON (D.database_id = U.database_id)
JOIN 
    sys.master_files F ON (D.database_id = F.database_id)
GROUP BY 
    D.database_id;

Code 3: (also works)

SELECT 
    database_id = (D.database_id),
    last_user_lookup = MAX(U.last_user_seek),
    last_user_update = MAX(U.last_user_seek),
    -- total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)),
    create_date = MAX(D.create_date)
FROM 
    sys.databases D
JOIN 
    sys.dm_db_index_usage_stats U ON (D.database_id = U.database_id)
-- JOIN 
--     sys.master_files F ON (D.database_id = F.database_id)
GROUP BY D.database_id;

Possible duplicate of [Arithmetic overflow error converting numeric to data type numeric](https://stackoverflow.com/questions/4670903/arithmetic-overflow-error-converting-numeric-to-data-type-numeric) — Patrick Artner, Jan 31 '18 at 23:22
Answered first, searched SO for you afterwards. You might want to revisit [how to ask](https://stackoverflow.com/help/how-to-ask) . The first lines go like this: **Search, and research** _...and keep track of what you find. Even if you don't find a useful answer elsewhere on the site, including links to related questions that haven't helped can help others in understanding how your question is different from the rest_ - the question is your exact error message. — Patrick Artner, Jan 31 '18 at 23:24

score 2 · Accepted Answer · answered Jan 31 '18 at 23:16

a arithmetic overflow error converting numeric to data type numeric is thrown if you try to put too many numbers in too few spaces.

You have 8 digits with 2 decimals: xxxxxx.xx

With 2 tables, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) does not exceed 6 places before the . - with 3 tables the sum does exceed this and can no longer be put into it.

Increase it to DECIMAL(9,2) then DECIMAL(10,2) then DECIMAL(11,2) until it fits - or analyze your data how broad the field has to be.

thanks. now i noticed that joining another table changes the numbers. :-( — Dani-Br, Jan 31 '18 at 23:27

SQL Server is throwing arithmetic error when joining 3 tables

1 Answers1