Data Warehousing and Business Intelligence

19 September 2015

Data Types for Common Columns

Filed under: Analysis Services — Vincent Rainardi @ 4:50 pm
  1. 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.
  2. 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.
  3. 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).
  4. 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.
  5. State/county/provice: varchar(100). This is for international location, not country specific. “Newfoundland and Labrador” in Canada is 25.
  6. 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)
  7. Email address: varchar(300). The official length is 254 (link). I don’t believe in 255 (link).
  8. 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.
  9. 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.
Advertisements

1 Comment »

  1. Good article! I’m a firm believer that columns should only be as large as they need to be.

    Not just character fields, but also numerical fields. For example, in SQL Server an integer field is 4 bytes and has a numeric range of about -2 billion to 2 billion. But for tables where I never expect to have that many rows (such as a lookup/reference table, which might only have around a dozen) it is more appropriate to use tiny int (1 byte, 0 – 255) or maybe small int (2 bytes, -32,768 to 32,767).

    By using tiny int, small int, and int appropriately, I have been able to shave off over 20GB of wasted space in a database. And if you ever do need to go into a higher range, it’s usually pretty simple to alter the column to a wider numeric type.

    Comment by Michael — 8 October 2015 @ 2:36 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: