What is precision?

[OhioSteve]

I have MS Access experience, but I am just learning this product. While creating a table, I gave a field the "decimal" data type. I noticed a trait called "precision". The book says that its the number of digits on both sides of the decimal point. So 123.123 would have a precision of six.

Why isn't precision equal to field length? It seems like they should be synonyms.

 

[SQLBill]

123.123 is a precision (accuracy) of 6. That IS also the length. When you set the length of a VARCHAR you would set:

varchar(7) for 123.123 because you have to count the decimal point

but for decimal datatype you set:

decimal(6,3) because you don't have to count the decimal point. Decimal datatype automatically includes the decimal point and the combination of precision and scale sets where the decimal point is displayed.

I hope that made it clearer.

-SQLBill

 

[OhioSteve]

In the table design view, I set the length of a decimal field to five. I kept the length at five, and changed the precision three times. I set it to 2, 3, and 6. Each time I kept the scale at 2. Each time I saved the table and SQL server accepted my change.

If precision is the same as length, why didn't the length change automatically? Or why didn't I get an error message? If precision and length are not the same, then what is precision?

 

[SQLSister]

Precision is how many numbers on both sides of the decimal place can be stored in the field. Length is the length in bytes that SQL server reservers for the datatype in each record. They are not the same thing. If you look up in BOL, the overview of the decimal datatype you will see a chart wich will tell you how many bytes will be reserved for each group of precision lengths.
Basically a precison of 1-9 will reserve 5 bytes of space, 10-19 will reserve 9 bytes of space, 20-28 will reserve 13 bytes of space and 29-48 will reserve 17 bytes of space.

 

[OhioSteve]

Thanks, SQLsister. I think that I understand the distinction now. So perhaps for decimal fields I should focus on setting the precision, and let the machine decide how many bytes it needs.

What is BOL?

 

[SQLBill]

Books OnLine. The HELP feature that comes with SQL Server.

-SQLBill