SQL portability gotchas

Oracle does not seem to have a problem with cursors, they are a huge performance problem in SQL server.

Actually pretty much all performance tuning is database specific (which is why ANSII standard code often performs very poorly compared to the better methods designed into the specifc flavor of SQL that is database specific).

Dates are another thing that seem to be handled very differntly from database to database.

Datatypes are not equivalent either. One thing that tends to get newcomers to SQL Server is that timestamp data type has absolutely nothing to do with dates and times and cannot be converted to a datatime value.

Oracle does not allow a select statement without a FROM clause. Therefore, you cannot do queries like this:

SELECT 1

Instead, you have to say that the query is from the DUAL table:

SELECT 1 FROM DUAL

Another example is generating unique (typically surrogate) primary keys.

Many databases, such as SQL Server and sqlite allow a column to be declared as an identity: typically, if the value for this column is missing on insert, the database will generate a unique value for the column.

Oracle, by contrast, has you create a sequence separate to the table and then use nextval on the sequence to generate the next value:

CREATE SEQUENCE test_seq;SELECT test_seq.nextval FROM dual;

Or, more typically:

INSERT INTO foo(id, title) VALUES (test_seq.nextval, 'bar');