May 16, 2011 at 6:31 am
We are currently reviewing column definitions for dealing with international clients. Our current email address field is VARCHAR(36). Online research returns conflicting recommendations and statements (what else is new?) and I could not find anything definitive on SQL server central. Or may be I did not ask the right question. It appears that NVARCHAR (128) would allow for reasonable length and foreign characters but a length 256 or 320 would enable the maximum permissible - depending on who you believe.
I believe that anyone who has to type in a 128 character email address needs help, but automated business systems using first name, middle initial, lastname @ bizunit.businessname.com could easily get into the 50+ character range. As well as the database we also have to consider boxes on printed forms (we still require them!) and data input screens.
Can anyone improve on the proposed NAVARCHAR(128) recommendation - with reasons why (I need to learn as we may find similar requirements elsewhere).
Thanks
SteveD
May 16, 2011 at 8:31 am
You certainly don't need to use nvarchar for an email address. Your current 36 is WAY too small. On the other hand you will find some people out there that are just stupid and want to test the limits so they make crazy long email names and domain names. I have found that varchar(75-100) seems to be plenty. Of course, there is no harm at some point in making the field bigger if you have an email that really is legitimate and longer than that. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2011 at 9:06 am
I think I use varchar(250) for mine.
Ultimately there is no difference to SQL Server if it's varchar(36) or varchar(1000) other than what the upper limit is for entries. Go large, and don't worry.
Not sure if nvarchar is needed or not. It appears I get email from all over the world and somtimes the names are in different characters, but not sure about the address itself. Wouldn't be surprised to find out the SMTP spec only allows western characters.
May 16, 2011 at 9:35 am
Steve Jones - SSC Editor (5/16/2011)
I think I use varchar(250) for mine.Ultimately there is no difference to SQL Server if it's varchar(36) or varchar(1000) other than what the upper limit is for entries. Go large, and don't worry.
Not sure if nvarchar is needed or not. It appears I get email from all over the world and somtimes the names are in different characters, but not sure about the address itself. Wouldn't be surprised to find out the SMTP spec only allows western characters.
Wiki entry for email addresses states that it is limited to the ASCII set. Like Steve says, "Go big or go home". 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2011 at 9:52 am
Thanks for the responses gentlemen. I note that Arabic domain names are now coming online.
http://www.arabianbusiness.com/uae-launches-first-arabic-address-website-271298.html
Presumably if the web 'domain' is in Arabic then it won't be long before there will be requests for the email domain to follow suit.
Part of my job is to look ahead (read I am the one who has to go back and redo things). We do have to cope with names and addresses in non-Roman character sets and I just finished installing a wave of Traditional and Simplified Chinese fonts for Adobe Reader on a number of workstations.
I suppose any change to the SMTP standard will take a while to implement. Much harder than web domains as email servers are more localized within companies and built into sub-systems.
Sigh.
Thanks again for your input.
SteveD
May 16, 2011 at 10:00 am
Certainly making your fields nvarchar will allow for that if it ever happens. It doesn't hurt anything, it just takes twice the storage which is really not a big deal unless you have millions of email addresses.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2011 at 10:01 am
I agree with Sean, nvarchar won't really hurt and if you have EE, you can compress the data, so it won't double the space if not needed.
However, even uncompressed, I doubt this is a place to worry about space savings.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply