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

specify the scale from 0 to 7, depending on how you want to divide and
store the seconds. Storage for this data type is fixed at 8 bytes, assuming a
precision of 7.
Datetimeoffset
The final SQL Server 2008 date and time data type addition is datetime-
offset. This is a standard date and time data type, similar to datetime2 (be-
cause it can store the precision). Additionally, datetimeoffset can store a
plus or minus 14-hour offset. It is useful in applications where you want to
store a date and a time along with a relative offset, such as when you're
working with multiple time zones. The storage requirement for datetime-
offset is 10 bytes.
String Data Types
When it comes to storing string or character data, the choice and variations
are complex. Whether you need to store a single letter or the entire text of
War and Peace, SQL Server has a string data type for you. Fortunately,
once you understand the difference between the available string data
types, choosing the correct one is straightforward.
Char and Varchar
Char and varchar are probably the most used of the string data types. Each
stores standard, non-Unicode text data. The differences between the two
lie mostly in the storage of the data. In each case, you must specify a length
when defining a column as char or varchar. The length sets the limit on the
number of characters the column can hold.
Here's the kicker: The char data type always requires the same num-
ber of bytes for storage as you have specified for the length. If you have a
char(20), it will always require 20 bytes of storage, even if you store only a
5-character word in the column. With a varchar, the storage is always the
actual number of characters you have stored plus 2 bytes. So a varchar(20)
with a 5-character word will take up 7 bytes, with the extra 2 bytes holding
a size reference for SQL Server. Each type can have a length of as many as
8,000 characters.
When do you use one over the other? The rule of thumb is to use char
when all the data will be close to the same length, and use varchar when
the data will vary a great deal. Following this rule should make for opti-
mum storage.