Re: Optimization of this SQL sentence

Am Dienstag, 17. Oktober 2006 17:50 schrieb Alexander Staubo:
> On Oct 17, 2006, at 17:29 , Mario Weilguni wrote:
> >
> > Enforcing length constraints with varchar(xyz) is good database
> > design, not a
> > bad one. Using text everywhere might be tempting because it works,
> > but it's
> > not a good idea.
>
> Enforcing length constraints is generally a bad idea because it
> assumes you know the data domain as expressed in a quantity of
> characters. Off the top of your head, do you know the maximum length
> of a zip code? A street address? The name of a city?

It's not a bad idea. Usually I use postal codes with 25 chars, and never had
any problem. With text, the limit would be ~1 GB. No matter how much testing
in the application happens, the varchar(25) as last resort is a good idea.

And in most cases, the application itself limits the length, and thus it's
good to reflect this in the database design.

Feel free to use text anywhere for your application, and feel free to use
numeric(1000) instead of numeric(4) if you want to be prepared for really
long numbers, but don't tell other people it's bad database design - it
isn't.