SQL & how different databases implement it

Some vaguely structured thoughts on the differences that you see between SQL as implemented by one DBMS and that of another.

26 May 2006

 

Treatment of NULLs by Oracle & SQL Server

Oracle & SQL Server behave subtly differently when processing columns containing nulls. Consider the following series of SQL statements:

CREATE TABLE tempone (
creditlimit NUMBER(6)); -- Oracle

CREATE TABLE tempone (
creditlimit INT); -- SQL Server

INSERT INTO tempone VALUES (null);
INSERT INTO tempone VALUES (2000);
INSERT INTO tempone VALUES (1000);

SELECT COUNT(*), SUM(creditlimit),
AVG(creditlimit), MIN(creditlimit),
MAX(creditlimit)
FROM tempone;

Reassuringly the result of executing the last of the above statements is the same for both Oracle and SQL Server. It returns values of 3, 3000, 1500, 1000 and 2000 respectively. Note that the AVG function computes the arithmetic mean, which excludes from its considerations the missing (null) value, hence the result of the AVG function is 1500, not 1000.

However, the following statement produces different results:

SELECT creditlimit
FROM tempone
ORDER BY creditlimit;

In SQL Server, the order of the results is:
NULL
1000
2000

In Oracle, the order of the results is:
1000
2000
NULL

If you are developing software that will be installable on a range of DBMSs or migrating from one DBMS to another you should be careful when your code includes ORDER BYs on nullable columns.

03 May 2006

 

Oracle Express Edition is available

I have been spending some time with the new, free Oracle Express Edition

This is essentially the same product as Oracle's standard offerings, but it has inbuilt limitations on the amount of user data that it will manage (4GB), number of processors in the server it will utilise (one) and amount of server memory it will use (1GB).

These are relatively minor drawbacks, and I am in the process of building a production web-based application that will use XE as the database back-end. So far and so good - all of the normal SQL, PL/SQL and database interfaces are available, working and performing. I have it installed on my ThinkPad T42 (under Windows XP) and it sits very quietly in the background and doesn't seem to affect anything else that I run.

The installation was very quick and easy. I have coupled it with Oracle SQL Developer which offers a GUI interface onto the database, allowing you to explore the database objects, insert data directly, run SQL and PL/SQL scripts, export and import data in a variety of ways and so on. Again I am using this on my ThinkPad and have been quite impressed with the interface and its performance. The user interface is not quite in the league of the best that Microsoft can produce, but it is by no means bad.

I look forward to updating you when I find the first problems, or when the application I am building goes live in September.

Links

Archives

September 2005   October 2005   December 2005   January 2006   March 2006   May 2006   June 2006   September 2006   May 2007   June 2007   November 2007   December 2007   March 2008   April 2008   December 2008   January 2011  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]