2

While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in the future? While switching, I would like to avoid changing datatypes of the columns that are NOT available in the new database to appropriate datatypes that are available in the new database.

In other words, are there any ANSI datatypes that will work in all the databases with the same size? That is, similar to "ANSI SQL", are there anything like "ANSI SQL Data types"?

migrated from stackoverflow.com Nov 14 '13 at 12:42

This question came from our site for professional and enthusiast programmers.

  • WHy are you planning to switch to a different database? The only times I have ever seen anyone switch was when they used COYS softeware that has to work on several backends. Even then, using universal types insteand of teh ones that work for that particular backend would save some time but create some major performance or data integrity issues. (one reason why every single COTS product I have ever had to support, and there have been a lot of them through the years, had lousy performance). So it would not be recommended to cripple youru database to save a little dev time. – HLGEM Nov 4 '13 at 18:12
  • This one makes sense, I'll go for MySQL specifics! Thanks! – Phantom Reference Nov 5 '13 at 13:32
6

For the most basic datatypes, nearly all DBMS either support the ANSI data types directly or at least allow to specify them.

The following statement:

create table products
(
   product_id    integer not null primary key,
   product_name  varchar(100),
   price         decimal(10,2)
);

works on nearly every DBMS. The data types will however be translated to the DBMS-specific ones (e.g. integer will be changed to number in Oracle, or varchar to varchar2).

When it comes to more "advanced" datatypes like CLOB, BLOB, XML, Json, or geospatial types then this doesn't hold true any more (CLOB, BLOB is pretty common, but e.g. not understood by Postgres although you can easily create a domain or a user data type with that name to make it more compatible).

The basic data types as defined by the ANSI standard are:

  • CHARACTER
  • CHARACTER VARYING (or VARCHAR)
  • CHARACTER LARGE OBJECT
  • NCHAR
  • NCHAR VARYING
  • BINARY
  • BINARY VARYING
  • BINARY LARGE OBJECT
  • NUMERIC
  • DECIMAL
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • REAL
  • DOUBLE PRECISION
  • BOOLEAN
  • DATE
  • TIME
  • TIMESTAMP
  • INTERVAL

Not all DBMS suppor all of them (MySQL doesn't have an interval data type, MySQL and SQL Server don't have a real boolean type, Oracle does not have a boolean type at all or a real DATE or TIME data type, Postgres doesn't have nchar - that list could go on and on). But the most common ones usually work quite well.

From my experience, the following ones work without (major) problems across different DBMS (although that is my experience - this is by far not a complete or definite list!)

  • VARCHAR
  • INTEGER
  • DECIMAL
  • DATE (with surprises: Oracle has it but includes a time)
  • TIMESTAMP (does something different than expected on SQL Server and MySQL)

If you stick with those, you are pretty much "universal", but anything else will give you some (unpleasant) surprises when you go from one DBMS to another.

0

Oracle tends to be the odd one out, with "NUMBER" covering bit, short, int, long, decimal, and float. It also uses CLOB and NCLOB for large text, and BLOB in place of IMAGE as used in SQL Server.

  • 1
    Not quite sure this answers the question. – Ash Burlaczenko Nov 4 '13 at 12:24
  • 1
    @AshBurlaczenko - it provides an answer to the question by pointing out exceptions. As in "it will not work because ..") – Hans Kesting Nov 4 '13 at 12:27
  • @HansKesting, the question isn't asking if something will work so I don't get your point. The question is are there any ANSI datatypes that will work in all the databases with the same size? which isn't answered by this answer. Are there any numbers that are even?, and you saying 3 isn't an even number does not answer that question. – Ash Burlaczenko Nov 4 '13 at 12:47
  • When you're defining a schema in a tool, type names matter. NVARCHAR (SQL Server) and NVARCHAR2 (Oracle) mean the same thing, but you have to use the right name in the right place, or your schema won't build. – Cylon Cat Nov 4 '13 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.