CODEWITHSUNDEEP
databasedatabase-design
Deniz Dogan
Deniz Dogan

Reputation: 26227

Does the size of database fields matter?

I have a database table, let's call it Countries. Countries all have names, short names and continents. Let's assume that the longest name of any country in the world is 54 characters long. Should I set the maximum size to 54 or should I set it to e.g. 64 or something else? Will my choice affect queries or storage in any way?

I know this might seem like pre-optimizing, but I find myself often choosing 32, 64, 128, etc. and I'd like to know if that matters.

Thanks

Upvotes: 3

Views: 2594

Answers (4)

al.
al.

Reputation: 504

Depends on the data type. For instance in ORACLE - a varchar2 data type would be a good choice for variable-length strings. You need only to define the maximum length of the string - varchar2(maxsize). This would mean that any string with a length of up to 10 would fit. The length of the column would depend on the actual string stored in it.

See. link text

In your case, if your absolutely sure that there is no country name longer than 54 characters, I would use variable-size data type of size 54.

Upvotes: 2

marc_s
marc_s

Reputation: 755531

If you're using SQL Server, I would strongly recommend NOT using VARCHAR(8000) or VARCHAR(MAX) for all fields..... you need to let common sense prevail here - make the field as big as you think you'll probably need it, add a little extra for unexpected length, and you're done :-)

Also, I think it's a good idea to keep it to a few lengths that you use over and over again. E.g. I use VARCHAR(255) for most fields like street address or e-mail etc. that could be a bit longer, VARCHAR(50) for things like phone numbers etc., and VARCHAR(20) for short strings like zip code and such.

Be pragmatic about it - find a style that works for you. Don't over-optimize, but don't go nuts the other way, either.

Marc

Upvotes: 1

Marcel Jackwerth
Marcel Jackwerth

Reputation: 54782

A solid DBMS should know what's best for it. If you need "54 chars" but the database can do better search-optimizations with 64 chars, it will expand the data-field internally automatically. If it doesn't, you will have saved 12 chars per row.

I don't think that's something you should worry about, just write down what you need.

Upvotes: 3

Mitch Wheat
Mitch Wheat

Reputation: 300827

Answers will be RDBMS specific:

If you are using SQL Server (2000 onwards), a varchar only uses space for the actual characters stored in it (plus a small overhead), up to the maximum size as declared.

As a general rule of thumb, don't optimise until you have a problem.

Upvotes: 2

Related Questions