Featured Database Articles

Every column in your table must
have a "data type," which is simply a property that defines
what type of data is stored in that column. In addition, a
data type will reject data that is not of the correct type
(i.e. attempting to store a letter in a data type designed
for numbers). SQL Server has over 25 different data types --
some with more options than others. Let's look at the
different data types and the options for each:

Holds integer data with
either a 1 or 0 value. Each set of up to 8 bit columns
requires 1 byte. So if there are anywhere from 1 to 8
bit columns in a table, the storage space you will need
is 1 byte. If there are anywhere from 9 to 16 bit
columns in a table, the storage space you will need is
2 bytes. And so on...

Decimal

decimal

Anywhere from 5 to 17 bytes depending on the precision

Holds fixed precision and scale numbers. When maximum
precision is used, valid values are from - 10^38 +1
through 10^38 - 1. The Precision specifies the
maximum total number of decimal digits that can be
stored, both to the left and to the right of the
decimal point. The precision must be a value from 1
through
38. The Scale specifies the maximum number of
decimal digits that can be stored to the right of the
decimal point. Scale must be a value from 0 through
Precision. Examples:
if precision is set to 10 and scale is set to 3 the
smallest (other than 0)/ largest number we could store
would be 0.001 / 9999999.999
if precision is set to 8 and scale is set to 6 the
smallest (other than 0)/ largest number we could store
would be 0.000001 / 99.999999

numeric

-

Same as decimal data type

Money

money

8 bytes

Holds monetary data values
from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1
(922,337,203,685,477.5807), with accuracy to one
ten-thousandth of a monetary unit

smallmoney

4 bytes

Holds monetary data values
from -214,748.3648 through +214,748.3647, with accuracy
to a ten-thousandth of a monetary unit.

Approximate

float(n)

Anywhere from 4 to 8 bytes
depending on the precision

Holds floating precision
number data from -1.79E + 308 through 1.79E + 308. The
value n is the number of bits used to store the
mantissa of the float number and can range from 1 to 53

real

4 bytes

Holds floating precision
number data from -3.40E + 38 through 3.40E + 38. Real
is the same as float(24).

Date and Time

datetime

8 bytes

Holds date and time data
from January 1, 1753, through December 31, 9999, with
an accuracy of three-hundredths of a second, or 3.33
milliseconds.

smalldatetime

4 bytes

Date and time data from
January 1, 1900 through June 6, 2079, with accuracy to
the minute.

Strings -

non-Unicode

char(n)

n bytes

Holds fixed-length
non-Unicode character data with length of n characters,
where n is a value from 1 through 8000. If less than n
number of characters are entered n bytes are
still required because unused character spaces get
padded to the end to make them n bytes long.

varchar(n)

Depends on the length of
data entered - 1 byte per character

Holds variable-length non-Unicode character data with a
length of n characters, where n is a value from 1
through 8000. The storage size is the actual length in
bytes of the data entered, not n bytes.

text

16 bytes for the pointer

Variable-length non-Unicode
data with a maximum length of 2^31 - 1 (2,147,483,647)
characters. A text column entry can hold up to 2^31 - 1
characters. It is a pointer to the location of the data
value, the data is stored separately from the table
data.

Strings -

Unicode

nchar(n)

2 bytes * n

Holds fixed-length Unicode
character data of n characters, where n is a value from
1 through 4000. Unicode characters use 2 bytes per
character and can support all international characters.
If less than n number of characters are entered n bytes
are still required because unused character spaces get
padded to the end to make them n bytes long.

nvarcher(n)

Depends on the length of
data entered - 2 byte per character

Holds variable-length
Unicode data of n characters, where n is a value from 1
through 4000. Unicode characters use 2 bytes per
character and can support all international characters.
The storage size is the actual length in bytes * 2 of
the data entered, not n bytes.

ntext

16 bytes for the pointer

Holds variable-length
Unicode data with a maximum length of 2^30 - 1
(1,073,741,823) characters. The column entry for ntext
is a pointer to the location of the data. The data is
stored separately from the table data

Binary

binary(n)

n + 4 bytes

Holds fixed-length binary
data of n bytes, where n is a value from 1 through
8000. Use binary when column data entries are
consistent in size.

varbinary(n)

Depends on the length of
data entered + 4 bytes

Holds variable-length
binary data of n bytes, where n is a value from 1
through 8000. Use varbinary when column data entries
are inconsistent in size.

image

16 bytes for the pointer

Used for variable-length
binary data longer than 8000 bytes, with a maximum of
2^31 - 1 bytes. An image column entry is a pointer to
the location of the image data value. The data is
stored separately from the table data

Other

sql_variant

size varies

A column of type
sql_variant may contain rows of different data types.
For example, a column defined as sql_variant can store
int, binary, and char values. The only types of values
that cannot be stored using sql_variant are text, ntext,
image, timestamp, and sql_variant.

timestamp

8 bytes

Timestamp is a data type
that exposes automatically generated binary numbers,
which are guaranteed to be unique within a database.
Timestamp is used typically as a mechanism for
version-stamping table rows. Each table can have only
one timestamp column.

uniqueidentifier

16 bytes

Stores a 16-byte binary
value that is a globally unique identifier (GUID).