SQL Server NUMERIC/DECIMAL precision vs storage

Considering what MSDN states regarding SQL Server 2008 R2 storage of NUMERIC/DECIMAL precision.

Precision of 1 to 9 is 5 bytes

Precision of 10 to 19 is 9 bytes

So if my business case logically requires a data type with 2 decim开发者_开发百科al places and a precision of 5 digits it makes no actual performance or storage difference if I define it as NUMERIC(5, 2) or NUMERIC(9, 2).

One considering I'm intentionally ignoring is the implied check constraint as I'd most likely put an actual check constraint on the column limiting the actual allowed range.

Does this make a difference when it comes to indexes, query performance or any other aspect of the system?

---

Numeric(5, 2) allows numbers up to and including 999.99. If you try to insert 1000.0 into that, you'll get an arithmetic overflow.

Numeric(9,2) allows numbers up to and including 9 999 999.99

Bear in mind that if you plan to ever sum this value, allow extra space, otherwise you'll get an overflow or you'll need to do an explicit cast.

They take up the same number of bytes. Since they're the same storage size, they're the same in the data page, in memory, in indexes, in network transmission, etc.

It's for this reason that I usually work out what size number I need to store (if using numeric), then increase precision (and maybe scale) so that I'm just below the point where the storage size increases. So if I need to store up to 99 million with 4 decimal places, that would be numeric (12,4). For the same storage, I can have a numeric (19,6) and give some safe space for when the business user announces that they really do need to store a couple billion in there.