Take the 2-minute tour ×
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.

I understand that an 254 character email address is valid, but implementations I have researched tend to use a varchar(60) to varchar(80) or equivalent. For example: this SQL Server recommendation uses varchar(80) or this Oracle example

Is there a reason to not use the full 254 character maximum? Doesn't a varchar by definition use only as much storage as needed to hold the data?

Are there significant performance implications/trade-offs which cause so many implementations to use less than the full 254 possible characters?

share|improve this question
5  
FWIW, a Stack Overflow article here states with some authority that the maximum length of an email address is 254 characters. –  Gord Thompson Mar 19 '13 at 15:11
    
@GordThompson so you should reject an e-mail that is x@some-valid-255-char-domain because of errata to the standard, even though just about every server on earth will accept such an address? If you normalize local part and domain, that's going to be some pretty ugly validation to ensure that the combination of a valid (1-64) localpart and a valid (4-255) domain don't combine to exceed 254 characters. –  Aaron Bertrand Mar 19 '13 at 15:52
    
I'm not sure that "just about every server on earth will accept such an address" because the Errata Report states that "there is a restriction in RFC 2821 on the length of an address in MAIL and RCPT commands of 256 characters. Since addresses that do not fit in those fields are not normally useful, the upper limit on address lengths should normally be considered to be 256." –  Gord Thompson Mar 19 '13 at 16:03
    
@GordThompson I guess, but if people are going to put in invalid crap, then I guess it's up to you to either accept it or deny it and present an error. In which case they might just put in shorter, invalid crap. It also seems to be a bit short-sighted to constrain the commands to something shorter than what is otherwise technically possible. Not something anyone here can do about, but it's still going to require validation that would otherwise be absolutely unnecessary. –  Aaron Bertrand Mar 19 '13 at 16:10
2  
To look at it from another perspective, perhaps you might want to make a business decision to say that you just don't want to do business with anyone whose email address is longer than 80 characters, because they are probably nuts. –  Joel Brown Mar 20 '13 at 15:24

6 Answers 6

up vote 19 down vote accepted

I've always used VARCHAR(320). Here's why. The standard dictates the following limitations:

  • 64 characters for the "local part" (username).
  • 1 character for the @ symbol.
  • 255 characters for the domain name.

Now, some folks will say you need to support more than that. Some folks will also say that you need to support Unicode for domain names (meaning you have to switch to NVARCHAR). While the standard may change in the meantime (it's been a while since I've had skin in the game), I am quite confident that at this time most servers in the world will not accept Unicode e-mail addresses, and I am sure many servers will have issues creating and/or accepting addresses with > 320 characters.

That said, you can prepare for the worst now, if you like (and if you are using Data Compression in SQL Server 2008 R2 or better, you will benefit from Unicode compression, meaning you only pay the 2 byte penalty for characters that actually need it). This way you can make your column as wide as you want, and you can let people stuff any too-long junk in there that they want - they won't receive an e-mail if they give you junk just like they won't receive an e-mail if the insert fails. The problem is if you let invalid junk in, you have to deal with it. And no matter what size you make it - if someone will try to stuff 400 characters into a 320-character column, someone will try to stuff 1025 characters into a 1024-character column. There is no reason any sensible person should have an e-mail address > 320 characters unless they are using it to explicitly test system boundaries.

But stop asking for opinions on this - and stop looking at other implementations for guidance (it just so happens in this case that the ones you referenced did not bother to do their own homework and just picked numbers out of their, well, you know). You have direct access to the standard - make sure you consult the most current version, support that as a minimum, and stay on top of the standard so you can adapt to changes in specs.


EDIT thanks to @ypercube for the ping in chat.

As an aside, perhaps you don't want to dump the whole address into a single column in the first place. Normalization might suggest that you don't want to store @hotmail.com 15 million times when a much skinnier FK int would work just fine and not have the additional overhead of variable length columns. You could also normalize the username, as john.smith@hotmail.com and john.smith@gmail.com share a common username - they don't know each other but your database doesn't care about that.

I talked about some of this here:

http://www.mssqltips.com/sqlservertip/2657/storing-email-addresses-more-efficiently-in-sql-server/

http://www.mssqltips.com/sqlservertip/2671/storing-email-addresses-more-efficiently-in-sql-server--part-2/

This introduces challenges however to the 254-character limit above, since there doesn't seem to be consensus about what happens when a valid 255-character domain is combined with a valid 1-character localpart. This should be accepted by most servers around the world but seem to violate this 254-character limit. So do you create a Domains table that has an artificially lower restriction on length for e-mail addresses, when the domain could be re-used as a valid 255-character URL?

share|improve this answer
    
