I have been reading up on T-SQL (SQL Server) vs P/L-SQL (Oracle) and discovered a handy flag that can be used in SQL Server to ensure FIPS 127-2 (SQL-92/ANSI-92) compliance.
As far as I know, both SQL Server and Oracle only guarantee entry level FIPS 127-2 compliance. Therefore, one of the ways to ensure that your SQL syntax is portable is by keeping your syntax FIPS 127-2 entry-level compliant.
For example, setting the flagger to "Entry" reveals that the following syntax is SQL-92 compliant:
SET FIPS_FLAGGER 'ENTRY'
SELECT Status
FROM
TABLE1
UNION
SELECT Status
FROM
TABLE2
---
RESULTS
FIPS Warning: Line 1 has the non-ANSI statement 'SET'.
FIPS Warning: The length of identifier 'CONTAINER_STATUS_VIEW' exceeds 18.
(7 row(s) affected)
Note that the FIPS compliance parser also looks at field name size constraints, so you will get warnings similar to above as well. I believe most of these can safely be ignored as there is a fair overlap between vendors, but the main focus should be the syntax.
If anyone has any other tips for writing vendor neutral SQL, I would be very interested.