SQL Analysis and Tips: Datatypes
 

Feature highlight: Datatypes and Functions Support
Credit: Imagenation,
O'Reilly's SQL in a Nutshell
 
Description:
This table describes the basic datatypes supported in candidate Web databases. What we see here is the start of a trend. Although all the databases support a core set of SQL'99 datatypes - no vendor is even close to complete compliance with the standard. One would expect one of the vendors to implement a superset of the SQL'99 datatypes - all of SQL'99 plus some of its own extensions. Not so.

And in fact the basic datatypes vary enough in implementation that one can see why heterogeneous joins between database tables, ODBC/JDBC links, and external data transfers can be so problematical. Hence the rise of an ironclad standard like XML Schema and XQuery language may have more success than one would have first considered possible.

SQL'99 DataTypes
SQLServer
MySQL 3.23.9
Oracle 9.1
PostGresSQL
binary - Binary LObj yes 8kb max longblob 4mb yes 4gb max -na-
bit string - bits, hex no hex -na- long raw 2gb -na-
boolean -na- -na-  -na- yes
character 8kb max 255bytes max 2kb max yes
-clob - Char LObj text, 2mb max mediumblob yes 4gb max -na-
-nchar - national yes + ntext yes nvarchar -na-
-nvchar - natl. varying yes Unicode yes yes -na-
-vchar - varying char yes yes 255 max yes 4kb yes
nulls yes yes yes yes
numeric numeric(p,s) numeric(p,s) number(p,s) numeric(p,s)
-decimal decimal(p,s) decimal(p,s) yes decimal(p,s)
-double precision float(p,s) yes yes float8
-float(p,s) yes yes float(n) float4
-integer yes+bigint 8b yes+bigint 8b int - 16bytes int4, int8
-real yes real(p,s) yes float4
-smallint yes+tinyint 1b yes+tinyint 1b yes int2
time datetime datetime datetime datetime
-date datetime datetime yes unique int. yes unique intvl
-interval smalldatetime year(2,4) yes yes
-time with timezone -na- -na- -na- yes
-timestamp rowversion yes+size yes yes
-timestamp with tzone -na- -na-   yes
Datatypes not in SQL99 standard
box -na- -na- -na- +circle,line, lseg, point, polygon
cidr -na- -na- -na- +inet, macaddr
cursor yes -na- -na- -na-
enum -na- 65k values -na- -na-
money +smallmoney -na- -na- money(9,2)
sqlvariant user object -na- sysanytype -na-
table resultset -na-  -na-  -na-
urowid rownum -na- user rowid serial
xmltype yes   yes  
Functions used on columns, fields, or variables
Aggregate:avg,count,min, max,sum,std all+ all all+ all
Builtin-6 time,date, user all all+ user, date 5
Date functions-12 10+own 12 9+own  
Math functions-8 all++ all++ all++ all++
String functions-8 some+own some+own all+own some+own
System-4 all++ all++ all++ some++
  SQL Server MySQL Oracle 9i Postgres



 
.

 
Top of Page  Tutorials Home 
©Imagenation 2001-2004