sql types

Notes on SQL types, particular character/binary types, particularly for different ODBC drivers.

https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/converting-data-from-c-to-sql-data-types

PostgreSQL

Reference: https://www.postgresql.org/docs/current/datatype.html

SQL Type	ODBC Type	Max
char(n)		~10M chars
varchar(n)		~10M chars
text		2^30
bpchar		2^30, blank-trimmed
varchar		2^30

MySQL

Reference: https://dev.mysql.com/doc/refman/8.0/en/data-types.html

SQL Type	ODBC Type	Max
char(n)		255 chars
varchar(n)		64K bytes
binary(n)		255 bytes ?
varbinary(n)		64K bytes ?
text variants	SQL_LONGVARCHAR	255, 64K, 16M, 4G bytes
blob variants	SQL_LONGVARBINARY	255, 64K, 16M, 4G bytes

MS SQL Server

Reference: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql

SQL Type	ODBC Type	Max
char	SQL_CHAR	8000 bytes
varchar	SQL_VARCHAR	8000 bytes / max=2^31
text	SQL_LONGVARCHAR	2^31 chars
nchar	SQL_WCHAR	4000 "chars" (UTF-16 codes)
nvarchar	SQL_WVARCHAR	4000 "chars" / max=2^31
ntext	SQL_WLONGVARCHAR	2^30 chars
binary	SQL_BINARY	8000 bytes
varbinary	SQL_VARBINARY	8000 bytes / max=2^31
image	SQL_LONGVARBINARY	2^31 bytes

• char, varchar
  • Since SQL Server 2019: "when a UTF-8 enabled collation is used, these data types store the full range of Unicode character data, and use the UTF-8 character encoding"
    • see https://techcommunity.microsoft.com/blog/sqlserver/introducing-utf-8-support-for-sql-server/734928
    • example collation: COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8
    • set database collation: ALTER DATABASE $dbname COLLATE $collation ;
    • get collation:
      • SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));
      • SELECT CONVERT (varchar(256), DATABASEPROPERTYEX('database_name','collation'));
  • If n not specified for CAST or CONVERT, defaults to 30.
  • Use database default collation unless overridden with COLLATE clause.
• ntext, text, and image
  • deprecated: use nvarchar(max), varchar(max), and varbinary(max) instead
  • text is "non-Unicode" data, unclear if affected by db collation
• nchar, nvarchar
  • Since SQL Server 2012: UTF-16 or UCS-2
• conversions

Oracle

References:

• https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html
• https://docs.oracle.com/en/database/oracle/oracle-database/26/odbcd/using-oracle-database-odbc-driver.html

SQL Type	ODBC Type	Max (SQL)
varchar2(n)	SQL_VARCHAR	4000 bytes / 32K if "extended"
nvarchar2(n)	SQL_WVARCHAR	4000 bytes / 32K if "extended"
raw(n)	SQL_VARBINARY	2000 bytes / 32K if "extended
char(n)	SQL_CHAR	2000 bytes
nchar(n)	SQL_WCHAR	2000 bytes
clob		2^32 blocks (?)
nclob		2^32 blocks (?)
blob		2^32 blocks (?)
long (!)	SQL_LONGVARCHAR, SQL_WLONGVARCHAR
long raw (!)	SQL_LONGVARBINARY

• char, varchar2
  • uses database character set, specified when db created
• nchar, nvarchar2
  • uses "national character set" (AL16UTF16 or UTF8)
• long is deprecated; use {c,nc,b}lob instead
• long raw is deprecated; use blob instead
• raw is variable-length binary
• comparison rules
• ODBC: "All conversions in Appendix D of the Microsoft ODBC 3.52 Software Development Kit and Programmer's Reference are supported for the ODBC SQL data types listed from a call to SQLGetInfo with the appropriate information type."

DB2

References:

• https://www.ibm.com/docs/en/db2/12.1.x?topic=le-data-types
• https://www.ibm.com/docs/en/i/7.6.0?topic=iaodsd-odbc-data-types-how-they-correspond-db2-i-database-types

SQL Type	ODBC Type	Max
char(n)	SQL_CHAR	255 "code units"
varchar(n)	SQL_VARCHAR	32K bytes
clob	SQL_LONGVARCHAR	2^31 bytes
graphic(n)	SQL_CHAR / SQL_WCHAR	127 units
vargraphic	SQL_VARCHAR / SQL_WVARCHAR	32K bytes
dbclob	SQL_LONGVARCHAR / SQL_WLONGVARCHAR	2^31 bytes
nchar(n)	SQL_WCHAR	alias for char instance
nvarchar(n)	SQL_WVARCHAR	alias for varchar instance
nclob	SQL_WLONGVARCHAR	alias for clob instance
binary(n)	SQL_BINARY	255 bytes
varbinary(n)	SQL_VARBINARY	32K bytes
blob	SQL_LONGVARBINARY	2^31

• char, varchar, clob
  • "The functions in the SYSFUN schema taking a VARCHAR as an argument will not accept VARCHARs greater than 4,000 bytes long as an argument..."
  • Can be UTF-8 ("CCSID for UTF-8 is 1208")
• graphic, vargraphic, dbclob
  • holds "double-byte character data"
• nchar, nvarchar, nclob
  • represents character data in UTF-8 or UTF-16BE
• promotion of data types
• casting between types

Firebird

References:

• https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref50/fblangref50-datatypes.html

SQL Type	ODBC Type	Max
char(n)		32K bytes
varchar(n)		32K bytes
binary(n)		32K bytes
varbinary(n)		32K bytes
blob		~2^32 bytes

• character data types
  • character set UTF8
  • BINARY is alias for CHAR CHARACTER SET OCTETS
  • nchar/nvarchar are aliases for char/varchar with character set ISO8859_1, aka Latin-1 (8 bit)
• binary data types
  • blob has subtype (binary or text), charset, segment size (irrelevant?), collation

MS Access

References:

• https://support.microsoft.com/en-us/access/data-types-for-access-desktop-databases
• https://learn.microsoft.com/en-us/sql/odbc/microsoft/microsoft-access-data-types
• https://learn.microsoft.com/en-us/sql/odbc/microsoft/sqlconfigdatasource-access-driver

SQL Type	ODBC Type	Max
short text / text	SQL_VARCHAR / SQL_WVARCHAR	255 chars?
long text / memo	SQL_LONGVARCHARR / SQL_WLONGVARCHAR	~1G bytes
long binary	SQL_LONGBINARY
varbinary	SQL_VARBINARY