The Data types reference sheet

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.