Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
2

This is a general question about database admin.

Is there some kind of standardization for character limits on fields in a DB? It makes sense to specify character limits on the database end for names, phone number etc where you know the sort of data being entered and you want the data entered to be rational so you restrict the character limit. But how do you know what a sensible limit is in this crazy world there could a lot of exceptions.

In my case it is a system for a local company here in the UK with only 10-15 employees. So I could go back and change it if needed but I'd rather enter a rational limit to start with.

Sorry if this question has already been asked. If it has I couldn't find it through Google or on similar Questions.

share|improve this question
1  
using check constraints rather than a hard limit can save you pain later – Jack Douglas♦ Aug 23 '12 at 17:15
@JackDouglas Is that applicable for all data field types or specifically VARCHAR or nVARCHAR? – CodeBlend Aug 24 '12 at 8:59
it is only the various character types that are usually length-limited in this way, isn't it? – Jack Douglas♦ Aug 24 '12 at 12:31

migrated from stackoverflow.com Aug 23 '12 at 12:07

2 Answers

up vote 3 down vote

I think some of it goes back to requirements. How much of a name are you storing? Do you ever want to keep the "names" separate (like first, middle, last)? Do you want to handle multiple middle names?

I'd say 50 characters should be fairly sufficient for each name if you wished to separate them out. (That will be way more than enough for most names, but will also cover names that are larger.) I'd also probably go with VARCHAR than CHAR for names since they vary so much.

If you were going to put the full name in one field, I'd go with 150 - 200 characters then.

Again....it all goes back to requirements...what do you need?

share|improve this answer
Does @JackDouglas comment a solution, it seems to me that check constraints is not the same as field limits is that true? – CodeBlend Aug 24 '12 at 9:05
That is correct, they are not the same. I guess I'd have to know more about his experiences with pain to understand why he'd suggest that. – Chris Aldrich Aug 24 '12 at 15:57
+1 For Requirements. Storing a name so you can address the person is different than storing it for a legal document. If I saw an abnormally large name field I would expect it to be for legal use or that perhaps requirements weren't well defined. Don't forget that the maximum size influences not just space, but optimization, indexing, debugging, unit testing, and documentation. – Leigh Riffel Aug 24 '12 at 18:35
up vote 1 down vote

While I wouldn't get out of control with what you permit for storage, using a VARCHAR or NVARCHAR type to store the strings makes it a much less critical question - since you're only going to use the storage that your data actually consumes, rather than the full length of your field, you can be a bit more generous here. Given the general pain of changing the data type later, I'd lean towards a bit more than you think you'll need - if you guess too high, there's no wasted space as a result, but if you guess too low there's some table maintenance involved.

I generally go with VARCHAR(255) if there's not some particular reason for me to pick a shorter field length. Remember that you can always control what's allowed and the length of your display fields in the presentation layer, regardless of the type you use to store the data in the actual database, so again, over-accommodating here doesn't do any harm.

With numbers and fixed-width fields, you have to be a bit more precise with what you think you'll use so you don't waste space, but for VARCHAR fields there's no extra cost.

share|improve this answer
3  
Why 255? Why not 239, 268 or 307? What's so special about 255 – a_horse_with_no_name Aug 23 '12 at 20:36
1  
@a_horse_with_no_name: I suppose it's an old habit from when I used to do Oracle. The VARCHAR2(X) value was meaningless, but if it was 255 or smaller, Oracle reserved 2 characters for the length, and 256 or larger and it reserved 3, so there was a slight space savings by picking a value of 255 over 256. However, I believe that's not the case in SQL Server, though I'm not sure anybody here knows for sure. I suppose the value is arbitrary - I'm in favor of "larger than you'll ever need" (why not 8000?) and then limit it at the application layer. – SqlRyan Aug 23 '12 at 21:11
I have never heard of such an "optimization" in Oracle (not even in Oracle 7). Do you have a reference? (Btw: I do prefer really large limits as well, makes changes later a lot easier) – a_horse_with_no_name Aug 23 '12 at 21:36
1  
Interesting point, I seem to remember having to copy to a temp table in order to make changes like altering a field character limit – CodeBlend Aug 24 '12 at 9:06
@a_horse_with_no_name: I did some Google-fu and I'm unable to turn up a reference to what I'm describing. When I was learning Oracle more than a decade ago on 8, my professor told us that and to be honest, I never bothered to confirm it. I could have been doing it this whole time when it's totally made up :) – SqlRyan Aug 24 '12 at 22:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.