Length of Numeric Columns

[PeterHegRT]

I'm puzzled about the space used to store numbers.

If you create the table...

create table testN (
cInt integer
,cNum10 numeric(10,0)
,cNum8_2 numeric(8,2)
)

and then do sp_help testN, you find that the integer takes 4 bytes, yet the numeric(10,0) takes 9, and the 8 with 2 dec places takes 5.
How is this space used?

- Peter

 

[mrdenny]

I'll refer you to BOL under "numeric data type, about numeric data type". In basic terms using the funky way that Microsoft stores the numbers within the row, this is how it works.

Basically:

Precision Storage Size
1-9 5 bytes
10-19 9 bytes
20-28 13 bytes
29-38 17 bytes

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com