Writing ANSI Standard SQL is not practical - this is the conclusion I came to after several years of writing portable applications, which had to work against both SQL Server and Oracle. Let me elaborate.
The best practice is to use stored procedures.
You do not want to issue SQL directly from your application. You want to invoke stored procedures - that will isolate your application from any changes in the datbase structure, any performance improvements etc.
Stored procedures are absolutely not portable.
You can see for yourself. I borrowed the following code snippet from asktom.oracle.com:
ops$tkyte@ORA8I.WORLD> create or replace
procedure p ( p_str in varchar2 )
2 is
3 l_str long := p_str || chr(10);
4 l_piece long;
5 n number;
6 begin
7 loop
8 exit when l_str is null;
9 n := instr( l_str, chr(10) );
10 l_piece := substr( l_str, 1, n-1 );
11 l_str := substr( l_str, n+1 );
12 loop
13 exit when l_piece is null;
14 dbms_output.put_line( substr( l_piece, 1,
250 ) );
15 l_piece := substr( l_piece, 251 );
16 end loop;
17 end loop;
18 end;
19 /
Is there any chance it could run on SQL Server? No way.
Even if you write ANSI Standard SQL, it works differently on different platforms
I provided several examples
here.
Stored procedures as an implementation of an interface
You can treat your database API as an implementation of an interface. Porting to another RDBMS is similar to an implementation of an interface one more time. If all your stored procedures are covered with unit tests, that will dramatically simplify another implementation - your unit tests will be both your documentation and your test harness.