RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again. : Page 2

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.


Don't Forget About Collations and Case Sensitivity

Many SQL Server shops use case-insensitive collations, so the following query against the table defined and populated in the previous section will return one row:

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

Many Oracle shops' string comparisons are case sensitive, so the value Scott stored in the party table will not match the condition WHERE FirstName = 'SCOTT'. Because many businesses do not need case sensitivity, you can uppercase all strings as you save them. If you go for this approach, you can also use a case-sensitive collation on SQL Server, which is somewhat more efficient because comparing case-sensitive strings requires less CPU.

Also, do not assume that sort order is the same on all servers. Suppose the Names table is populated and selected from the following:



Depending on the RDBMS and its configuration, this query may return either ABC or A_B.

Because you need only one connection to reproduce all the problems previously discussed, it is relatively easy to expose them using proper unit and regression testing. For regression testing, you need to compare data in Oracle and SQL Server tables. Many tools are available for accomplishing that. For instance, you could import all Oracle data into a SQL Server database, and use Red Gate Software's Data Compare tool. Alternatively, you could export all the data into XML files and compare the files.

Some differences in Oracle's and SQL Server 2000's behavior manifest themselves only when multiple connections modify and select data. Even then, they happen only intermittently, which makes them difficult to reproduce and understand. The following section provides a short example of this.

Intermittent Bugs

Suppose you need to implement a simple business rule stating that no more than one technical lead can report to a manager, and the table is as follows:

  ManagerId INT FOREIGN KEY REFERENCES Employee(EmployeeId ), 
  Role VARCHAR(20), 
  FullName VARCHAR(50))

Suppose you embed in your application an insert statement to insert one employee and implement that business rule as follows:

INSERT INTO Employee(ManagerId, Role, FullName)
  SELECT ManagerId, @Role, @FullName 
  FROM Employee
WHERE ManagerId = @ManagerId 
AND (@Role <> 'Technical Lead' OR NOT EXISTS(SELECT 1 FROM Employee WHERE ManagerId = @ManagerId 
AND @Role = 'Technical Lead' ))

This statement will insert anybody except for a technical lead as long as you provide a valid ManagerId. On SQL Server 2000 (or on SQL Server 2005, if you do not use snapshot isolation), this statement will prevent you from inserting a second technical lead reporting to the same manager. However, on Oracle (and on SQL Server 2005 using snapshot isolation), you can do the following:

  1. Add a new manager and commit.
  2. Begin a new transaction, add a technical lead, but do not commit yet.
  3. Open another connection and add another technical lead reporting to the same manager. Because this connection does not see uncommitted changes from the first one, the insert will succeed.
  4. Commit the transaction in the first connection, and note that two technical leads report to the same manager.

The biggest problem with bugs like this is that they are intermittent; you cannot reproduce them if you are the only user on the system. In many cases, this is exactly what is happening—the bug ends up with 'Cannot Reproduce' status. In order to reproduce such issues, you need to understand concurrency really well and use test scenarios like the previously demonstrated ones.

Portability Is Not as Easy as It Seems

As you have seen, portability is not an easy-to-accomplish feature. Achieving portability requires a good understanding of the differences between different back ends, a lot of work, and a lot of testing. If you really want your application to work on both Oracle and SQL Server, you need to be proactive. Take the following steps:
  1. Learn about the differences between these two RDBMSs.
  2. Review all your source code, and identify and fix potential problems.
  3. In your test plans, incorporate test cases exposing potential differences. For example, make sure that your application distinguishes between an unknown middle name and a known absence of a middle name.
  4. Do regression testing, making sure that all your unit tests work identically on both platforms.

Alexander Kuznetsov has over 10 years of experience in database design, development, troubleshooting, and administration. Currently, he works with DRW Trading Group and concentrates on database design, development, and performance improvements.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date