|Lesson 3 || Specifying length, precision and scale |
|Objective || Specify the length, precision, and scale for the columns in your table. |
Specifying Length, Precision and Scale of DataType
Once you choose the datatype for columns in your tables, you can then specify their length, precision, and scale. However, you can specify
these attributes only for specific datatypes, as follows:
- Length: the length of text or binary data that is to be stored in the database. This attribute is valid for binary, char, nchar, nvarchar, nvarbinary, and varchar datatypes.
- Precision: the number of decimal places to the left and the right of the point. This attribute is valid for decimal and numeric datatypes only.
- Scale: the number of decimal places to the right of the point. This attribute is valid for decimal and numeric datatypes only.
If you try to insert data with a greater precision than is defined, the number of decimal places greater than the scale will simply be truncated.
For example, if you define a column with a scale of 2, inserting 10.238 will result in 10.23 being inserted.
In the next lesson, you will learn how and when to use Nulls.
Numbers are some of the most powerful attributes, because often, math is performed with them to get your client paid, or to calculate or forecast revenue. Get the number of dependents wrong for a person, and his or her taxes will be messed up. Or get your wife's weight wrong in the decidedly wrong direction on a form, and she might just beat you with some sort of cooking device (sad indeed).
Values are generally numeric, such as the following examples:
- Monetary amounts: Financial transactions, invoice line items, and so on
- Quantities: Weights, number of products sold, counts of items (number of pills in a prescription bottle), number of items on an invoice line item, number of calls made on a phone, and so on
- Other: Wattage for light bulbs, size of a TV screen, RPM rating of a hard disk, maximum speed on tires, and so on
once we dug deeper into invoicing and payments, but I specifically avoided having monetary values to keep things simple in the model.
Numbers are used all around as attributes and are generally going to be rather important (not, of course, to minimize the value of other attributes!). They're also likely candidates to have domains chosen for them to make sure their values are reasonable. If you were writing a package to capture tax information about a person, you would almost certainly want a domain to state that the count of dependents must be greater than or equal to zero. You might also want to set a likely maximum value, such as 30. It might not be a hard and fast rule, but it would be a sanity check, because most people don't have 30 dependents (well, most sane people, before, or certainly not after).
Domains do not have to be hard and fast rules at this point (only the hard and fast rules will likely end up as database constraints, but they have to be implemented somewhere, or users can and will put in whatever they feel like at the time). In our example paragraphs, there's one such attribute:
The client manages a couple of dental offices.
The question here is what attribute this would be. In this case, it turns out it won't be a numeric value, but instead some information about the cardinality of the dental Office entity. There would be others in the model.