SQL Server Data Types 2000, 2005 and 2008
In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.
SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data types in Transact-SQL or the Microsoft .NET Framework. Alias data types are based on the system-supplied data types. (source MSDN)
The Data types in SQL Server are organized into categories:
| Exact Numerics |
Approximate Numerics |
| Date and Time |
Character Strings |
| Unicode Character Strings |
Binary Strings |
| Other data types |
|
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 |
Approximate Numerics
| Type |
From |
To |
| float |
-1.79E + 308 |
1.79E + 308 |
| real |
-3.40E + 38 |
3.40E + 38 |
Date and Time
| Type |
From |
To |
| datetime (3.33 milliseconds accuracy) |
Jan 1, 1753 |
Dec 31, 9999 |
| smalldatetime (1 minute accuracy) |
Jan 1, 1900 |
Jun 6, 2079 |
| date (1 day accuracy) |
Jan 1, 0001 |
Dec 31, 9999 |
| datetimeoffset |
Jan 1, 0001 |
Dec 31, 9999 |
| datetime2 (100 nanoseconds accuracy) |
Jan 1, 0001 |
Dec 31, 9999 |
| time (100 nano seconds accuracy) |
00:00:00.0000000 |
23:59:59.9999999 |
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. |
| 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. |
| 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. |
| image |
Variable-length binary data with a maximum length of 2,147,483,647 bytes. |
Other Data Types
| Type |
Description |
| 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 new row is inserted or existing 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. |
| cursor |
A reference to a cursor |
| table |
Stores a result set for later processing |