whoa whoa whoa, I never noticed it was 64 on the primary –  jcolebrand Mar 19 '13 at 16:25
2  
@TheronK that's not what I said. It's an argument against a 254 character limit on e-mail addresses (or domains pre-concat). If you build a Domains table and you use it for more than just e-mail addresses, then there could be valid 255-character domains in there, that wouldn't be valid when concatenating to formulate an e-mail address. –  Aaron Bertrand Mar 19 '13 at 17:34

There are a few considerations with this decision. First and foremost is to use current and future predictions of necessary limitations that the data will have to conform to. There's a reason why you don't want to set every string column data type to varchar(1024) when you are just storing a string that shouldn't exceed 32 characters (emphasis on the should keyword).

If you have some sort of vulnerability where emails are all modified to become 255 characters, then you could potentially have a long performance impact of page splits. This may seem out of the ordinary, and it most likely is, but you need to size your data to the business requirement. Much like the age-old constraint at the database vs. application debate, I'm a firm believer that data type limitations and allowable values should also be enforced at the data tier.

Which leads me to my next point. The database is most likely just the data tier. What does the application tier utilize? For instance, if you have an application where you can only enter 80 characters for an email address, why would you want to the data type to be any larger? Business needs to answer two questions:

  1. What can it be?
  2. What should it be?

Only then you'll have your answer.

Doesn't a varchar by definition use only as much storage as needed to hold the data?

Yes and no. There is going to be a sort of offset for the variable length data to record the length of it.

share|improve this answer
    
According to the Stack Overflow article here, an email address can be up to 254 characters. If that is true, then when does it make sense to use anything shorter for a column width? Isn't saying that it should never exceed 'n' characters just another way of saying that it will "probably 'never'" exceed that? (Which, of course, is just asking for a user to encounter a case where valid data IS longer than we thought it should be.) –  Gord Thompson Mar 19 '13 at 15:05
    
Agreed, if it could be that then size it accordingly. That was the point I was trying to make in my answer. –  Thomas Stringer Mar 19 '13 at 15:05
    
+1 For also answering the secondary question! –  Thronk Mar 19 '13 at 15:07
    
the data tier needs to be sized to accommodate email addresses from other applications. The business requirement is to support integration with other web api's. –  Thronk Mar 19 '13 at 17:30

RFC 5321 (the current SMTP spec, obsoletes RFC2821) states:

The maximum total length of a user name or other local-part is 64 octets. The maximum total length of a domain name or number is 255 octets

So 64 + 255 + @ sign implies VARCHAR(320). You probably will never need this much but it's safe to have it, just in case.

share|improve this answer
    
The correct limit is 254. rfc-editor.org/errata_search.php?rfc=3696&eid=1690 –  Neil McGuigan Dec 19 '13 at 18:32

Any variation of VARCHAR uses only as much space in the data block as needed. The additional bytes for storing the length are trivial compared to the space that would be wasted using a fixed-length CHAR instead.

Since a VARCHAR column length is really a "maximum length," it should be set larger than the maximum length possible under any circumstances. Only as much space as each row needs will be used. The application programs should then be designed with scrolling fields or whatever makes sense based on typical values.

A database design is like a physical piece of paper in that it sets out the hard limits as to size. A paper page cannot be enlarged. In this analogy, the application program is like a form printed on the page. There is a lot that can be done to adjust how much data we can hold in the form.

Though the command to increase a VARCHAR size may look simple and run instantly on a small table, doing so on a table with thousands of rows or more is probably going to require some kind of database quiesce while regenerating all the data and index blocks. One way is to copy everything to a new table with the larger columns. Whatever technique is used, it's a-big-hairy-deal. Thus, you should consider the VARCHAR column size largely immutable once a production table is loaded.

share|improve this answer

VARCHAR is the best data type to be used for email addresses as Emails vary a lot by length. NVARCHAR is also an alternative but I would recommend it to use only if the email address contains extended chars and keep in mind that it requires double amount of storage space as compared to VARCHAR.

In my environment, we use varchar(70) as the longest ones that I have came across are closely 60-70 char long, but it depends on your company's customer base as well. Also, as a side-note, make sure that you have some Email validation check in-place for the validity of Email addresses.. like using check constraints or CHARINDEX

share|improve this answer

As a comment to the excellent answers already here:

First, if you have created the field as varchar(240) and you want to later change it to a longer field, say varchar(320), this change should be a trivial operation on the database server - depending, of course, on your database product.

alter table Schema.Object alter column EmailAddress varchar(320) ;

Second, depending on the average row size and the page size, using varchar(320) instead of varchar(240) might not change the number of allocated pages (the disk space actually taken up by the table).

Third, someone above talked about validating an email address. I contend that there is only one sure way to validate an email address and that is to send an email to it. :-)

share|improve this answer

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.