I see no reason at all to waste 2 bytes per row storing dashes. 2B rows = 4B bytes totally wasted.
Are these the same two bytes we saved by dropping the century from dates back in the Y2K days? I'm going to argue for the dashes. The design principle that you want to store data the way it is used and use it the way it's stored. When you see 2019-03-19, you know that it's a date. When you see 252-77-6688, you know that it's a Social Security number. Likewise, 23:00:00 Hrs is clearly a time. The cost of a couple of pieces of punctuation is negligible these days, but the cost of an error in misreading the data is not negligible.
So you're saying that we should store dates as '2019-03-19'? That's just not done. Do we need to store the blanks in credit card #s too, which are usually printed on cards as nnnn nnnn nnnn nnnn to make them easier to read.
The display has nothing to do with how data is stored.
The Y2K method only saved one byte, and, yes, it was the correct decision at the time.
You love living in a completely theoretical world, where the logical design is never converted to physical design. Back in the real world, we have to follow the normal process of converting a logical design to a physical one, accepting comprises along the way.
"He who loves practice without theory is like the sailor who boards ship without a rudder and compass and never knows where he may cast." -- Leonardo da Vinci
My first paying IT job was in 1965. That's over 50 years in this trade; my consolation is that I got old by not dying. 🙂 Please don't tell me that I'm all theory. I was a code monkey for over 25 years. However, if you want to yell at me for being all "ANSI/ISO standards", "mathematical correctness", "best practices in the industry", "decades of research behind me" and pedantic as all merry hell, I will agree.
Basically, at some point, people were paying me to fix the messes that have been made by People like you, who think that it's okay to compromise on things. You have given me most of my later SQL consulting work. I fix bad designs and I'm expensive. But it's cheaper than living with a bad design of somebody wanted to save the cost of some storage, or kludge a program to cover a bad design..
I also agree that display has nothing to do with storage. Those things we show decimals are stored as binary. Of course we don't know if it's high-end or low-end storage or maybe on a it old Russian three valued machine. However, I like Brent's law that data should be stored the way it's used and use it the way it's stored. This means that a human being can read it, that pattern matching is a lot easier, check digits are easier to compute, etc. It's important to pick the level of abstraction (physical hardware, programming language, particular product, etc.) for your design. In the database, deciding to optimize at the current hardware/software level is always wrong.
I wrote one of the first articles on the Y2K problems in Information Systems News when I had a regular column. It was all too familiar with what would happen we got to the cusp of 1999 – 2000 and had to decide which decade ambiguous dates would fall. This decision was totally dependent on your data. You would be surprised by the percentage of errors you got trying to add the century. We have a lot of people that live over 90. Now tell me if a birthdate year is 19xx or 20xx in the hospital that treats both geriatrics and pediatrics. Whoever thought this was "a correct decision at the time" bought me a car at the time I got through cleaning of their data. Our slogan was "another day, another K, and we mean take-home pay" for jobs like this.
And paying for the extra RAM and disks back in the day would have bought 2-5 houses.
And it's not me you're doing re-work for. I do a true logical design, to the constant annoyance of many others on this site, who insists it's not needed (you can just start with an "identity" column and your "table" "design" issues are solved many seem to think). When things change sufficiently, you can go back to the logical design and convert it to a newer physical design.
In the database, deciding to optimize at the current hardware/software level is always wrong.
... Ridiculous. There's literally nothing else you can do. You can't make something that only runs 20 years from that timee (well, those of us who don't just follow theory can't). We need things that work now.
Again, how data is physically stored is 100% irrelevant to how it's displayed, period. Humans don't read binary, and all modern commercial data is stored that way (afaik).
Yes, anyone that insists on storing the dashes within a SSN is just not thinking clearly. What happens as the population grows when it goes to 10 chars? And they decide as a result to format it differently? You're the one that is providing future make-work, and at a loss today too! The Y2K thing saved big resources at the time. You're wanting to waste bytes now and make it harder to refactor in the future, the perfecta of poor design and practice.
Any times I've dealt with contractors they've been virtually useless. Smart people, many, but useless for actually getting anything done. They do produce directories full of "recommendations" and "documentation", but nothing you can actually use to engineer a working system.
The people that compromise when needed are the only people who ever produce anything. If MS had waited until Windows was perfect, we'd still be waiting on it. If IBM had waited until their relational implementation was perfect, System R and everything that followed would not have already happened.
It's like saying "Don't reinvent the wheel." The wheel's been reinvented hundreds of times, or we'd all have wooden wheels with no rims.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.