My opportunity has me focusing on finishing a back finish for Oracle for any Python ORM. I am surprised about just how much in a different way RDBMSes do things for the easy stuff. I have many userful stuff here concerning the variations between Oracle along with other RDBMSes. Just from sheer curiosity, Let me find out more.

What exactly are some common "gotchas" when it comes to porting SQL in one platform to a different?

Please, just one gotcha per answer.

Oracle doesn't appear to possess a trouble with cursors, they're an enormous performance condition in SQL server.

Really virtually all performance tuning is database specific (and that's why ANSII standard code frequently works very poorly in comparison towards the better techniques designed in to the specifc flavor of SQL that's database specific).

Dates are another factor that appear to become handled very differntly from database to database.

Datatypes aren't equivalent either. One factor that has a tendency to get beginners to SQL Server is the fact that timestamp data type has practically nothing related to dates and occasions and can't be transformed into a datatime value.

Oracle doesn't allow a choose statement with no FROM clause. Therefore, you can't do queries such as this:

SELECT 1

Rather, there are here the totally in the DUAL table:

SELECT 1 FROM DUAL

Another example is producing unique (typically surrogate) primary secrets.

Many databases, for example SQL Server and sqlite allow a column to become declared being an identity: typically, when the value with this column is missing on place, the database will produce a unique value for that column.

Oracle, by comparison, has you produce a sequence separate towards the table after which use nextval around the sequence to create the following value:

CREATE SEQUENCE test_seq;
SELECT test_seq.nextval FROM dual;

Or, more typically:

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

What exactly are some common "gotchas" when it comes to porting SQL in one platform to a different?

Just like tries to translate from English to Russian by replacing the dictionary words directly into the phrases.

Works best for hello and goodbye, but fails for Mary were built with a little lamb to express nothing of Shakespeare.

Different RDBMS's have different cultures, despite getting SQL within their title.

For example, the row restricting.

In Oracle:

WHERE rownum = 1

In SQL Server:

SELECT TOP 1

In MySQL and PostgreSQL:

LIMIT 1

In DB2:

SELECT * ... FETCH FIRST 1 ROW ONLY

Four different clauses.

Multi-value IN clause queries. I did previously begin using these constantly on Oracle and was surprised to discover that you can't do that in SQL Server. For instance, this question:

SELECT * FROM mytable WHERE (col1, col2) IN ( SELECT col1, col2 FROM othertable )

Oracle won't permit you to place empty strings: they're quietly transformed into NULL.

Oracle includes a different method of quotes in opposition to MySQL.

MySQL: `object_name`, 'string', "string"
Oracle: "object_name", 'string'

In addition, getting away differs.

MySQL: 'It\'s easy'
Oracle: 'It''s slightly confusing'

(Observe that to flee anything apart from quotes in Oracle, you should use the ESCAPE directive inside your query Choose * FROM testTable WHERE percent = '50%' ESCAPE '')

Performance issues is really a large one. For example, sights in Oracle are, AFAIK, virtually as quickly as tables. It was not the situation with SQLServer after i needed to rely on them. The sights effectively wiped out the performance slowing down lower exactly the same chooses by a purchase of magnitude or even more (a question completely from the tables required, say .5s, while using the a view might take one minute). There have been also lots of restrictions on the use, for example not every SQL functions might be utilized on sights.

Observe that it was true 5-6 six years back, I'm not sure if Microsoft has enhanced this since.