Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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.


advertisement
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)



Oracle just doesn't store NULL entries in the index, so both inserts succeed. SQL Server includes NULL entries in the index, so the second INSERT will fail.

This difference also may cause significant performance differences. For example, should you drop the unique index and recreate it as non-unique as follows:

DROP INDEX Names.UQ_Names go CREATE INDEX IX_Names ON Names(Name)

This index may speed up a query only on SQL Server and never on Oracle:

SELECT Name, OtherData FROM Names WHERE Name IS NULL

To emulate Oracle's indexes on SQL Server, you can use a "nullbuster" approach (this name was coined by Steve Cass), which I described in "Indexes on Computed Columns: Speed Up Queries, Add Business Rules". To emulate SQL Server's indexes on Oracle, you can use a function-based index on (Name, CASE WHEN Name IS NULL THEN 1 ELSE NULL END).

Zero Length Strings

Consider the following table:

CREATE TABLE Party(FirstName VARCHAR(30), MiddleName VARCHAR(30), LastName VARCHAR(30))

and some sample data:

INSERT INTO Party VALUES('Alex','','Kuznetsov') INSERT INTO Party VALUES('Scott',NULL,'Larson')

I am Russian. As such, I do not have a middle name. The table provides a zero length string to store that fact in the database. On the other hand, I do not know if my buddy Scott Larson has a middle name, nor what it is if he does. Therefore, the table provides a null to store that quite different fact in the database.

On SQL Server, these two facts are stored differently. So I can easily retrieve parties without middle names as follows:

SELECT FirstName, MiddleName, LastName FROM Party WHERE MiddleName = ''

Also on SQL Server, I can separately select all the parties whose middle names I do not know as follows:

SELECT FirstName, MiddleName, LastName FROM Party WHERE MiddleName IS NULL

Unfortunately, Oracle does not distinguish between a known zero length string and a null value; it converts a zero length string into a null. Because of that, the former query will return nothing, and the latter query will return both rows.

As you can see, identical queries against identically populated tables return different results on different RDBMSs, but that is not all. Suppose you have an empty table Names (as described above) as well as the unique index UQ_Names. On Oracle, the following inserts succeed no matter how many times you issue them:

INSERT INTO Names(Name) VALUES('') INSERT INTO Names(Name) VALUES(NULL)

On SQL Server, the batch succeeds only for the first time.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap