Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Stairway to Database Design STEP 6: Procedure Bodies Expand / Collapse
Author
Message
Posted Wednesday, September 21, 2011 9:39 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:49 AM
Points: 738, Visits: 1,307
Matthew Kaney (9/21/2011)
Old Hand - I dealt with a database like that too. Each table had two keys, the primary incrementing identity field, and then the company's code # field. e.g. ProductKey, ProductCode. All of the code fields, and ever table had them, were char types and the data was right aligned with spaces. I couldn't figure out why they did this and then they told me well, if you use alphanumeric codes, one with at least one letter, it left aligns them so the program uses this to determine if the field is alphanumeric or not.

???

What a royal pain in the butt it was to get that ERP integrated with other systems.


Matt, I feel and know, your pain. Its funny what some people did in the past, to make things work. I suppose I should be more lenient, because back in the day that's what worked and there wasn't anything else on the scene to suggest that their poor way of doing things could have been done better.


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1178786
Posted Wednesday, September 21, 2011 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 22, 2011 1:18 AM
Points: 6, Visits: 66
I'm sure we will look back upon today with the same bewilderment. Until then, at least it provides plenty of extra work :)

Just thought of another one.. Old date format in IBMDB@ CYYMMDD.. The way you converted it from doing: INPUTDATE + 19000000, it stores it as an integer. The concept of a date was a formatting issue, not a datatype, talk about separating the data and presentation layers sheesh.
Post #1178879
Posted Wednesday, September 21, 2011 3:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 1,945, Visits: 3,184
>> recently SQL added a new type: DATETIME2. <<

Whi9chis what ANSI/ISO SQL calls TIMESTAMP ..


>> People may have old applications lying around that use DATETIME as the interface format for dates if no conversion is done, and if DATETIME is the interface format, no conversion in the database means that it's also the storage format. Suppose I now decide to add to my database some data about things which occurred at dates that can't be expressed in the DATETIME format? <<

Well, you are screwed But in commercial and most scientific databases, we need just DATE values and sometimes DATETIME2(0) – date time to whole seconds. The conversion to the new data types from the old DATETIME is going to be a screaming pain. The fun part will be going thru code to look for all the ways we used to trim the TIME fields from DATETIME by setting them to '00:00:00.00' across all values. I liked the “floor-float” method because it was so fast in Sybase in the 1970's; other people like the CONVERT() from Microsoft; then there the homemade tricks. Ugh!

>> Often it will be better to provide conversion in interface procedures (in fact on the principal of modularity I should never have allowed the application to constrain the storage format in the database in the first place - the interface representation should never have been the storage format, it should have been something reasonably universal, like ISO 8601, in the first place. <<

Agreed; “No matter how far you have walked down the wrong road, turn back.” ---Turkish proverb.

>> (a) for many scientific applications real and float are extremely useful (although I find it irritating that we don't have a 128-bit float in SQL) <<

Actually, ANSI/ISO SQL does have DOUBLE PRECISION. What I miss is an old DG Algol convention of having the data type modifiers LONG and SHORT; i.e. a LONG INTEGER is twice as many bit as a mere INTEGER and a SHORT INTEGER is half as many bits. I could get 255 16-bit words of INTEGERS and REAL before it went bust. Oh, it ran like frozen crap. But it had more decimals that a Cyber.

The real problem with FLOAT/REAL in SQL is that SQL implementations to not have the corrections for floating point math.

>> (b) it's 15 years since last I had no floating point hardware in my desktop, and those desktops were all run of the mill commercial machines, not scientific workstations (maybe that statement in the article was also an artifact of its epoch?). <<

That might be out of date for the desktop; you need it for video games :) I am not sure about commercial mainframes. The big add-on used to be BCD hardware because so much commercial data used to be in that format.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1179081
Posted Thursday, September 22, 2011 4:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
CELKO (9/21/2011)
>> (a) for many scientific applications real and float are extremely useful (although I find it irritating that we don't have a 128-bit float in SQL) <<

Actually, ANSI/ISO SQL does have DOUBLE PRECISION.

But DOUBLE PRECISION is a synonym for FLOAT(53), so it's a 64 bit float not a 128 bit float.

What I miss is an old DG Algol convention of having the data type modifiers LONG and SHORT; i.e. a LONG INTEGER is twice as many bit as a mere INTEGER and a SHORT INTEGER is half as many bits. I could get 255 16-bit words of INTEGERS and REAL before it went bust. Oh, it ran like frozen crap. But it had more decimals that a Cyber.

Much more fun was the IBM 1620, which was very much a BCD machine. It could have up to 100 bcd digits in the mantissa of a floating point number. And fixed point numbers of unlimited (except by availability of store) size.
The real problem with FLOAT/REAL in SQL is that SQL implementations to not have the corrections for floating point math.

But I would normally expect that to be done in the hardware. Or in any software floating point implementation that claimed to conform to the standard - IEEE 754 is quite explicit about how rounding is to be controlled and how errors are to be indicated to the software. It may be that the SQL standard is deficient and fails to allow the rounding and error handling to be properly controlled (eg specifying whether error flags such as "inexact" are to be tested or not - the hardware or software FP operation will set them but if the SQL software always ignores them they won't be much use); if so, it's a pity and needs to be fixed. It's also worthy of note that the IEEE standard includes a decimal floating point format, which greatly reduces rounding issues (one can represent dollar amounts exactly in decimal floating point, even amounts like 0.01 dollars which couldn't be represented exactly in binary floating point unless the unit were changed to be the cent instead of the dollar, so all rounding on input conversion is elimnated) - why doesn't SQL offer a decimal floating point type? I wonder whether anyone is looking at upgrading the SQL standard in the light of the 2008 revision of the floating point standard?

>> (b) it's 15 years since last I had no floating point hardware in my desktop, and those desktops were all run of the mill commercial machines, not scientific workstations (maybe that statement in the article was also an artifact of its epoch?). <<

That might be out of date for the desktop; you need it for video games :) I am not sure about commercial mainframes. The big add-on used to be BCD hardware because so much commercial data used to be in that format.

I think that until the late 1980s floating point hardware was only provided in "scientific computers" and "graphics workstations"; but from about 1989 onwards it would have been regarded as strange to call a product a general purpose computer if it didn't have hardware floating point.


Tom
Post #1179799
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse