Skip to content

Latest commit

 

History

History
94 lines (86 loc) · 6.77 KB

MS SQL data types.md

File metadata and controls

94 lines (86 loc) · 6.77 KB

MS SQL data types

Exact numerics

bigint, int, smallint, tinyint Exact-number data types that use integer data. Data type Range Storage tinyint 0 to 255 1 Byte smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes decimal, numeric Numeric data types that have fixed precision and scale. Decimal and numeric are synonyms and can be used interchangeably. decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )] Precision Storage 1 - 9 5 Bytes 10-19 9 Bytes 20-28 13 Bytes 29-38 17 Bytes bit An integer data type that can take a value of 1, 0, or NULL. smallmoney, money Data types that represent monetary or currency values. Data type Range Storage money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 (-922,337,203,685,477.58 to 922,337,203,685,477.58 for Informatica. Informatica only supports two decimals, not four.) 8 bytes smallmoney - 214,748.3648 to 214,748.3647 4 bytes

Approximate numerics

float, real Approximate-number data types for use with floating point numeric data. n value Precision Storage 1-24 7 digits 4 bytes 25-53 15 digits 8 bytes Data type Range Storage float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Depends on the value of n real - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 4 Bytes

Date and time

date Defines a date in SQL Server. Data type Default string literal Down-level ODBC Down-level OLEDB Down-level JDBC Down-level SQLCLIENT format passed to
down-level client time hh:mm:ss[.nnnnnnn] SQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTRor DBTYPE_STR Java.sql.String String or SqString date YYYY-MM-DD SQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTRor DBTYPE_STR Java.sql.String String or SqString datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn] SQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTRor DBTYPE_STR Java.sql.String String or SqString datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] SQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTRor DBTYPE_STR Java.sql.String String or SqString [+|-]hh:mm smalldatetime Defines a date that is combined with a time of day.

Character strings

char, varchar Character data types that are either fixed-size, char, or variable-size, varchar. text Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

Unicode character strings

nchar, nvarchar Character data types that are either fixed-size, nchar, or variable-size, nvarchar. ntext Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

Binary strings

binary, varbinary Binary data types of either fixed length or variable length. Data type Use when ... binary the sizes of the column data entries are consistent. varbinary the sizes of the column data entries vary considerably. varbinary(max) the column data entries exceed 8,000 bytes. image Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

Other data types

cursor A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. table Is a special data type used to store a result set for processing at a later time. hierarchyid The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy. sql_variant A data type that stores values of various SQL Server-supported data types. rowversion Is a data type that exposes automatically generated, unique binary numbers within a database. uniqueidentifier Is a 16-byte GUID. xml It's the data type that stores XML data. You can store xml instances in a column, or a variable of xml type. Spatial Types - geometry The planar spatial data type, geometry, is implemented as a common language runtime (CLR) data type in SQL Server. This type represents data in a Euclidean (flat) coordinate system. Spatial Types - geography The geography spatial data type, geography, is implemented as a .NET common language runtime (CLR) data type in SQL Server.