NVARCHAR(?) for Email addresses in SQL Server NVARCHAR(?) for Email addresses in SQL Server sql sql

NVARCHAR(?) for Email addresses in SQL Server


I've always used 320 based on your latter calculation. It doesn't cost you anything to allow more*, unless people abuse it and stuff junk in there. It could cost you to allow less, as you'll have a frustrating users if they have legitimately longer e-mail addresses and now you'll have to go back and update schema, code, parameters etc. In the system I used to work with (an e-mail service provider), the longest e-mail address I came across naturally was about 120 characters - and it was clear they were just making a long e-mail address for grins.

* Not strictly true, since memory grant estimates are based on the assumption that varying-width columns are half-populated, so a wider column storing the same data can have lead to vastly different performance characteristics of certain queries.

And I've debated whether NVARCHAR is necessary for e-mail address. I've yet to come across an e-mail address with Unicode characters - I know the standard supports them, but so many existing systems do not, it would be pretty frustrating if that was your e-mail address.

And while it's true that NVARCHAR costs double the space, with SQL Server 2008 R2 you can benefit from Unicode compression, which basically treats all non-Unicode characters in an NVARCHAR column as ASCII, so you get those extra bytes back. Of course compression is only available in Enterprise+...

Another way to reduce space requirements is to use a central lookup table for all observed domain names, and store LocalPart and DomainID with the user, and store each unique domain name only once. Yes this makes for more cumbersome programming, but if you have 80,000 hotmail.com addresses, the cost is 80,0000 x 4 bytes instead of 80,000 x 11 bytes (or less with compression). If storage or I/O is your bottleneck, and not CPU, this is definitely an option worth investigating.

I wrote about this here:

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


I guess VARCHAR(320) would be the normal limit for an ASCII-based domain name and email address. But won't we start seeing unicode domain names appearing sometime soon?

http://en.wikipedia.org/wiki/Internationalized_domain_name

Maybe NVARCHAR(320) is what we should start using?