The Daily WTF: Curious Perversions in Information Technology

2008-02-20 Reply Admin

Someone You Know:
akatherder:
Someone You Know:
Occasionally one does need more than one character for a middle initial. At my last job, there were two employees who had the same exact first, middle, and last names. Supposing that their names were John Andrew Smith and John Andrew Smith, it was common for official reports to refer to them as John A1 Smith and John A2 Smith. (Fortunately the headaches stopped when John A2 Smith got married and got a hyphenated name.)

Are you posing that as a WTF or are you seriously posing that as an acceptable way to solve the problem of two people having the same name?

That depends on which problem you're referring to. In this case, the name was not being used as a database key or anything; the system was perfectly capable of treating two people with the same name as different entities. If I recall correctly, a unique employee ID number was used for this purpose.

The problem only arose when schedules and other human-readable reports needed to be generated. These guys needed to know which one of them was supposed to work which shift, and so did other people, like the supervisors assigned to those shifts. It was decided that a number would be appended to the middle initials; the guy with more seniority got #1. This organization had a staff of around 250, so the odds that this sort of issue would occur often enough to merit spending more effort on it were judged to be acceptably low.

If you don't consider this an acceptable solution to this problem, please suggest something better.

Human names are not identified. You can get two humans with name John. So this why you have national identifiers (or SSN in USA).

2008-02-20 Reply Admin

MiddleInitial NVARCHAR(255)

Only the necessary number of characters is stored with a varchar. That number (255) can be anything, and it will still only use the minimum number of bytes necessary to store the data.

This is not a wtf by any means.

2008-02-20 Reply Admin

What WTF? All I read was a lame ad for something called Rivendell

EvanED · 2008-02-20 Reply Admin

DeLos:
Flat files are much smarter to use. You can store more data on your drive if you use flat files because they are so thin you can stack them on top of each other.

I am glad to see this is a featured comment, because it totally deserves it 100%. Props, sir. (Or ma'am, if that applies.)

Someone You Know:
That depends on which problem you're referring to. In this case, the name was not being used as a database key or anything; the system was perfectly capable of treating two people with the same name as different entities. If I recall correctly, a unique employee ID number was used for this purpose.
The problem only arose when schedules and other human-readable reports needed to be generated. These guys needed to know which one of them was supposed to work which shift, and so did other people, like the supervisors assigned to those shifts. It was decided that a number would be appended to the middle initials; the guy with more seniority got #1. This organization had a staff of around 250, so the odds that this sort of issue would occur often enough to merit spending more effort on it were judged to be acceptably low.

If you don't consider this an acceptable solution to this problem, please suggest something better.

Personally, I think that the senior employee should have requested that his name be changed to "Batman" for those purposes. :-)

Tei:
Human names are not identified. You can get two humans with name John. So this why you have national identifiers (or SSN in USA).

So you're saying that the schedules should say "Employee #24601, your hours are ..." instead of a name? How depersonalizing. As was described, the names WEREN'T identifiers for anything from the computer end, just at the computer - human interface.

(BTW, as has been discussed on this site many times, SSNs and other national IDs are also a poor key, for a number of reasons.)

Minos · 2008-02-20 Reply Admin

My company's ERP, while running on SQL Server, has a database schema with lots of holdovers from its days on IBM mainframes. Thus, field names are all 6 characters or less. All the fields in a table share a common 2-letter prefix; for example, the Address Book table has fields that all start with "AB". The remaining 4 characters identify the field in a cryptic manner that reminds me of old 8.3 DOS filenames.

Going back to the Address Book example, it stores the name of the last user to modify the record. That field? "ABUSER". So all of our customers, employees, suppliers, and all the other entities we have an address for have an ABUSER.

2008-02-20 Reply Admin

EvanED:
So you're saying that the schedules should say "Employee #24601, your hours are ..." instead of a name? How depersonalizing.

Depersonalizing? A Unique ID is the most personal thing possible! John Smith would have one and John Smith 2 would have a different one, while the name 'John Smith' itself could refer to either of them. I'd be happy to have a name unique in the whole world!

Yours truly,

['{6BE6761D-F5DE-4C49-BCEF-B6B0E9B2A798}']

KattMan · 2008-02-20 Reply Admin

GrandmasterB:
MiddleInitial NVARCHAR(255)

Only the necessary number of characters is stored with a varchar. That number (255) can be anything, and it will still only use the minimum number of bytes necessary to store the data.

This is not a wtf by any means.

You are only half right. The database still reserves the 255 characters. It has to because records all have to be the same length in order to do proper offset calculations. The difference is that with a char you get all 255 characters then a terminator, with a varchar you get the terminator without all the extra padding. So no you are not actually saving space by saying varchar(255), you just don't have to return all of it if the data does not need it, thereby saving bandwidth instead. Other fields like ntext actually only reserve 8 bytes in the actual data table to hold a beginning pointer to the data in a separate file.

akatherder · 2008-02-20 Reply Admin

Someone You Know:
That depends on which problem you're referring to. In this case, the name was not being used as a database key or anything; the system was perfectly capable of treating two people with the same name as different entities. If I recall correctly, a unique employee ID number was used for this purpose.
The problem only arose when schedules and other human-readable reports needed to be generated. These guys needed to know which one of them was supposed to work which shift, and so did other people, like the supervisors assigned to those shifts. It was decided that a number would be appended to the middle initials; the guy with more seniority got #1. This organization had a staff of around 250, so the odds that this sort of issue would occur often enough to merit spending more effort on it were judged to be acceptably low.

If you don't consider this an acceptable solution to this problem, please suggest something better.

At a bare minimum, appending the number to their first or last name would be preferable to breaking the datatype and ignoring all reasonable assumptions about a field named "MiddleInitial".

That's like having a field named StateAbbreviation and making it 3 characters.

AL = Alabama AL2 = Alaska //bypassing AK workaround

2008-02-20 Reply Admin

I found this gem of a field in a database table for storing users' names:
MiddleInitial NVARCHAR(255)

The Real WTF is that the code assumes that initials always in the middle. I know several people who have a leading initial in their name. I also know someone who uses a name that matches up with no part of his legal name (for reasons that I suspect have to do with the whims of parents rather than anything more nefarious).

