19 September 2015
Data Types for Common Columns
- People’s name: nvarchar(50) for surname or first name, nvarchar(100) for full name. Norwegian and German names have special characters hence unicode.
I prefer surname than last name, and given name than first name. But in most cases I don’t have a say because column name should match the screen.
- Company name: nvarchar(100). According to UK’s company house the longest they found was 82 character (link). Unicode (nvarchar) of course, to cater for German and Scandinavian companies.
- Address: nvarchar(300). This cover street1 (70), street2 (70), street 3 (70), local area (50), but not city/town, state/county/provice, post code and country. This is for international address, not country specific. I don’t believe in 255 (link).
- City or town: varchar(100). The record is a town New Zealand with 85 chars, followed by Wales with 58 chars (link). I usually set it to 50 as none of the big cities are more than 50 but I think there are small towns which more than 50.
- State/county/provice: varchar(100). This is for international location, not country specific. “Newfoundland and Labrador” in Canada is 25.
- Country: varchar(100). Formal name of UK is 52 chars (UK of GB and NI) but it is usually United Kingdom (14). I think the longest one is “South Georgia and the South Sandwich Islands” for now (44), but to future proof it I prefer 100 than 50. Got it: “Territory of the French Southern and Antarctic Lands” is 52 chars (that’s the formal name, yes the ISO name is only 35 chars)
- Email address: varchar(300). The official length is 254 (link). I don’t believe in 255 (link).
- Post code: varchar(20). 8 in UK and 5 in US and Indonesia, 10 in Brazil, Guam, Iran, and Saudi. So I’m taking 20 for future proofing it.
- Telephone numbers: varchar(30) is enough for international telephone number in any country. The longest is probably India and China. India’s mobile is 16 chars e.g. +91-AAA AAB BBBB. China is 17: +86 755 XXXX YYYY.
RSS feed for comments on this post. TrackBack URI