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

DatatypeMinMaxStorage8910TypeNotesBigint-2^632^63-18 bytes   Exact numeric Int-2,147,483,6482,147,483,6474 bytes   Exact numeric Smallint-32,76832,7672 bytes   Exact numeric Tinyint02551 bytes   Exact numeric Bit011 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+110^38–1Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes    Exact numericDecimal 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.Numericno       Money-2^63 / 100002^63-1 / 100008 bytes   Exact numeric Smallmoney-214,748.3648214,748.36474 bytes   Exact numeric Float-1.79E + 3081.79E + 3084 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53   Approximate numericsPrecision is specified from 1 to 53.Real-3.40E + 383.40E + 384 bytes   Approximate numericsPrecision is fixed to 7.Datetime1753-01-01 00:00:00.0009999-12-31 23:59:59.9978 bytes   Date and timeIf you are running SQL Server 2008 and need milliseconds precision, use datetime2(3) instead to save 1 byte.Smalldatetime 1900-01-01 00:002079-06-06 23:59    Date and time Date0001-01-019999-12-31 nono Date and time Time00:00:00.000000023:59:59.9999999 nono Date and timeSpecifying 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.Datetime20001-01-01 00:00:00.00000009999-12-31 23:59:59.9999999Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytesnono Date and timeCombines the date datatype and the time datatype into one. The precision logic is the same as for the time datatype.Datetimeoffset0001-01-01 00:00:00.0000000 -14:009999-12-31 23:59:59.9999999 +14:00Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytesnono Date and timeIs a datetime2 datatype with the UTC offset appended.Char0 chars8000 charsDefined width   Character stringFixed widthVarchar0 chars8000 chars2 bytes + number of chars   Character stringVariable widthVarchar(max)0 chars2^31 chars2 bytes + number of charsno  Character stringVariable widthText0 chars2,147,483,647 chars4 bytes + number of chars   Character stringVariable widthNchar0 chars4000 charsDefined width x 2   Unicode character stringFixed widthNvarchar0 chars4000 chars    Unicode character stringVariable widthNvarchar(max)0 chars2^30 chars no  Unicode character stringVariable widthNtext0 chars1,073,741,823 chars    Unicode character stringVariable widthBinary0 bytes8000 bytes    Binary stringFixed widthVarbinary0 bytes8000 bytes    Binary stringVariable widthVarbinary(max)0 bytes2^31 bytes no  Binary stringVariable widthImage0 bytes2,147,483,647 bytes    Binary stringVariable widthSql_variant      OtherStores values of various SQL Server-supported data types, except text, ntext, and timestamp.Timestamp      OtherStores a database-wide unique number that gets updated every time a row gets updated.Uniqueidentifier      OtherStores a globally unique identifier (GUID).Xml   no  OtherStores XML data. You can store xml instances in a column or a variable.Cursor      OtherA reference to a cursor.Table      OtherStores a result set for later processing.