Why SQL numeric precision is specified as the number of digits?

Question

I understand that this might be a loaded question. For example, in Sql Server 2008, doing DECLARE @someNum NUMERIC(10); means that the range of the variable will be limited by 10 digits. In other languages such as C, Java, etc. the standards for representing whole and fractional numbers are different. The range of the values is being sliced by multiples of bytes, and this is understandable, due to the way hardware works. A language could provide something that most hardware does not yet support, such as a 256-bit integer type, which will work fast on a 64-bit computer, if it is implemented correctly.

The numeric types in the databases seem to be not tied to underlying hardware. I understand that the values might need to be null-ablle, which can cost a bit or two, but why such arbitrary precision that does not seem to optimize storage or calculation speed?

Perhaps databases do not need to perform numeric computations often. Perhaps digit width of a column is a natural extension of character width. Perhaps SQL was supposed to be a non-programmers language (because it sounds like English :) )? Perhaps history took a weird turn, such as one that allowed the inferior VHS format to prosper.

Please help me understand this.

Answer 1

doing DECLARE @someNum NUMERIC(10); means that the range of the variable will be limited by 10 digits.

Actually, you're either very wrong in that assertion, or SQL server is hugely incompatible with other DB engines. Typically, you have:

• NUMERIC(precision, scale)
• NUMERIC(precision)

In the second case, scale is zero, i.e. you can only handle integers; precision is the maximum total number of digits with the specified scale (meaning integers in the second case).

By contrast, you seem to be reading numeric(10) as meaning "any number as long as there are 10 digits or less", for which there is no (best I'm aware anyway) SQL construct short of single/double float types (whose name differ per DB engine).

---

The numeric types in the databases seem to be not tied to underlying hardware.

Indeed, they're not. Numeric/Decimal is an arbitrary precision number, stored very much like a varchar of huge length (1000+ digits). You can actually use it to store 1e100 + 1 as an exact value. You'll get 1 followed by zeros, and finally a 1.

You sound like you need a float... The latter would store 1e100 when given 1e100 + 1, and would yield 1e100.

Answer 2

This is speculative, but I can think of a couple plausible reasons:

1. Relational databases use numbers as identifiers more frequently than as the objects of arithmetical operations. Efficiency of storage and comparison would be more important than fast arithmetic operations. Going from 16-bit right to 32-bit integers is a big jump and would result in needless file bloat (waste in a table = num numeric fields * num rows * number of unused bytes)
2. Transact SQL is a pretty old language, developed originally for 16-bit processors. 16-bit integers are pretty weak tea for any sort of commercial/business used, particularly in light of point (1). The original T-SQL would have needed a more powerful and flexible integers than natively provided. This may be driven by backwards compatibility.

Answer 3

I think your notion that a value in a column is like a variable is not accurate. SQL Server (and other databases) stores data in terms of pages (8 KB for SQL Server). Each page will have as many rows of data as will fit (plus some overhead). So SQL Server storage really doesn't care about each column type and stores raw bytes.

Check this link for more information
http://msdn.microsoft.com/en-us/library/cc280360.aspx

Answer 4

Fixed-point numeric types are not used for the same purpose as floating-point numeric types.

Sure, floating-point types (which every SQL database also has) are optimized for the machine hardware, but they are also imprecise. Every day we get to see another newbie who doesn't understand this (not to pick on anyone, that was just the most recent example I found).

You pay a hefty price for the performance and space benefits that a floating point gives you; in particular, you end up with something that can never ever be used in any system that requires exact quantities, such as a financial system. Go ahead and try using floats for your invoicing component; the accountants will kick you to the curb after the 10th time in a row that the control process fails.

Integers are great for precise storage of quantities, but pretty miserable when it comes to computation. We sold 5 units yesterday and 6 units today; you want the average? It's exactly 5, according to the system!

There are situations where you must use fixed-point precision, even if it's not perfectly optimized. Generally, those situations involve either money or precursors to money (like discount rates), although anything dealing with real-world quantities is usually better off as fixed point as well - weights and measures are another obvious example.

Oh, and by the way, that silly VHS comment at the end? Nothing but a myth. So let that be a double lesson to you: Things happen for a reason. Vapid platitudes such as "VHS won because of marketing" or "SQL was originally supposed to be for non-technical users" may sound quaint and charming but they are rarely the whole story and are often outright fabrications.