I attended the SQL Server user group in Louisville. SQL Server 2008 adds many additional data types. To begin my learning, I first took inventory of the data types in 2000 and 2005. Stay tuned to this page, I will update it with 08 data types.
SQL Server Data Types
Exact numeric
| 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 numeric
| Type | From | To |
| float | -1.79E + 308 | 1.79E + 308 |
| real | -3.40E + 38 | 3.40E + 38 |
DateTime
| 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, max of 8,000 characters. |
| varchar | Variable-length non-Unicode, max of 8,000 characters. |
| varchar(max) | Variable-length non-Unicode data, max of 231 chars (2005 only). |
| text | Variable-length non-Unicode data, max of 2,147,483,647 characters. |
* Text datatype is used in previous versions of SQL Server, it is recommended to use Varchar(MAX) instead of Text datatype, as ntext, text, and image data types will be removed in a future version of Microsoft SQL Server
Unicode Character Strings
| Type | Description |
| nchar | Fixed-length Unicode data, max of 4,000 characters. |
| nvarchar | Variable-length Unicode data, max of 4,000 characters. |
| nvarchar(max) | Variable-length Unicode data, max of 230 chars (2005 only). |
| ntext | Variable-length Unicode data, max of 1,073,741,823 characters. |
Difference between nvarchar and nvarchar(MAX) datatype
nvarchar stores variable-length unicode character data whose range varies upto 4000 chars, varchar(MAX) stores variable-length character data whose range may varies beyond 4000 bytes.
Binary Strings
| Type | Description |
| binary | Fixed-length binary data, max of 8,000 bytes. |
| varbinary | Variable-length binary data, max of 8,000 bytes. |
| varbinary(max) | Variable-length binary data, max of 231 bytes (2005 only). |
| image | Variable-length binary data, max 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.