THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Writing ANSI Standard SQL is not practical.

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

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


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.


Published Tuesday, February 24, 2009 5:30 PM by Alexander Kuznetsov



Armando Prato said:

I worked for a company that supported SQL Server, Oracle, and IBM's DB2.   They used generic SQL wherever they could due to portability.  It drove me crazy that I was forced to not use the best extensions SQL Server had to offer because the other platforms did not support it (TOP for instance).

February 25, 2009 9:14 AM

Constantin said:

Oh my god, talk about throwing out the baby with the bath water! Yes, sometimes stored procedures are a good way to improve performance or make use of extensions provided by some vendor, but to suggest stored procedures as best practice replacement to standard ANSI SQL is unsound.

Here in my humble opinion is more sound advice ....

The old saying, "Don't break the rule unless the rule is broken", must always be weighed against "If it ain't broke, don't break it."

Finding a proper balance is always better than "throwing out the baby  with the bath water"


January 13, 2012 5:58 PM

Alexander Kuznetsov said:


Can you elaborate what do you mean by "finding a proper balance"?

January 13, 2012 9:53 PM

Constantin said:

Portability across platforms and vendors is very important. For example, our local development is tested against h2 databases, we then promote to Oracle databases so sql scripts are written so that they can work for both systems as much as possible.

Obviously there WILL be differences so you have to manage these differences prudently. In general however we do not use sql extensions offered up by the respective vendors unless there is a compelling reason to do so.

Vendors usually break strict compliance to a given standard to force clients to use "their" product to the bitter end (since the code won't be compliant with any other system) . I've been around long enough to see this tactic used over and over.

Also, adhering to a standard over time means a company can hire from a bigger pool of developers in the future as opposed to a short list of folks who have the knowledge of a dying language that becomes increasingly obscure over time.

Microsoft's proprietary extensions to Java is a simple example.

January 17, 2012 7:30 PM

Alexander Kuznetsov said:


Your approach can only work short term or for small systems, as long as you do not need any performance tuning. With any reasonable amounts of data and or concurrency, you can either do tuning, which will be vendor specific, or you will have inefficient performance. Been there, done that.

January 22, 2012 10:46 PM

Willem said:

Stored procedures aren't the only means to provide abstraction.

A well layered application tier will allow you to use ANSI sql  by default but load the appropriate server specific sql as needed.

'isolating your application from any changes in the database structure' is not really the goal imho.

Code is code so if you have to change code it doesn;t matter whether it's in the persistence layer of the application or the db.

You just want to make sure they are cleanly absrtacted so a change in the db structure causes a ripple of changes that extend beyond the persistence layer

February 18, 2013 9:22 PM

Willem said:

shoud read

so a change in the db structure _DOESN'T_ cause

February 18, 2013 9:23 PM

AlexK said:


What you are saying makes sense to me. Yet in many cases it seems to be much cheaper to use stored procedures than have every single client application use one and the same persistence layer.

I have clients written in a dozen of languages, on Linux, Macs, and Windows. Some apps use Web interface, some use Excel.

Naturally your situation may be different.

February 18, 2013 10:09 PM

Orlin said:

Using stored procedure has many advantages. I'm involved in a huge project for a decade. We have many databases and we consider them as applications. Each has an API/Interface that consists of stored procedures and is used by different services written in different programming languages. None of these services uses plain SQL statements embedded in its code. This approach decouples both sides. It encapsulates the db schema and makes it transparent for the outside world. Whatever changes need to be done (redesign/refactoring/optimizations) in the database will not affect the application that uses it. Last but not least: why should for example C++, .Net or Java developer write SQL code when it can be done by a highly qualified DB developer? Isn't it easier for example to get one Java expert and one SQL Server expert than a person who is proficient in both?

August 23, 2016 5:53 PM
New Comments to this post are disabled

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on and Currently he works as an agile developer.

This Blog


Privacy Statement