This post is based on the stairway series at SQL Server Central called Stairway to Data, by Joe Celko.
Most people do not make a distinction between a number and a numeral in everyday conversation. A number is the abstraction represented by the numeral, which is a written symbol.
Tag Numbers
As Joe Celko says: “A tag number is simply a name written with digits. The first advantage of using digits instead of alphas is that they are linguistically neutral and there are simple rules for generating an unlimited set of such values. Tag numbers are often stored in strings rather than in an internal binary format. Arithmetic with tag numbers makes no sense at all — you cannot add bus route #123 to bus route #456 and get bus route #579. Why keep them in a computable format?”.
Ordinal Numbers
Joe says: “An ordinal number represents a position (first, second, third, … ) in an ordering. An ordinal number also implies a corresponding cardinal number (1, 2, 3, …), but knowing the ordinal position does not tell you about the cardinality of the whole set; in English, knowing that you are the third person in line for a promotion tells you that you have at least two competitors. It does not tell you if there is a fourth, fifth, .. , n-th candidate who were hoping for the job.”.
Obviously, ordinal numbers exist in a database when there is an ordering in the data. This usually means sequences of some kind, such as check numbers, serial numbers in a manufactured product and other things. But, arithmetic with ordinal numbers makes no sense.
Cardinal Numbers
The usual definition of a cardinal number is something which represents a quantity or magnitude. They are integers. Well, sort of. For a mathematician, here is where we get into countable infinite sets and other things. The numbers database people need to worry about are not so exotic.
The natural or “counting” numbers, are positive integers {1, 2, 3, ..}. Then the “whole” numbers are the natural numbers together with zero {0, 1, 2, 3, ..}. Finally, we have the “integers”, which are zero, the natural numbers and the negatives of the naturals {.. , –3, –2, –1, 0, 1, 2, 3, ..}, which is what the INTEGER data type stores in digital computers. The natural and whole numbers can be used in a database with a CHECK() constraint on an INTEGER column declaration. The constraints preserve the appropriate range, but they also help the optimizer. The shame is that most SQL programmers do not bother to use the constraints.
Zero, NULL and Math
Be careful when working with NULL in SQL Server. NULL propagates. What does this mean? What is NULL? The NULL in SQL is not a number. The usual description of NULLs is that they represent currently unknown values that might be replaced later with real values when we know something. The basic rule for math with NULLs is that they propagate. An arithmetic operation with a NULL will return a NULL. That makes sense; if a NULL is a missing value, then you cannot determine the results of a calculation with it.
However, the expression (NULL / 0) is confusing. The first thought is that a division by zero should return an error; if NULL is a true missing value, there is no value to which it can resolve and make that expression valid. However, SQL propagates the NULL and does not even issue a warning about division by zero when it appears as a constant in an expression. A non-NULL value divided by zero will cause an error, however.