The best way to store someone's name in a database is not as 'first name', 'middle initial', 'last name' but rather as 'formal name', 'informal name'. It's the only way you can capture the complexity of reality in a sane way. (And no, 1NFers, you're not duplicating data.)

2008-02-20 Reply Admin

I do have two middle initials. Like for real.

2008-02-20 Reply Admin

KattMan:
akatherder:
Someone You Know:
Occasionally one does need more than one character for a middle initial. At my last job, there were two employees who had the same exact first, middle, and last names. Supposing that their names were John Andrew Smith and John Andrew Smith, it was common for official reports to refer to them as John A1 Smith and John A2 Smith. (Fortunately the headaches stopped when John A2 Smith got married and got a hyphenated name.)

Are you posing that as a WTF or are you seriously posing that as an acceptable way to solve the problem of two people having the same name?

Well it is so much better than the way a lot of health insurance companies deal with twins. Either one claim is always denied because they feel you are double claiming, or one child is "born" a month later.

Any parent who has twins and gives them the exact same name should be taken out the back and shot :P

2008-02-20 Reply Admin

Someone You Know:
akatherder:
Someone You Know:
Occasionally one does need more than one character for a middle initial. At my last job, there were two employees who had the same exact first, middle, and last names. Supposing that their names were John Andrew Smith and John Andrew Smith, it was common for official reports to refer to them as John A1 Smith and John A2 Smith. (Fortunately the headaches stopped when John A2 Smith got married and got a hyphenated name.)

Are you posing that as a WTF or are you seriously posing that as an acceptable way to solve the problem of two people having the same name?

That depends on which problem you're referring to. In this case, the name was not being used as a database key or anything; the system was perfectly capable of treating two people with the same name as different entities. If I recall correctly, a unique employee ID number was used for this purpose.

The problem only arose when schedules and other human-readable reports needed to be generated. These guys needed to know which one of them was supposed to work which shift, and so did other people, like the supervisors assigned to those shifts. It was decided that a number would be appended to the middle initials; the guy with more seniority got #1. This organization had a staff of around 250, so the odds that this sort of issue would occur often enough to merit spending more effort on it were judged to be acceptably low.

If you don't consider this an acceptable solution to this problem, please suggest something better.

How about an 'Nickname' or 'Alias' field?

2008-02-20 Reply Admin

Dunno about your part of the world, but banks around here are often offline due to "computer problems" -- the database servers crash often enough that the banks have instituted training days where they act as if the system is down (for normal transactions), even when it isn't. They don't announce these events, and they don't post notices of server crashes to the website, and they're not going to admit that they have corrupted data unless they absolutely have to.

The real WTF is the idea that someone would use a bank's policies as a guide to software best practices. Banks externalize almost all of their costs; they have no incentive (anymore) to do anything right, as they get your money regardless.

Databases are fine, if you take them offline before backing them up, back them up regularly, restore them routinely to verify that the backup actually is working, and keep a DBA on hand for when the inevitable emergency arises. Flat files may not be appropriate, but databases aren't an automatic win, except for DBAs.

2008-02-20 Reply Admin

a guy who had been gang pressed into fixing problems

I think it’s “press-ganged”. I don’t want to imagine what gang pressing is.

FredSaw · 2008-02-20 Reply Admin

pauldwaite:
a guy who had been gang pressed into fixing problems

I think it’s “press-ganged”. I don’t want to imagine what gang pressing is.

I'm thinking, similar to acupressure as opposed to acupuncture.

2008-02-20 Reply Admin

KattMan:
GrandmasterB:
Only the necessary number of characters is stored with a varchar. That number (255) can be anything, and it will still only use the minimum number of bytes necessary to store the data.
This is not a wtf by any means.

You are only half right. The database still reserves the 255 characters. It has to because records all have to be the same length in order to do proper offset calculations. The difference is that with a char you get all 255 characters then a terminator, with a varchar you get the terminator without all the extra padding. So no you are not actually saving space by saying varchar(255), you just don't have to return all of it if the data does not need it, thereby saving bandwidth instead. Other fields like ntext actually only reserve 8 bytes in the actual data table to hold a beginning pointer to the data in a separate file.

Didn't we just go over the whole "making ridiculous claims with undue confidence" thing?

Any database that actually does such a thing should not be used by anyone, ever.

2008-02-20 Reply Admin

The "beige box" part can be fixed with paint, stickers, or, in a pinch, duct tape.

Thalagyrt · 2008-02-20 Reply Admin

Alan:
Yup, using a length of 255 for a middle initials field is stupid.
Regards

Alan Q. T. Z. D. I. B. I. W. K. L. S. D. W. Z. Q. R. R. G. O. A. P. Ú. Q. C. Y. S. L. Q. C. F. C. D. R. E. M. W. L. H. X. Í. C. L. Q. C. F. I. W. A. L. S. S. S. S. S. S. É. L. E. E. A. O. D. P. Ú. Q. C. Y. S. L. R. V. M. W. L. D. T. N. Ú. U. R. D. D. A. L. W. C. Brown

Kudos to anyone who expands all of those letters into names and comes up with the most outrageous name ever.

2008-02-20 Reply Admin

Given the number of times I've gone into the bank and either had to have my transaction handled in some manual mode or not been able to complete a transaction at all because "the computer is down again", I'm somewhat inclined to side with "Jerry" on the database issue.

I'll take my tongue out of my cheek now.

2008-02-20 Reply Admin

KattMan:
GrandmasterB:
MiddleInitial NVARCHAR(255)

Only the necessary number of characters is stored with a varchar. That number (255) can be anything, and it will still only use the minimum number of bytes necessary to store the data.

This is not a wtf by any means.

You are only half right. The database still reserves the 255 characters. It has to because records all have to be the same length in order to do proper offset calculations. The difference is that with a char you get all 255 characters then a terminator, with a varchar you get the terminator without all the extra padding. So no you are not actually saving space by saying varchar(255), you just don't have to return all of it if the data does not need it, thereby saving bandwidth instead. Other fields like ntext actually only reserve 8 bytes in the actual data table to hold a beginning pointer to the data in a separate file.

That's what I love about this site. You find out who the TRUE WTF idiots are.

Daniel Beardsmore · 2008-02-20 Reply Admin

Khazwossname:
Alan:
Regards
Alan Q. T. Z. D. I. B. I. W. K. L. S. D. W. Z. Q. R. R. G. O. A. P. Ú. Q. C. Y. S. L. Q. C. F. C. D. R. E. M. W. L. H. X. Í. C. L. Q. C. F. I. W. A. L. S. S. S. S. S. S. É. L. E. E. A. O. D. P. Ú. Q. C. Y. S. L. R. V. M. W. L. D. T. N. Ú. U. R. D. D. A. L. W. C. Brown
(Silly party)

With all such problems, where do you define a sane limit? I think my English teacher at school had about six middle names, and didn't some poor girl in the US get one first name for every letter in the alphabet? So, what should we set as a limit -- 42?

(Assuming this is anything close to a sensible way to store names at all, which it isn't.)

2008-02-20 Reply Admin

Cultural imprecision again (sigh) The middle initial: One for an odd number of names, two for an even number of names.

robzyc · 2008-02-21 Reply Admin

Is anyone else having problems trying to get through to the banks IT department?! I keep telling them that I have discovered from a well-informed source that they should not be storing my data in a database, but a flat file, and I keep getting disconnected?

2008-02-21 Reply Admin

Thalagyrt:
Alan:
Yup, using a length of 255 for a middle initials field is stupid.
Regards

Alan Q. T. Z. D. I. B. I. W. K. L. S. D. W. Z. Q. R. R. G. O. A. P. Ú. Q. C. Y. S. L. Q. C. F. C. D. R. E. M. W. L. H. X. Í. C. L. Q. C. F. I. W. A. L. S. S. S. S. S. S. É. L. E. E. A. O. D. P. Ú. Q. C. Y. S. L. R. V. M. W. L. D. T. N. Ú. U. R. D. D. A. L. W. C. Brown

Kudos to anyone who expands all of those letters into names and comes up with the most outrageous name ever.

As a side challenge, guess what my actual middle initial is.

DOA · 2008-02-21 Reply Admin

blah:
"You should be using a flat file system. In all my years working in IT text files are the only thing that have stayed the same".
What can you say to that? Really?

"So has binary. Let's write everything in machine code." :)

DOA · 2008-02-21 Reply Admin

clevershark:
lurrrve:
When I asked my boss about using source control, he said it was a bad idea because developers sometimes work on the same files and version control doesn't handle that well.

I know that "LOL" is a cliche, but this actually made me laugh out loud. Good thing I'm working from home today.

He's right you know, version control is lousy with that... when hit by a meteorite, or a nuclear bomb explodes nearby. What happens if debris from that satellite the Americans shot down lands on your server and different developers are working on the same file, huh?

2008-02-21 Reply Admin

Roundhill:
Perhaps that database designer ran into someone like my mother, who has two middle names.

Here (in Europe) it is very common to have multiple "middle" names, and especially in catholic families one of those is often Maria. Even for men.

In some countries (such as Portugal and Spain) one can have multiple family names as well (and the number indicates one's social status).

2008-02-21 Reply Admin

There are people who have more than one middle name, like me. Ruben F. C.. Still That's not 255 characters but on US websites I can never fill in my two middle initials because apparently that phenomenon doesn't exist there.

2008-02-21 Reply Admin

Daniel Beardsmore:
With all such problems, where do you define a sane limit? I think my English teacher at school had about six middle names, and didn't some poor girl in the US get one first name for every letter in the alphabet? So, what should we set as a limit -- 42?

I hope you didn't mean a multiplication of the number of letters in the alphabet by two...

Someone You Know · 2008-02-21 Reply Admin

akatherder:
Someone You Know:
That depends on which problem you're referring to. In this case, the name was not being used as a database key or anything; the system was perfectly capable of treating two people with the same name as different entities. If I recall correctly, a unique employee ID number was used for this purpose.
The problem only arose when schedules and other human-readable reports needed to be generated. These guys needed to know which one of them was supposed to work which shift, and so did other people, like the supervisors assigned to those shifts. It was decided that a number would be appended to the middle initials; the guy with more seniority got #1. This organization had a staff of around 250, so the odds that this sort of issue would occur often enough to merit spending more effort on it were judged to be acceptably low.

If you don't consider this an acceptable solution to this problem, please suggest something better.

At a bare minimum, appending the number to their first or last name would be preferable to breaking the datatype and ignoring all reasonable assumptions about a field named "MiddleInitial".

That's like having a field named StateAbbreviation and making it 3 characters.

AL = Alabama AL2 = Alaska //bypassing AK workaround

Not in this case. For legal reasons, the first and last names had to remain identical to the employee's legal name, while the middle initial did not. If John Smith goes to the bank with a paycheck made out to John Smith2, the bank might let him deposit it, but technically they shouldn't.

2008-02-21 Reply Admin

NSCoder:
You people with ad-blockers are missing out on so much.

Our penises are long enough as it is, but thank you for your concern!

2008-02-21 Reply Admin

Alan:
As a side challenge, guess what my actual middle initial is.

Are you by any chance Alan Horatio Quintessence Brown?

2008-02-21 Reply Admin

captcha: WHO GIVES A FUCK WHAT YOUR CAPTCHA WAS

2008-02-21 Reply Admin

Our production schema has a unique constraint on the Initials field in the User table.

The General · 2008-02-21 Reply Admin

Alan:
Yup, using a length of 255 for a middle initials field is stupid.
Regards

Alan Q. T. Z. D. I. B. I. W. K. L. S. D. W. Z. Q. R. R. G. O. A. P. Ú. Q. C. Y. S. L. Q. C. F. C. D. R. E. M. W. L. H. X. Í. C. L. Q. C. F. I. W. A. L. S. S. S. S. S. S. É. L. E. E. A. O. D. P. Ú. Q. C. Y. S. L. R. V. M. W. L. D. T. N. Ú. U. R. D. D. A. L. W. C. Brown

Are you by any chance related to my friend Alan Q. T. Z. D. I. B. I. W. K. L. S. D. W. Z. Q. R. R. G. O. A. P. Ú. Q. C. Y. S. L. Q. C. F. C. D. R. E. M. W. L. H. X. Í. C. L. Q. C. F. I. W. A. L. S. S. S. S. S. S. É. L. E. E. A. O. D. P. Ú. Q. C. Y. S. L. R. V. M. W. L. D. T. N. Ú. U. R. D. D. A. L. W. Ž. Brown? Otherwise it's a bit of a freaky coincidence.

KattMan · 2008-02-21 Reply Admin

Teh Optermizar:
KattMan:
Well it is so much better than the way a lot of health insurance companies deal with twins. Either one claim is always denied because they feel you are double claiming, or one child is "born" a month later.

Any parent who has twins and gives them the exact same name should be taken out the back and shot :P

The problem isn't the names, names don't matter in most insurance applications. Everyone is referred to by number, and in some cases this number is generated by policy number + birthmonth + birthyear. Take a close look at that and you will see why twins are a problem.

One other thing to note is that this problem also raises it's head if the two adults are nearly the same exact age, born on the same month in the same year.

KattMan · 2008-02-21 Reply Admin

safdsdaf:
KattMan:
You are only half right. The database still reserves the 255 characters. It has to because records all have to be the same length in order to do proper offset calculations. The difference is that with a char you get all 255 characters then a terminator, with a varchar you get the terminator without all the extra padding. So no you are not actually saving space by saying varchar(255), you just don't have to return all of it if the data does not need it, thereby saving bandwidth instead. Other fields like ntext actually only reserve 8 bytes in the actual data table to hold a beginning pointer to the data in a separate file.

That's what I love about this site. You find out who the TRUE WTF idiots are.

I'll admit when I am wrong and after reading these comments I had to go back and check for myself. I find that I am only half right. What I described for a varchar is a fringe case. Normally a varchar is a pointer outside the normal record in the table, this maintains the static record length for offset processing. But in 2005 in fringe cases, the data could be stored directly in the normal record stream but this is only when the data is small enough to be held in the space normally set aside for the address pointer. In this case it still uses all the storage space, but only returns the data up to the terminator.

Somewhere I took the fringe case and made it the normal case for varchar in my head. What I said still holds true for ntext and nvarchar, they are pointers. The fringe case might still apply to them but in even more rare circumstances, but I can't find anything actually stating this so that might not be the case.

I apologize.

2008-02-21 Reply Admin

KattMan:
safdsdaf:
KattMan:
You are only half right. The database still reserves the 255 characters. It has to because records all have to be the same length in order to do proper offset calculations. The difference is that with a char you get all 255 characters then a terminator, with a varchar you get the terminator without all the extra padding. So no you are not actually saving space by saying varchar(255), you just don't have to return all of it if the data does not need it, thereby saving bandwidth instead. Other fields like ntext actually only reserve 8 bytes in the actual data table to hold a beginning pointer to the data in a separate file.

That's what I love about this site. You find out who the TRUE WTF idiots are.

I'll admit when I am wrong and after reading these comments I had to go back and check for myself. I find that I am only half right. What I described for a varchar is a fringe case. Normally a varchar is a pointer outside the normal record in the table, this maintains the static record length for offset processing. But in 2005 in fringe cases, the data could be stored directly in the normal record stream but this is only when the data is small enough to be held in the space normally set aside for the address pointer. In this case it still uses all the storage space, but only returns the data up to the terminator.

Somewhere I took the fringe case and made it the normal case for varchar in my head. What I said still holds true for ntext and nvarchar, they are pointers. The fringe case might still apply to them but in even more rare circumstances, but I can't find anything actually stating this so that might not be the case.

I apologize.

Which database is this?

2008-02-21 Reply Admin

Alan:
Yup, using a length of 255 for a middle initials field is stupid.

Indeed, that would be a pretty stupid name if you needed that!

Regards,

Johann Gambolputty de von Ausfern schplenden schlitter crasscrenbon fried digger dangle dongle burstein von knacker thrasher apple banger horowitz ticolensic grander knotty spelltinkle grandlich grumblemeyer spelterwasser kürstlich himbleeisen bahnwagen gutenabend bitte eine nürnburger bratwustle gerspurten mit zweimache luber hundsfut gumberaber shönendanker kalbsfleisch mittler raucher von Hautkopft of Ulm.

2008-02-21 Reply Admin

Flat files are useful, especially in the telecoms. If you process like 400 millions records a day (average, not peak) with multiple transformations under way, you're better off using simple text files rather than a relational database.

KattMan · 2008-02-21 Reply Admin

28% Genius:

Which database is this?

SQL 2005

Someone You Know · 2008-02-21 Reply Admin

Johann Gambolputty:
Alan:
Yup, using a length of 255 for a middle initials field is stupid.
Indeed, that would be a pretty stupid name if you needed that!
Regards,

Johann Gambolputty de von Ausfern schplenden schlitter crasscrenbon fried digger dangle dongle burstein von knacker thrasher apple banger horowitz ticolensic grander knotty spelltinkle grandlich grumblemeyer spelterwasser kürstlich himbleeisen bahnwagen gutenabend bitte eine nürnburger bratwustle gerspurten mit zweimache luber hundsfut gumberaber shönendanker kalbsfleisch mittler raucher von Hautkopft of Ulm.

You're about 20 hours late on that reference, Johann...

2008-02-21 Reply Admin

I hate Middle Initial fields that are one char (I have two middle names).

2008-02-21 Reply Admin

Someone You Know:
You're about 20 hours late on that reference, Johann...

Damn it, somehow missed that one. Though I DID wonder why nobody did this obvious joke before. Mea culpa.

2008-02-21 Reply Admin

Yeah 255 chars for the middle initial is obscene! My last company stored the middle initial as a bool - all employees were arbitrarily allocated a middle initial of Q or Z

n9ds · 2008-02-21 Reply Admin

Wow, automation! Back when I was working in radio, watching the automation fail (routinely) was a full-time occupation. We had to make sure there was a tape (reel-to-reel of course) mounted (correctly) on the correct drive, and there were carts in the cart players, one of which was the news, another was the weather, both of which you had to record in between failures, and ... That was only for two stations, (AM/FM sisters).

The hardware was proprietary (remember, the debate was TRS-DOS vs. CP/M back then), I have no idea what processor. And logging? Why, printing to paper of course! Talk about stability, non-volatility, near universal readability....Ah, the good old days. May they never return.

KattMan · 2008-02-21 Reply Admin

KattMan:
28% Genius:

Which database is this?

SQL 2005

Oh and just so that people don't think I pulled this out of no where, here is the relevant quote:

If any columns are for LOB data types (text, ntext, image, and the new LOB types in SQL Server 2005 - varchar(max), nvarchar(max), varbinary(max), XML), then there's a pointer stored in the data record which points to a text record on a different page (the root of a loose tree that stores the LOB value). Exceptions to this are when schema has been set to store LOB columns 'in-row' when possible. This is when a LOB value is small enough to fit within the confines of the data page that holds the data record, and so is stored in the same data record. This is a performance benefit as selecting the LOB column does not require an extra IO to read the text record.

In SQL Server 2005, non-LOB variable length columns (e.g. varchar, sqlvariant) may also be stored 'off-row' as part of the new capability (called row-overflow) of having table rows longer than 8060 bytes. In this case the storage format is the same as for LOB values - a pointer in the data record pointing to a text record.

For your own reference this is at the following location: http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/23/644607.aspx

real_aardvark · 2008-02-21 Reply Admin

Wojtek:
Flat files *are* useful, especially in the telecoms. If you process like 400 millions records a day (average, not peak) with multiple transformations under way, you're better off using simple text files rather than a relational database.

Once you've got to that level of throughput, you're basically dealing with the amount of RAM you can acquire. On average, that's, what, 4,000 records a second? Depends on the size of the record (and the transformations involved), but that's not a huge hit for modern computers with SAN storage.

Flat files would be something of a bust in this scenario, because they have no transactional integrity. I'm not fan of RDBMSes, but, if you can suck the whole lot into memory, then you're golden. Otherwise, use a decent ISAM solution. (MySQL and InnoDb, the horrors!)

This doesn't seem to be much or an argument, unless you're arguing against stored procedures, and there I'd (generally) agree with you.

Mind you, it does explain why my telephone bill is always so high.

2008-02-21 Reply Admin

OMFGROFLOL!

savar · 2008-02-21 Reply Admin

GregP:
Tp:
We looked through the schema, and laughed pretty good when we found that "age" was defined as varchar(255).

I saw something similar from an Indian programmer once, a numeric range was specificed as 1-1000 or so. We got back a varchar(1000).

That's just the right amount of storage for a unary representation.

Those Unstable Databases & An Interesting Database Field

Leave a comment on “Those Unstable Databases & An Interesting Database Field”