PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 55

Another tip is to avoid using varchar for short columns. We have seen
databases use varchar(2) columns, and the result is wasted space. Let's as-
sume you have 100 rows in your table and the table contains a varchar(2)
column. Assuming all the columns are NULL, you still need to store the
2 bytes of overhead, so without storing any data you have already taken up
as much space as you would using char(2).
One other special function of varchar is the max length option. When
you specify max as the length, your varchar column can store as much as
2^31-1 bytes of data, which is about 2 trillion bytes, or approximately 2GB
of string data. If you don't think that's a lot, open your favorite text editor
and start typing until you reach a 2GB file. Go on, we'll wait. It's a lot of in-
formation to cram into a single column. Varchar(max) was added to SQL
Server in the 2005 release and was meant to replace the text data type from
previous versions of SQL Server.

Nchar and Nvarchar
The nchar and nvarchar data types work in much the same way as the char
and varchar data types, except that the n versions store Unicode data.
Unicode is most often used when you need to store non-English language
strings that require special characters such as the Greek letter beta (

).
Because Unicode data is a bit more complex, it requires 2 bytes for each
character, and thus an nchar requires double the length in bytes for stor-
age, and nvarchar requires double the actual number of characters plus the
obligatory 2 bytes of overhead.
From our earlier discussion, recall that SQL Server stores tables in
8,060-byte pages. Well, a single column cannot span a page, so some sim-
ple math tells us that when using these Unicode data types, you will reach
8,000 bytes when you have a length of 4,000. In fact, that is the limit for
the nchar and nvarchar data types. Again, you can specify nvarchar(max),
which in SQL Server 2005 replaced the old ntext data type.

Binary and Varbinary
Binary and varbinary function in exactly the same way as char and varchar.
The only difference is that these data types hold binary information such
as files or images. As before, varbinary(max) replaces the old image data
type. In addition, SQL Server 2008 allows you to specify the filestream at-
tribute of a varbinary(max) column, which switches the storage of the
BLOB. Instead of being stored as a separate file on the file system, it is
stored in SQL Server pages on disk.