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.

24 October 2005

 

NULLs versus empty strings

SQL Server and Oracle treat columns containing strings with no length in very different ways.

Consider the following Microsoft SQL Server script:

CREATE TABLE nulltest ( a1 char (10) , a2 varchar (10) ) ON PRIMARY
go
insert into nulltest (a1,a2) values ('','')

go
select count(*)from nulltest where a1 is null

go
select count(*)from nulltest where a2 is null

go

The results from the two selects is 0 in both cases. SQL Server does not treat the empty strings as nulls.

The Oracle equivalent is:

CREATE TABLE nulltest ( a1 char (10) , a2 varchar2 (10) ) tablespace c1ts;
insert into nulltest (a1,a2) values ('','');
select count(*)from nulltest where a1 is null;
select count(*)from nulltest where a2 is null;


The two selects both return 1. Oracle is treating an empty string as a NULL.

Be wary when dealing with these cases.

15 October 2005

 

Dates and times

The ability to store information about dates and times is fundamental to the vast majority of databases. It is a very common need to store information about when something occurred. Imagine a bank being able to operate without being able to record when a transaction occurred, when an account was opened, when the next automatic payment for the mortgage needs to be paid and so on.

Given this, it is surprising to me how sparse the support for date and time information is in Microsoft SQL Server and Oracle.

In Sybase there are four data types:
DATETIME, SMALLDATETIME, DATE and TIME.
Microsoft SQL Server shares the first two but does not implement the third and fourth.

The DATETIME and SMALLDATETIME are both used to store an instant in time (i.e. a date and time together, for example 15 October 2005 at 9:36AM). The differences between them are that the DATETIME can store an instant of time to more precision (nearest 1/300th of a second) and a greater range of dates (1 Jan 1753 to 31 Dec 9999) compared to SMALLDATETIME (nearest minute and 1 Jan 1900 to 6 Jun 2079).

The Sybase DATE data type stores a calendar date without any time element at all. TIME is used to store a time without a calendar date component. DB2 has also implemented these two data types, ever since its early days.

To me the DATE and TIME data types make a huge amount of sense. If you just want to store a date, why should you be forced to store it with a time aswell. OK, you can always set the time element of a DATETIME value to 00:00:00, but why should you have to remember to do it and suffer the consequences of forgetting to do it? If I want to have a column in a table which is a person's date of birth, or a column to hold the date on which a law comes into force, or any one of a 100 different examples then the DATE data type does it for me. DATETIME, although you can make do with it, just doesn't store the type of data that I want.

It is a similar story with TIME. This is ideal for storing data to do with timetables. The train leaves every day at 10:25AM. How can I store this in a column of a SQL Server table? I can not.
Oracle has similar deficiencies - it has the DATE and the TIMESTAMP data type, both of which store point-in-time data, with different degrees of precision. However Oracle has a marvellous system of storing INTERVALs between points in time. I will describe those another day ...

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]