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:
|
Data Type |
Size |
Description |
|
Integers
|
|
|
|
bigint |
8 bytes |
Holds integer (whole
number) data from -2^63 (-9,223,372,036,854,775,808)
through 2^63-1 (9,223,372,036,854,775,807).
|
|
int |
4 bytes |
Holds integer (whole
number) data from -2^31 (-2,147,483,648) through 2^31 -
1 (2,147,483,647). |
|
smallint |
2 bytes |
Holds integer data from
2^15 (-32,768) through 2^15 - 1 (32,767). |
|
tinyint |
1 byte |
Holds integer data from 0
through 255. |
|
bit |
1 byte for up to 8 bit
columns |
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). |
Page 3: Data Types (Continued)
»
See All Articles by Columnist Michael Aubert
» 
Comment and Contribute
