Table C-1 contains a list of all SQL Server 2005 native data types along with their range, size, and sample constants.
Data Type and Synonym | Description | Range or Length | Storage Size | Sample Constant |
---|---|---|---|---|
Character Strings | ||||
char (character) | Character string | 1 to 8,000 chrs | 1 to 8,000 bytes | ‘D12-D13A36’ |
varchar (character varying) | Variable-length character string | 1 to 8,000 chrs | 1 to 8,000 bytes | ‘Toronto’ |
varchar (max) | New large value variable-length character string | Ito231-l chrs | 16 bytes + 0 to 2GB | ‘SQL Server’ |
text | Large value variable-length character string from earlier versions | Ito231-lchrs | 1 6 bytes + 0 to 2GB | ‘SQL Server’ |
Unicode Character Strings | ||||
Nchar (national character or national char) | Unicode character string | 1 to 4,000 chrs | 2 to 8,000 bytes | N ‘Sunder id’ |
Nvarchar (national character varying or national char varying) | Variable-length Unicode character string | 1 to 4,000 chrs | 2 to 8,000 bytes | N ‘Q’ |
Nvarchar (max) | New large value variable-length Unicode character string | Ito230-lchrs | 16bytes + OtolGB | N ‘HWKO ara’ |
Ntext (national text) | Large value variable-length Unicode character string from earlier versions | Ito230-lchrs | 16bytes + OtolGB | |
Date and Time | ||||
datetime | Date and time | 1-Jan-I/Sato 31-Dec-9999; precision: 3 ms | 8 bytes | ‘6/27/1998 10: 20: 17 .31’ |
smalldatetime | Small date and time | l-Jan-1900to6-Jun-2079; precision: 1 min | 4 bytes | ‘Oct 30, 1993 14: 30’ |
Integer Numbers | ||||
tinyint | Tiny integer | 0 to 255 | Ibyte | 17 |
smallint | Small integer | -32,768 to 32,767 (-215 to 21S-1) | 2 bytes | 23017 |
int | Integer | -2,147,483,648 to 2,147,483,647 (-231 to 231-1) | 4 bytes | 343013 |
bigint | Big integer | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (-263 to 263-1) | 8 bytes | 3222121343013 |
bit | Logical | 0,1, or Null | 1 byte (up to 8 bits per byte) | 1 |
Exact Numbers | ||||
numeric (decimal or dec) | Numeric or decimal | -1038 to 1038-1 (depends on precision and scale) | 5 to 17 bytes | -352.4512 |
Approximate Numbers | ||||
real | Real (single-precision) number | -3.40 1038 to 3.40 1038 | 4 bytes | -232 .212E6 |
float | Float (double-precision) number | -1.7910308 tol.79103ll8 | 8 bytes | 34 .2131343E-64 |
Monetary | ||||
smallmoney | Small monetary data type | -214,768.3648to 214,748.3647 | 4 bytes | $120.34 |
money | Monetary data type | -922,337,203,685,477.5808 to922,337,203,685,477.5807 | 8 bytes | $120000000 |
Binary | ||||
binary | Fixed-length binary string | 1 to 8,000 bytes | 1 to 8,000 bytes | OxaSdl |
varbinary | Variable-length binary string | 1 to 8,000 bytes | 1 to 8,000 bytes | OxA5F2 |
varbinary (max) | New large value variable-length binary string | 1 to 231-1 bytes | 16 + 0 to 2GB | OxA5F22362F282 |
image | Large value variable-length binary string from earlier versions | 1 to 231-1 bytes | 16 + 0 to 2GB | OxA5F22362F282 |
time stamp | Database-wide unique number | n/a | 8 bytes | n/a |
unique identifier | Globally unique identifier (GUID) | n/a | 16 bytes | |
Special | ||||
cursor | Cursor reference | n/a | n/a | n/a |
sql_variant | Variant | n/a | n/a | n/a |
table | Table | n/a | n/a | n/a |
xml | XML string | 1 to 231 - 1 chrs | 1 6 bytes + 0 to 2GB | <PartID>23151-123123123</PartID> |