Differences between databases, e.g. Oracle and SQL Server
Building a wholly database-independent application that is highly scalable is extremely hard - you have to know how each database works in great detail.
The area that most databases differ on is concurrency and locking. If you get this wrong, then your database either will not run in a multi-user environment or it will run slowly.
- see the book 'expert one-on-one Oracle' by Thomas Kyte Chapter 1.
In Oracle, when you want a repeatable read you set the isolation level to serializable, you do not use SELECT FOR UPDATE. SELECT FOR UPDATE is the method used in SQL Server, DB2 and Informix. In Oracle SELECT FOR UPDATE stops concurrent transactions and if you have MTS turned on it will grind the whole database to a holt.
Oracle and SQL Server/Sybase treat Nulls differently. In Oracle you cannot compare NULLS so select * from dual where null=null; returns 'no rows selected. In SQL Server null = null so a row would have been returned.
Oracle will also not index a null entry (since it uses B*Tree indexes) where SQL Server would. These are both ANSI SQL compliant, just slightlt different interpretations.
SQL92 standard has 4 levels.
Entry-level - same features as Oracle 7.
Transitional
Intermediate - Dynamic SQL, CASE, CAST, cascade DELETE for referential integrity, DATE & TIME datatypes, variable length character strings.
Full - Connection management, temporary tables, deferrable integrity constraints, BIT string data types, derived tables in the FROM clause, subqueries in CHECK clauses.
So two SQL92 compliant databases (most are only entry level) can have different implementations of SQL. If you use above entry level SQL you will hit problems when you port the database.
An example of this is the method used to generate a unique key. Oracle uses an object called a SEQUENCE. Informix has a SERIAL datatype. Sybase and SQL Server have an IDENTITY type.
This shows that it is not always possible or desirable to strive for database independence, even if you work really hard you will not end up with an implementation that operates the same in every database. Eg. In Oracle a SELECT COUNT(*) FROM T never blocks a writer, in other databases that query plus 2 simple updates can cause a deadlock.