Datasheet

11
Chapter 1: Introducing T-SQL and Data Management Systems
Data Type Storage Description
*
datetimeoffset 10 bytes The datetimeoffset data type is used to store date
and time from January 1, 0001 through December 31,
9999. The accuracy of the
datetimeoffset data type
varies based on the type of server hardware SQL Server
is installed on, but defaults to 100 nanoseconds if
supported. When defined, the
datetimeoffset data
type expects a date and time string to be specified
along with a time zone offset. Possible time zone offsets
are between − 14.00 and +14.00 hours. For example, to
define a variable that is time - zone aware for Pacific
Standard Time, the following code would be used:
DECLARE @PacificTime AS datetimeoffset(8)
Character Data Types
char 1 byte per
character.
Maximum
8000
characters.
The
char data type is a fixed - length data type used to
store character data. The number of possible characters is
between 1 and 8000. The possible combinations of
characters in a
char data type are 256. The characters that
are represented depend on what language, or collation, is
defined. English, for example, is actually defined with a
Latin collation. The Latin collation provides support for
all English and western European characters.
varchar 1 byte per
character. Up
to 2GB
characters.
The
varchar data type is identical to the char data
type, but with a variable length. If a column is defined
as
char(8) , it will consume 8 bytes of storage even if
only three characters are placed in it. A
varchar
column consumes only the space it needs. Typically,
char data types are more efficient when it comes to
processing and
varchar data types are more efficient
for storage. The rule of thumb is to use
char if the data
will always be close to the defined length, but use
varchar if it will vary widely. For example, a city name
would be stored with
varchar(167) if you wanted to
allow for the longest city name in the world, which is
Krung thep mahanakhon bovorn ratanakosin
mahintharayutthaya mahadilok pop noparatratchathani
burirom udomratchanivetmahasathan amornpiman
avatarnsathit sakkathattiyavisnukarmprasit (the poetic
name of Bangkok, Thailand). Use
char for data that is
always the same. For example, you could use
char(12)
to store a domestic phone number in the United States:
(123)456 - 7890. The 8000 - byte limitation can be exceeded
by specifying the
(MAX) option (varchar(MAX)) ,
which allows for the storage of 2,147,483,647 characters
at the cost of up to 2GB storage space.
(continued)
CH001.indd 11CH001.indd 11 3/26/10 11:35:37 AM3/26/10 11:35:37 AM