SQL Server: Data types

Some (all?) SQL Server data types

  Data type  .NET Framework equivalent type  Comment  Host file data type

  bigint  Int64   SQLBIGINT

  binary    SQLBINARY

  bit    which can be used instead of the non existing boolean datatype  SQLBIT

  char    SQLCHAR

  cursor   

  date   

  datetime  DateTime   SQLDATETIME

  datetime2  DateTime  

  datetimeoffset  DateTimeOffset  

  decimal  SqlDecimal   SQLDECIMAL

  double precision   A synonym for float(53) 

  float  Single / Double    SQLFLT8

  geography   

  geometry   

  hierarchyid   

  image   Binary data with a maximal length of 2³⁰-1 bytes. Compare with ntext and text.  SQLBINARY

  integer  Int32  abbreviated with int  SQLINT

  money, smallmoney  Decimal   SQLMONEY, SQLMONEY4

  nchar    SQLNCHAR

  ntext, text   Variable-length string-data with maximal length of 2³⁰-1 bytes (ntext) or characters (text). ntext is stored as Unicode, text in the code page of the Server. Compare with image.  SQLNCHAR, SQLCHAR

  numeric    SQLNUMERIC

  nvarchar    SQLNCHAR

  real  Single  real is a synonym for float(24)  SQLFLT4

  rowversion   Not found in sys.systypes 

  smalldatetime  DateTime   SQLDATETIM4

  smallint  Int16   SQLSMALLINT

  sql_variant   This type allows to store values of different data types (Same concept as the VBA datatype variant).  SQLVARIANT

  sysname   

  table   Not found in sys.systypes 

  time  TimeSpan  

  timestamp   A deprecated synonym for rowversion  SQLBINARY

  tinyint  Byte   SQLTINYINT

  uniqueidentifier   A 16-bit GUID  SQLUNIQUEID

  varbinary    SQLBINARY

  varchar    SQLCHAR

xml

Oracle users: especially note that there is no number data type.

A variable is associated with a data type with the declare statement.

The column host file data types maps data types to those that are used in format files.

TODO

T-SQL functions:

type_id(…) returns the id for a specific type name
type_name(…) returns the unqualified type name of a specified type ID.
columnproperty(…) returns column or parameter information

Data type	.NET Framework equivalent type	Comment	Host file data type
`bigint`	`Int64`		`SQLBIGINT`
`binary`			`SQLBINARY`
`bit`		which can be used instead of the non existing boolean datatype	`SQLBIT`
`char`			`SQLCHAR`
`cursor`
`date`
`datetime`	`DateTime`		`SQLDATETIME`
`datetime2`	`DateTime`
`datetimeoffset`	`DateTimeOffset`
`decimal`	`SqlDecimal`		`SQLDECIMAL`
`double precision`		A synonym for `float(53)`
`float`	`Single` / `Double`		`SQLFLT8`
`geography`
`geometry`
`hierarchyid`
`image`		Binary data with a maximal length of 2³⁰-1 bytes. Compare with `ntext` and `text`.	`SQLBINARY`
`integer`	`Int32`	abbreviated with `int`	`SQLINT`
`money`, `smallmoney`	`Decimal`		`SQLMONEY`, `SQLMONEY4`
`nchar`			`SQLNCHAR`
`ntext`, `text`		Variable-length string-data with maximal length of 2³⁰-1 bytes (`ntext`) or characters (`text`). `ntext` is stored as Unicode, `text` in the code page of the Server. Compare with `image`.	`SQLNCHAR`, `SQLCHAR`
`numeric`			`SQLNUMERIC`
`nvarchar`			`SQLNCHAR`
`real`	`Single`	`real` is a synonym for `float(24)`	`SQLFLT4`
`rowversion`		Not found in `sys.systypes`
`smalldatetime`	`DateTime`		`SQLDATETIM4`
smallint	`Int16`		`SQLSMALLINT`
sql_variant		This type allows to store values of different data types (Same concept as the VBA datatype `variant`).	`SQLVARIANT`
`sysname`
`table`		Not found in `sys.systypes`
`time`	`TimeSpan`
`timestamp`		A deprecated synonym for `rowversion`	`SQLBINARY`
`tinyint`	`Byte`		`SQLTINYINT`
`uniqueidentifier`		A 16-bit GUID	`SQLUNIQUEID`
`varbinary`			`SQLBINARY`
`varchar`			`SQLCHAR`
`xml`

SQL Server: Data types

TODO

See also