The Number that shouldn't be a number
- RonKyle - Thursday, December 13, 2018 12:41 PM
Probably to replace "FLOAT" when it comes to calculations such as mortgage interest calculations. One of the keys to such calculations is to never store the results formatted for human consumption. Whoever did this is likely unaware of the truncation to just 6 decimal places under certain conditions, especially the conditions created by multiplication.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Thursday, December 13, 2018 1:39 PM
I'm not sure I understand the answer. Are you saying that decimals to this place are necessary for interest calculations? For money I've used decimal(19,4) thinking four decimals was more than enough. Is that sound?
- RonKyle - Thursday, December 13, 2018 5:31 PM
Agreed. I may not have a real handle on this, but it seems to me as if rounding to cents anyway, even interest calculations don't need a whole lot of decimal places. If I'm not mistaken, once you get to the second place beyond the one cent point, can anything else change the rounding? The only place i seem to use more decimals is when I'm trying to match to an existing number, such as in reconciling my investment transactions where I need to reconcile to the transaction total the broker presents by entering more decimals until the totals match. Probably the only need for more accuracy would be if you are matching to an existing number that already has an accuracy point beyond cents. General rule might be two decimals beyond that point.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) ) - skeleton567 - Friday, December 14, 2018 7:23 AM
It usually has to be looked at based on your circumstances. We have had need for more than expected precision in a number of places, more than just for interest calculations.
For example, in insurance you tend to price things "per year": however things get interesting after that since not all polices are annual, change to a policy might happen part of the way through the year, or your accounting only gets to recognize premium over time. So you end up having to "prorate" (i.e. convert the price from "per year" to "per day"), and you still need the numbers to tie in no matter which of the two you use.
Extend that even a little bit further, and you might need to in some cases turn that "per building per year" price into a "per square foot per day" price, on a warehouse size building. You'd better be able to carry a LOT of extra precision or your "rounding errors" quickly climb into 100's of thousands or higher.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
even in this case (breaking down to daily or even hourly costs) you can not invoice more than rounded cents. So more precisition will only prevent rounding errors, if you have to add many mini prices and wants to round only the totoal sum.
On the other hand I worked for a telephony company before, where the telephone calls will be priced at minute level, but even they did not calculate with more than 4 decimal places.
PS: 6-fold quotes when you refer to the answere just above are very good to read and increases the clarity a lot!
God is real, unless declared integer.
- Fully agreed on everything (including the ability to only quote the latest post 😛). In this case it was more of a need to identify which values are "final" and which ones might need to be interim and carry more significant digits to support the final answer.
It kind of brings us back to Gail's original point: understand what the data is supposed to be used for and don't just put your blinders on when deciding what data types to use.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
....and Slashdot's quote for the day is:
God is real, unless declared integer.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses - PHYData DBA - Thursday, December 13, 2018 9:07 AM
No sir. Don't mistake an ad for information as the gospel. That's only a part of what the standard actually says.
Section 4.1.2.2 of the standard clearly states that the YYYY-MM-DD format is an allowable "extended" format and that the "basic" format is actually YYYYMMDD.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I'm really surprised that no one mentioned Microsoft doing this (date and/or time as INT) in the msdb database.
SELECT * FROM dbo.sysjobhistory
Complete pain to convert that to a time format to do any sort of reporting on when jobs ran.
-
Thank you for the article. I feel aiming for the highest data quality somehow got lost by developers and staff in general. Having an area code of VARCHAR(3) and phone number of VARCHAR(7) goes a long ways in enforcing data integrity. Just taking a moment to increase data integrity I have been focusing on:
- Primary keys are identity columns. When I started about 30 years ago they found with Corporate Mergers and other factors Identity Columns offer the most flexibility.
- With the move of Identity columns being primary keys, it is extremely important to have a business key/alternate key to have logical uniqueness between rows in a table.
- Optional vs Mandatory attributes are extremely important (null/not null). This has guided me in the right direction multiple times in process/programing logic. If in the end an attribute value of null violates data integrity then it should be reflected in the database.
- Normalize data. This prevents tables from having a lot of columns that leads to forward fetches and other space issues. Most importantly Foreign Keys are a great asset to data integrity.
- Default values. Having a timestamp on create_date of a row, true/false attribute, goes a long ways in data integrity and the flexibility for interfaces to not worry about it.
- Effective/Obsolete date. This goes a long way in making ones data temporal in nature.
- Check Constraints. Like Foreign Keys, the ensure a data column only accepts an enumerated set of values.
These 7 simple principles alone go a long ways in having an organization get out of the reactive habit of data fixes and more time on the water for water skiing or golfing.
-
"On the plus side, storing telephone number as int uses less storage than a varchar. Ok you won't need to use any numeric functions on it, but you probably won't need to use any string functions on it either. I don't think this is as bad as putting a date into a varchar."
Nope,
that's not a "plus side to doing it wrong", it's just doing it wrong, plain and simple. So here's an example. Your phone number starts with a zero, right? Maybe more than one (as can be the case in ROI). So you store your telephone number, then you take it out again. Actually, no you don't, because when you stored it, you changed the value to something else, because any leading zeros were removed. So if you're using this for the likes of automatic dialling, it just ain't going to work - is it. But that's ok. You saved a couple of bytes in your 3Tb database, hoorah, the sales team will be delighted to find that out. Because it's a *number* right?
Nope, wrong. It isn't. Your domestic numbers generally (in a number of countries, I don't know about all) start with a zero, so your int simply can not store that value. furthermore, the standard format for a phone number is +[Country Code] [(9) - domestic start character] [x...x]. Your int simply can not store that value.
I would argue that competent design should have storing relevant data accurately and in a manner which allows the data to be retrieved in the appropriate format without corruption as a core requirement.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. - andrew gothard wrote:
"On the plus side, storing telephone number as int uses less storage than a varchar. Ok you won't need to use any numeric functions on it, but you probably won't need to use any string functions on it either. I don't think this is as bad as putting a date into a varchar."
Nope, that's not a "plus side to doing it wrong", it's just doing it wrong, plain and simple. So here's an example. Your phone number starts with a zero, right?
Ah... be careful now. That's absolutely not possible.
A typical phone number on the NANP (North American Numbering Plan) is based on NPA (Area Code), NXX (Exchange), and XXXX (Line number)
The letters identify the digits that can be used for each.
https://www.voip-info.org/npa-nxx/
The "N" means the digits 2 thru 9.
Breaking up a phone number as...
... means that the first column would contain the area code in the NPA format and the second column would contain the exchange and line number in the NXXXXXX format.
Neither column would ever start with 0 or 1.
Despite my misgivings on this subject years ago, I wouldn't fault anyone for doing such a thing with phone numbers.
Integer Date and times is a whole 'nuther story. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I worked with one company that stored phone numbers as integers. Yes. Plural. They broke up the 10-character US phone numbers into separate AreaCode (first 3 digits), Exchange (Next 3 digits), and LineNumber (final 4 digits) fields. While the AreaCode and Exchange fields are designed to never start with a leading 0, the LineNumber frequently did....so they had to convert to strings AND occasionally left pad with zeroes to generate a full 10-character phone number
- mwyss wrote:
I worked with one company that stored phone numbers as integers. Yes. Plural. They broke up the 10-character US phone numbers into separate AreaCode (first 3 digits), Exchange (Next 3 digits), and LineNumber (final 4 digits) fields. While the AreaCode and Exchange fields are designed to never start with a leading 0, the LineNumber frequently did....so they had to convert to strings AND occasionally left pad with zeroes to generate a full 10-character phone number
A little integer math would have run more quickly.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just as peanuts are not nuts, they are legumes, peanuts do not have exocarp, mesocarp, and endocarp, phone numbers are not numbers. Phone numbers are not mathematical objects as phone numbers do not have any arithmetic properties. Storage concerns are irrelevant if the content does not fit.
😎
Another way of thinking of phone numbers is thinking of an identifier that is location, device and service specific, is the call going through the local PSTN, HLR, VLR or an IP gateway? Whichever route, different terms and conditions apply! There are many different number plans, many of which make this more contorted.
When storing a data entity, all attributes and properties must be considered!
Viewing 15 posts - 106 through 120 (of 121 total)
You must be logged in to reply to this topic. Login to reply