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 |
Advertisements
MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.
SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.
SQL Server DBA
I am a
SQL Server DBA with almost 9 years of experience in database
technologies.
I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose
of new SQL DBA website is to offer more features and make it easy for
me to update contents on regular basis.