| Precedence | Data Type | Class | Range | Space Used | 7.0 | 2000 | 2005 | 2008 | Remarks |
|---|---|---|---|---|---|---|---|---|---|
|
*1 Using variable length columns will add some extra bytes to every row; 3 bytes for the first 8 variable length columns. *2 Use is discouraged; a better data type is available that serves the same purpose. |
|||||||||
| 18 | tinyint | Numeric data | 0 - 255 | 1 byte | Y | Y | Y | Y | |
| 17 | smallint | Numeric data | -32,768 - 32,767 | 2 bytes | Y | Y | Y | Y | |
| 16 | int | Numeric data | -2,147,483,648 - 2,147,483,647 | 4 bytes | Y | Y | Y | Y | |
| 14 | smallmoney | Numeric data | -214,748.3648 - 214,748.3647 | 4 bytes | Y | Y | Y | Y | Author recommends using decimal() instead |
| 12 | decimal(9,y) | Numeric data | -999,999,999 - 999,999,999 | 5 bytes | Y | Y | Y | Y | Precision up to 9 digits uses 5 bytes. Numeric is a synonym for Decimal. |
| 13 | money | Numeric data | -922,337,203,685,477.5808 - 922,337,203,685,477.5807 | 8 bytes | Y | Y | Y | Y | Author recommends using decimal() instead |
| 15 | bigint | Numeric data | -9,223,372,036,854,775,808 - 9,223,372,036,854,775,807 | 8 bytes | Y | Y | Y | ||
| 12 | decimal(19,y) | Numeric data | -9,999,999,999,999,999,999 - 9,999,999,999,999,999,999 | 9 bytes | Y | Y | Y | Y | Precision from 10 up to 19 digits uses 9 bytes |
| 12 | decimal(28,y) | Numeric data | -10^28+1 - 10^28-1 | 13 bytes | Y | Y | Y | Y | Precision from 20 up to 28 digits uses 13 bytes |
| 12 | decimal(38,y) | Numeric data | -10^38+1 - 10^38-1 | 17 bytes | Y | Y | Y | Y | Precision from 29 up to 38 digits uses 17 bytes |
| 28 | char(x) | Character data | 1 - 8,000 characters | 1 - 8000 bytes | Y | Y | Y | Y | char(1) uses 1 byte and can store 1 character |
| 27 | varchar(x) | Variable Length Character data | 0 - 8,000 characters | 2 - 8002 bytes | Y | Y | Y | Y | varchar(5) uses 2 bytes when empty and 7 bytes when occupied with 5 characters*1 |
| 27 | varchar(max) | Variable Length Binary data | 0 - 2,147,483,645 characters | 2 bytes - 2 gigabytes | Y | Y | varchar(max) uses 2 bytes when empty and 3 bytes when occupied with 1 character*1 | ||
| 21 | text | Variable Length Character data | 0 - 2,147,483,647 characters | 16 bytes - 2 gigabytes | Y | Y | Y*2 | Y*2 | text uses 16 bytes when empty and 17 bytes when occupied with 1 character |
| 26 | nchar(x) | Character data | 1 - 4,000 characters | 2 - 8000 bytes | Y | Y | Y | Y | nchar(5) uses 10 bytes and can store 5 character |
| 25 | nvarchar(x) | Variable Length Character data | 0 - 4,000 characters | 2 - 8002 bytes | Y | Y | Y | Y | nvarchar(5) uses 2 bytes when empty and 12 bytes when occupied with 5 characters*1 |
| 25 | nvarchar(max) | Variable Length Binary data | 0 - 1,073,741,822 characters | 2 bytes - 2 gigabytes | Y | Y | nvarchar(max) uses 2 bytes when empty and 4 bytes when occupied with 1 character*1 | ||
| 20 | ntext | Variable Length Character data | 0 - 1,073,741,823 characters | 16 bytes - 2 gigabytes | Y | Y | Y*2 | Y*2 | ntext uses 16 bytes when empty and 18 bytes when occupied with 1 character |
| 8 | date | Temporal data | 0001-01-01 - 9999-12-31 | 3 bytes | Y | ||||
| 9 | time(2) | Temporal data | 00:00:00.00 - 23:59:59.99 | 3 bytes | Y | Precision up to 2 digits uses 3 bytes | |||
| 7 | smalldatetime | Temporal data | 1900-01-01 00:00 - 2079-06-06 23:59 | 4 bytes | Y | Y | Y | Y | |
| 9 | time(4) | Temporal data | 00:00:00.0000 - 23:59:59.9999 | 4 bytes | Y | Precision of 3 or 4 digits uses 4 bytes | |||
| 9 | time(7) | Temporal data | 00:00:00.0000000 - 23:59:59.9999999 | 5 bytes | Y | Precision from 5 up to 7 digits uses 5 bytes | |||
| 5 | datetime2(2) | Temporal data | 0001-01-01 00:00:00.00 - 9999-12-31 23:59:59.99 | 6 bytes | Y | Precision up to 2 digits uses 6 bytes | |||
| 5 | datetime2(4) | Temporal data | 0001-01-01 00:00:00.0000 - 9999-12-31 23:59:59.9999 | 7 bytes | Y | Precision of 3 or 4 digits uses 7 bytes | |||
| 6 | datetime | Temporal data | 1753-01-01 00:00:00.000 - 9999-12-31 23:59:59.997 | 8 bytes | Y | Y | Y | Y | |
| 5 | datetime2(7) | Temporal data | 0001-01-01 00:00:00.0000000 - 9999-12-31 23:59:59.9999999 | 8 bytes | Y | Precision from 5 up to 7 digits uses 8 bytes | |||
| 4 | datetimeoffset(2) | Temporal data | 0001-01-01 00:00:00.00 - 9999-12-31 23:59:59.99 | 8 bytes | Y | Precision up to 2 digits uses 8 bytes | |||
| 4 | datetimeoffset(4) | Temporal data | 0001-01-01 00:00:00.0000 - 9999-12-31 23:59:59.9999 | 9 bytes | Y | Precision of 3 or 4 digits uses 9 bytes | |||
| 4 | datetimeoffset(7) | Temporal data | 0001-01-01 00:00:00.0000000 - 9999-12-31 23:59:59.9999999 | 10 bytes | Y | Precision from 5 up to 7 digits uses 10 bytes | |||
| 19 | bit | Binary data | 1 bit | 1 byte | Y | Y | Y | Y | bit uses 1 byte and can store 1 bit, 8 bits use 1 byte and can collectively store 8 bits. Because of nullability, 8 bit columns require more space than 1 binary(1) column |
| 30 | binary(x) | Binary data | 8 - 64,000 bits | 1 - 8000 bytes | Y | Y | Y | Y | As of SQL Server 2005 binary(1) uses 1 byte and can store 8 bits. SQL Server 2000 and earlier uses 4 more bytes |
| 29 | varbinary(x) | Variable Length Binary data | 0 - 64,000 bits | 2 - 8002 bytes | Y | Y | Y | Y | As of SQL Server 2005 varbinary(5) uses 2 bytes when empty and 7 bytes when occupied with 33 bits or more. SQL Server 2000 and earlier uses 2 more bytes*1 |
| 29 | varbinary(max) | Variable Length Binary data | 0 - 17,179,869,160 bits | 2 bytes - 2 gigabytes | Y | Y | varbinary(max) uses 2 bytes when empty and 3 bytes when occupied with up to 8 bits *1 | ||
| 22 | image | Variable Length Binary data | 0 - 2,147,483,647 bytes | 16 bytes - 2 gigabytes | Y | Y | Y*2 | Y*2 | image uses 16 bytes when empty and 17 bytes when occupied with 1 byte |
| 24 | uniqueidentifier | Globally Unique Identifier | 1 GUID | 16 bytes | Y | Y | Y | Y | |
| 23 | timestamp | Database-wide Unique Number | 1 number | 8 bytes | Y | Y | Y | Y | |
| 11 | float(24) | Approximate Numeric data | -3.40E+38 - 3.40E+38 | 4 bytes | Y | Y | Y | Y | Mantissa up to 24 bits uses 4 bytes. Real is a synonym for Float(24). Author recommends never to use this data type for money amounts |
| 10 | float(53) | Approximate Numeric data | -1.79E+308 - 1.79E+308 | 8 bytes | Y | Y | Y | Y | Mantissa from 25 up to 53 bits uses 8 bytes. Author recommends never to use this data type for money amounts |
| 3 | xml | XML data | 2 bytes - 2 gigabyes | Y | Y | The "stored representation" cannot exceed 2 gigabytes in size | |||
| 2 | sql_variant | Depends | Depends | 16 - 8,016 bytes | Y | Y | Y | Author recommends avoiding this data type | |
| 1 | User defined data type | UDT | Depends | unknown | Y | Y | Author recommends avoiding this data type | ||
| 1 | geography(NULL) | Spatial data | empty | 2 bytes | Y | *1 | |||
| 1 | geography('Point') | Spatial data | 1 point | 24 bytes | Y | *1 | |||
| 1 | geography('Line') | Spatial data | LineString with 1 line | 40 bytes | Y | *1 | |||
| 1 | geography('LineString') | Spatial data | LineString with multiple lines | 82 - ? bytes | Y | Uses 34 bytes plus 16 bytes for every point in the LineString*1 | |||
| 1 | geography('Polygon') | Spatial data | 82 - ? bytes | Y | Uses 34 bytes plus 16 bytes for every point in the Polygon*1 | ||||
Back to SQL Server main menu. Mail your comments to gertjans@xs4all.nl.