How long should SQL email fields be? [duplicate]
The theoretical limit is really long but do you really need worry about these long Email addresses? If someone can't login with a 100-char Email, do you really care? We actually prefer they can't.
Some statistical data may shed some light on the issue. We analyzed a database with over 10 million Email addresses. These addresses are not confirmed so there are invalid ones. Here are some interesting facts,
- The longest valid one is 89.
- There are hundreds longer ones up to the limit of our column (255) but they are apparently fake by visual inspection.
- The peak of the length distribution is at 19.
- There isn't long tail. Everything falls off sharply after 38.
We cleaned up the DB by throwing away anything longer than 40. The good news is that no one has complained but the bad news is not many records got cleaned out.
I've in the past just done 255 because that's the so-ingrained standard of short but not too short input. That, and I'm a creature of habit.
However, since the max is 319, I'd do nvarchar(320) on the column. Gotta remember the @!
nvarchar won't use the space that you don't need, so if you only have a 20 character email address, it will only take up 20 bytes. This is in contrast to a nchar which will always take up its maximum (it right-pads the value with spaces).
I'd also use nvarchar in lieu of varchar since it's Unicode. Given the volatility of email addresses, this is definitely the way to go.
The following email address is only 94 characters:
i.have.a.really.long.name.like.seetharam.krishnapillai@AReallyLongCompanyNameOfSomeKind.com.au
- Would an organisation actually give you an email that long?
- If they were stupid enough to, would you actually use an email address like that?
- Would anyone? Of course not. Too long to type and too hard to remember.
Even a 92-year-old technophobe would figure out how to sign up for a nice short gmail address, and just use that, rather than type this into your registration page.
Disk space probably isn't an issue, but there are at least two problems with allowing user input fields to be many times longer than they need to be:
- Displaying them could mess up your UI (at best they will be cut off, at worst they push your containers and margins around)
- Malicious users can do things with them you can't anticipate (like those cases where hackers used a free online API to store a bunch of data)
I like 50 chars:
If one user in a million has to use their other email address to use my app, so be it.
(Statistics show that no-one actually enters more than about 40 chars for email address, see e.g.: ZZ Coder's answer https://stackoverflow.com/a/1297352/87861)