Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again.
The same ANSI Standard SQL query can run against identical data on SQL Server and Oracle and return different results. So just because your application issues ANSI standard SQL, don't assume it's portable.
by Alexander Kuznetsov
October 19, 2006
ut here in the field, there is a popular urban legend: "If your application issues only ANSI standard SQL, then your application is portable." Many developers actually believe it. Unfortunately in some cases, identical SQL works differently on different RDBMS. This article presents some examples when the same query runs against identical data on SQL Server and Oracle and returns different results. It also discusses what it takes to develop truly portable applications and whether the applications ought to be portable in the first place.
The following sections describe examples when identical SQL works differently on SQL Server and Oracle.
NULLs in Indexes
Consider the following table:
CREATE TABLE Names(Name VARCHAR(10), OtherData VARCHAR(100))
CREATE UNIQUE INDEX UQ_Names ON Names(Name)
INSERT INTO Names(Name) VALUES(NULL)
INSERT INTO Names(Name) VALUES(NULL)
It's quick, easy and you get access to all the articles on DevX.
This registration/login is to allow you to read articles on devx.com. Already a member?
To become a member of DevX.com create your Member Profile by completing the form below. Membership is free!
ut here in the field, there is a popular urban legend: "If your application issues only ANSI standard SQL, then your application is portable." Many developers actually believe it. Unfortunately in some cases, identical SQL works differently on different RDBMS. This article presents some examples when the same query runs against identical data on SQL Server and Oracle and returns different results. It also discusses what it takes to develop truly portable applications and whether the applications ought to be portable in the first place.
