SQL Server: most commonly used data types?

Question

I'm a bit confused as there are many variable types in sql server (ntext, varchar, nvarchar, etc) so maybe if you give me what data types you use for the following fields I'll understand this a little better. If I'm missing a common field type please let me know.

ID
Telephone Number
Email
Description (a paragraph of text)
Name
SSN
Price
Ship Date
Sex (m/f)
Discontinued (yes/no)
Quantity
Zip Code

Answer 1

A brief recommendation:

• TEXT, NTEXT, IMAGE: all those types are deprecated and scheduled to be removed in a future version of SQL Server - don't use those!

• CHAR vs. VARCHAR: CHAR is fixed-length, and it will be padding inputs with spaces to the defined length. Works best for short strings (< 5 characters), e.g. codes, like currency (almost always 3 characters), US status (2 chars) etc. VARCHAR on the other hand works best for longer strings and is only storing as much characters as are inserted/updated. If you define a VARCHAR(200) and only insert Christmas into the field, your field occupies 9 characters (and a litte bit of overhead)

• NCHAR/NVARCHAR: Unicode versions of the above; always stores 2 bytes per characters, so your field with Christmas in it will store 9 characters and use 18 bytes to do so. Those are needed if you have non-Western-European characters - such as Cyrillic, Arabic, Hebrew, Asian or other alphabets.

• VARCHAR(MAX) / NVARCHAR(MAX) are the replacements for TEXT and NTEXT - storing up to 2 GByte (2 billion bytes) of data - that's over 300 times the content of Tolstoi's War and Peace - should suffice for the vast majority of cases :-)

So your decision tree could be like this:

1. Do I need non-Western-European characters? If yes --> use NCHAR/NVARCHAR types, otherwise CHAR/VARCHAR

2. Is my string very short (< 5 characters) and typically always the same length? If yes: use CHAR, otherwise VARCHAR

3. Do I need really really huge volumes of text? If so, use VARCHAR(MAX), otherwise size it to match your needs