Ten = Fifteen?

Question

Ten = Fifteen?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jamie Thomson SSChampion Points: 11805 More actions · Answer 1

Thanks Steve, you beat me to it. The Excel thing is a bug in the Excel driver. Its nothing to do with SQL Server. Discredit where discredit is due! Definately annoying though I'll grant you!

It is a bug however, and I think we all accept that products have bugs in them (god knows how many of mine are lying around undiscovered). Progress has actually been designed this way though which is what I find amazing.

-J

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

Actually, it's not the fault of Excel's driver. It's the fault of the ISAM that Excel uses. This problem preceeds Excel.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Ron-389664 Newbie Points: 7 More actions · Answer 3

I've been using SQL Server for a few years now, but have well over a decade of experience using databases with variable length data designs. Many of these enable you to enter data of any length in a field. (I'm sure that will appall those who are married to strong data typing.) In those systems the field length stored in the dictionary is only used as the length to appear in columnar output from queries. I'd point my finger at the ODBC driver or report writer that errors out instead of truncating or wrapping the longer data.

Will Strutts Mr or Mrs. 500 Points: 558 More actions · Answer 4

You should try PICK type databases or as they like to call themselves Multivalued Databases. There are no types in PICK. All data is characters of unlimited length. Hence, you can put character data in numeric fields. You won't know you have a problem with the data until you try using it in a formula. PICK uses a dictionary to describe each database item. Each item has a list of PICK attributes which are stored in the Master Dictionary and File Dictionary. Each data item has numbered attributes. Each numbered attribute describes a particular thing about the data. For instance, attribute 10 would describe the displayed length of the database field. Attribute 8 describes the source of the data. So Progress is not the only database with oddities. Try interfacing PICK with the outside world is real challenge too since each database field can have multiple values that don't necessarily line up with other field values with multiple values. When you are trying to interface PICK to the SQL world its real fun. That is what we have to do here at my company on a daily basis. Pulling data from the PICK world and then manipulating it the SQL world so that we can send it out to our customers.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 5

Sam and William have it right. Lot of us are spoiled by tools that have rigid structures and won't let you get away with much.

Another example was Embeded VB. All variables were of type Variant no matter how, or if, you declared them. You get errors at runtime if you try to use a variable that contains bad data. Those were the days before TRY/CATCH. You were stuck with ON ERROR GOTO. Yes, I said GOTO. Today my VB code is strongly typed.

Can you imagine how ticked I was at developers storing dates in Varchar(8) fields. "Hey, how come puting an ORDER BY on the SELECT doesn't work? You stored the dates how?" "Charles you can just CAST that in the ORDER BY." Or you can just used the fields the right way and save us all the confusion.

Weird? Here is one. There are 32 teeth in the adult mouth (33 if you have a supernumerary). Each tooth has a certain number of surfaces. One competitor stored sufaces in base 9 arithmetic. Base 9! We found that there are not more than 8 (they sometimes have different names) so we just bit mapped them into a byte (smallint).

ATBCharles Kincaid