The Data types reference sheet
The columns 8, 9, 10 shows what versions of SQL Server that supports the data type
- 8 = SQL Server 2000
- 9 = SQL Server 2005
- 10 = SQL Server 2008
Datatype | Min | Max | Storage | 8 | 9 | 10 | Type | Notes |
---|---|---|---|---|---|---|---|---|
Bigint | -2^63 | 2^63-1 | 8 bytes | Exact numeric | ||||
Int | -2,147,483,648 | 2,147,483,647 | 4 bytes | Exact numeric | ||||
Smallint | -32,768 | 32,767 | 2 bytes | Exact numeric | ||||
Tinyint | 0 | 255 | 1 bytes | Exact numeric | ||||
Bit | 0 | 1 | 1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc... | Exact numeric | ||||
Decimal | -10^38+1 | 10^38–1 | Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes | Exact numeric | Decimal and numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For booth the minimum is 1 and the maximum is 38. | |||
Numeric | no | |||||||
Money | -2^63 / 10000 | 2^63-1 / 10000 | 8 bytes | Exact numeric | ||||
Smallmoney | -214,748.3648 | 214,748.3647 | 4 bytes | Exact numeric | ||||
Float | -1.79E + 308 | 1.79E + 308 | 4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53 | Approximate numerics | Precision is specified from 1 to 53. | |||
Real | -3.40E + 38 | 3.40E + 38 | 4 bytes | Approximate numerics | Precision is fixed to 7. | |||
Datetime | 1753-01-01 00:00:00.000 | 9999-12-31 23:59:59.997 | 8 bytes | Date and time | If you are running SQL Server 2008 and need milliseconds precision, use datetime2(3) instead to save 1 byte. | |||
Smalldatetime | 1900-01-01 00:00 | 2079-06-06 23:59 | Date and time | |||||
Date | 0001-01-01 | 9999-12-31 | no | no | Date and time | |||
Time | 00:00:00.0000000 | 23:59:59.9999999 | no | no | Date and time | Specifying the precision is possible. TIME(3) will have milliseconds precision. TIME(7) is the highest and the default precision. Casting values to a lower precision will round the value. | ||
Datetime2 | 0001-01-01 00:00:00.0000000 | 9999-12-31 23:59:59.9999999 | Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytes | no | no | Date and time | Combines the date datatype and the time datatype into one. The precision logic is the same as for the time datatype. | |
Datetimeoffset | 0001-01-01 00:00:00.0000000 -14:00 | 9999-12-31 23:59:59.9999999 +14:00 | Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytes | no | no | Date and time | Is a datetime2 datatype with the UTC offset appended. | |
Char | 0 chars | 8000 chars | Defined width | Character string | Fixed width | |||
Varchar | 0 chars | 8000 chars | 2 bytes + number of chars | Character string | Variable width | |||
Varchar(max) | 0 chars | 2^31 chars | 2 bytes + number of chars | no | Character string | Variable width | ||
Text | 0 chars | 2,147,483,647 chars | 4 bytes + number of chars | Character string | Variable width | |||
Nchar | 0 chars | 4000 chars | Defined width x 2 | Unicode character string | Fixed width | |||
Nvarchar | 0 chars | 4000 chars | Unicode character string | Variable width | ||||
Nvarchar(max) | 0 chars | 2^30 chars | no | Unicode character string | Variable width | |||
Ntext | 0 chars | 1,073,741,823 chars | Unicode character string | Variable width | ||||
Binary | 0 bytes | 8000 bytes | Binary string | Fixed width | ||||
Varbinary | 0 bytes | 8000 bytes | Binary string | Variable width | ||||
Varbinary(max) | 0 bytes | 2^31 bytes | no | Binary string | Variable width | |||
Image | 0 bytes | 2,147,483,647 bytes | Binary string | Variable width | ||||
Sql_variant | Other | Stores values of various SQL Server-supported data types, except text, ntext, and timestamp. | ||||||
Timestamp | Other | Stores a database-wide unique number that gets updated every time a row gets updated. | ||||||
Uniqueidentifier | Other | Stores a globally unique identifier (GUID). | ||||||
Xml | no | Other | Stores XML data. You can store xml instances in a column or a variable. | |||||
Cursor | Other | A reference to a cursor. | ||||||
Table | Other | Stores a result set for later processing. |