SQL Server Common Data Types

There are many variable types in SQL Server (ntext, varchar, nvarchar etc.), how to decide which data type to use.
For example, common fields in many applications are:
ID
Telephone Number
Email
Description (a paragraph of text)
Name
SSN
Price
Ship Date
Sex (m/f)
Discontinued (yes/no)
Quantity
Zip Code

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:

Do I need non-Western-European characters? If yes --> use NCHAR/NVARCHAR types, otherwise CHAR/VARCHAR
Is my string very short (< 5 characters) and typically always the same length? If yes: use CHAR, otherwise VARCHAR
Do I need really really huge volumes of text? If so, use VARCHAR(MAX), otherwise size it to match your needs

ID = bigint 
Telephone = varchar(12)
Email = varchar(100)
Description = nvarchar(max) (sql Server 2005 and 2008 only)
Name = nvarchar(100)
SSN = varchar(11)
Price = money
ShipDate = datetime (date if using SQL Server 2008)
Sex = char(1) (i have also used bit before 0 = female 1 =male)
Discontinued (true false field) = bit
Quantity = int if not fractional decimal if it is fractional
ZipCode = varchar(10)

DataTypes Data Types SQL