Specify VARCHAR length, or leave blank?

bunner_bob

Is there any compelling reason to specify the length of a VARCHAR column (i.e. VARCHAR(32)) vs. just leaving it wide open (VARCHAR())? Assuming, that is, you're already checking for length in your data authentication, to prevent ridiculous entries.

Since the content isn't padded like CHAR(), wouldn't you get the same size data whether you've specified a length limit or not? Are there other considerations?

That's assuming you can even do this, which I don't know for sure...

nemonoman

In the case of varchars, the length attribute would restrict the maximum number of chars that will be saved.

Lars_Berg

You cannot omit the length property for a varchar column.

Sxooter

Actually, on some databases you can.

But it's not a good idea if you'd like your DDL to be portable, and it is most definitely against the SQL spec.

I.e. PostgreSQL will let you do this. It's generally better to use the non-standard type text if you want unlimited length types in postgresql though.

bunner_bob

So I guess the next question is, again assuming there is validation in effect to limit the length of submitted content appropriately, is there any reason not to set a long length for varchar?

I guess my thinking is, it makes for one less thing to worry about. Assuming you aren't going to allow more than 255 characters in your validation, just making all your VARCHAR fields 255 in length and limiting size in validation seems like an easier way to go.

Sxooter

My philosophy goes kinda like this... (p.s. I'm talking about postgresql usage here...)

For PostgreSQL the text and varchar type are stored in exactly the same way internally. So all this is about syntactic sugar etc...

If you're gonna store things of indeterminate size, from a fairly well behaved application, then use the "text" type. It is limited to ~1 gig or so. Note that inserting 100 megs at a time can cause performance to fall off a bit, so don't let any tom dick or harry insert anything they want into one.

If there is a true max size for a field, set it in a varchar(xxx) to force the database to limit the size. You can always alter the column later. In pg 8.0 with an alter table statement, otherwise by createing a new colun and selecting the old column into the new column and dropping the old one.

The size in postgresql only affects the input of the data, not how it's stored or how much room the db engine uses to store it, by the way.

csn

I thought I remember reading something somewhere a long time ago that it was better, for performance reasons, to specify char and varchar fields in sizes like 16, 32, 64, 128, 256, etc. Any truth to this?

laserlight

Originally posted by csn
I thought I remember reading something somewhere a long time ago that it was better, for performance reasons, to specify char and varchar fields in sizes like 16, 32, 64, 128, 256, etc. Any truth to this?

Maybe, since there might be optimisations available if such sizes are used.

On the other hand, using say, indices would result in much greater optimisation, if used properly.

Sxooter

It makes no difference to postgresql. might to other databases.

csn

I think the logic was it had something to do with page sizes, or maybe block sizes on disk.

I think mysql's docs said that using char is more effiicient than using varchar. Because the fields are all the same size and line up better.