THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 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.

 

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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"

Constantin

January 13, 2012 5:58 PM
 

Alexander Kuznetsov said:

Constantin,

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:

Constantin,

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:

Willem,

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

Leave a Comment

(required) 
(required) 
Submit

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 sqlblog.com, 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 simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement