SQL Server 2000 and SQL Server 2005 data types
The right SQL Server data type is crucial to the performance of
a production server, especially when the databases on the server get
bigger in size and number of rows. Even if an application seems to be
performaning well, a poor choice of a data type in a certain column
can slowly start degrading the performance of the application. This
list provides details on the various data types spported by both
SQL Server 2000 and SQL Server 2005.
Exact numerics
| Type |
From |
To |
| bigint |
-9,223,372,036,854,775,808 |
9,223,372,036,854,775,807 |
| int |
-2,147,483,648 |
2,147,483,647 |
| smallint |
-32,768 |
32,767 |
| tinyint |
0 |
255 |
| bit |
0 |
1 |
| decimal |
-10^38 +1 |
10^38 –1 |
| numeric |
-10^38 +1 |
10^38 –1 |
| money |
-922,337,203,685,477.5808 |
+922,337,203,685,477.5807 |
| smallmoney |
-214,748.3648 |
+214,748.3647 |
numeric and decimal
are Fixed precision and scale data types and are functionally equivalent.
Approximate numerics
| Type |
From |
To |
| float |
-1.79E + 308 |
1.79E + 308 |
| real |
-3.40E + 38 |
3.40E + 38 |
datetime and smalldatetime
| Type |
From |
To |
| datetime (3.33 milliseconds accuracy) |
Jan 1, 1753 |
Dec 31, 9999 |
| smalldatetime (1 minute accuracy) |
Jan 1, 1900 |
Jun 6, 2079 |
Character Strings
| Type |
Description |
| char |
Fixed-length non-Unicode character
data with a maximum length of 8,000 characters. |
| varchar |
Variable-length non-Unicode
data with a maximum of 8,000 characters. |
| varchar(max) |
Variable-length non-Unicode
data with a maximum length of 231 characters
(SQL Server 2005 only). |
| text |
Variable-length non-Unicode
data with a maximum length of 2,147,483,647 characters. |
Unicode Character Strings
| Type |
Description |
| nchar |
Fixed-length Unicode data with
a maximum length of 4,000 characters. |
| nvarchar |
Variable-length Unicode data
with a maximum length of 4,000 characters. |
| nvarchar(max) |
Variable-length Unicode data
with a maximum length of 230 characters (SQL
Server 2005 only). |
| ntext |
Variable-length Unicode data
with a maximum length of 1,073,741,823 characters. |
Binary Strings
| Type |
Description |
| binary |
Fixed-length binary data with
a maximum length of 8,000 bytes. |
| varbinary |
Variable-length binary data
with a maximum length of 8,000 bytes. |
| varbinary(max) |
Variable-length binary data
with a maximum length of 231 bytes (SQL Server
2005 only). |
| image |
Variable-length binary data
with a maximum length of 2,147,483,647 bytes. |
Other Data Types
- sql_variant: Stores values of various SQL Server-supported
data types, except text, ntext, and timestamp.
- timestamp: Stores a database-wide unique number that
gets updated every time a row gets updated.
- uniqueidentifier: Stores a globally unique identifier
(GUID).
- xml: Stores XML data. You can store xml instances
in a column or a variable (SQL Server 2005 only).
- cursor: A reference to a cursor.
- table: Stores a result set for later processing.
|
