Tips & Tricks

Have you ever written an application using one database only to have it break when you switched to a different database? How often have you decided to limit the functionality of your database by writing only basic SQL because you just weren't sure if your queries would work on a different database?

Or maybe you've written ColdFusion code to manipulate query results without realizing that the database could do the same manipulation quicker and more efficiently. In general, the database tends to be seriously underutilized in most applications. This article aims to provide a quick overview of some important SQL differences between the two most popular commercial databases - Oracle9i and SQL Server 2000 - to help the CF developer write more portable and database-smart applications.

Sequences and Identity Columns
One of the major differences between Oracle and SQL Server is in the treatment of sequential primary keys. To obtain an automatically generated sequential primary key for a table in SQL Server, all you have to do is make the column an IDENTITY column. Oracle, on the other hand, requires that you first create a sequence and insert its value explicitly.

SQL Server:
CREATE TABLE products (
ID IDENTITY,
Desc char(50)
);
INSERT INTO products(desc) VALUES('Acme widget');

Oracle:
CREATE SEQUENCE products_seq
START WITH 1000
INCREMENT BY 1;
CREATE TABLE products (
ID NUMBER,
Desc char(50)
);
INSERT INTO products(ID,desc) VALUES(products_seq.NEXTVAL,'Acme widget');

Another difference is that SQL Server allows you to conveniently retrieve the auto-generated ID from the insert via the following code:

SQL Server:
<cfquery name="insertProducts">
SET NOCOUNT ON;
INSERT INTO products(desc) VALUES('Acme widget');
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
SET NOCOUNT OFF
</cfquery>

Warning: there are variants of the above code that retrieve the ID using @@IDENTITY rather than SCOPE_IDENTITY. @@IDENTITY is not as good as SCOPE_IDENTITY because it will give an incorrect result if you have a trigger on the table that inserts into another table.

In Oracle, because you're managing the sequence explicitly, you would retrieve the sequence value before you do the insertion rather than retrieving the value after insertion.

Oracle:
<cfquery name="getSeqValue">
SELECT products_seq.NEXTVAL AS ID from DUAL
</cfquery>
<cfset ID=getSeqValue.ID[1]>
<cfquery>
INSERT INTO products (ID,desc) VALUES(#ID#,'Acme widget')
</cfquery>

However, these advantages of the SQL Server approach to sequential primary keys come with a price - you cannot insert a value into an IDENTITY column. Thus, if you try to bulk import rows from another table into a table with an IDENTITY column, you will have to omit the original column corresponding to the IDENTITY column. This becomes especially painful to manage properly if the omitted column is the foreign key value by which other tables reference your original table. For this reason, despite its ease of use, it is often a good idea to avoid the use of the IDENTITY column in SQL Server.

Derived Tables and Top N Queries
A common question that keeps coming up in various CF forums is: How do I retrieve only the first N results of a query? While this is a task that's possible to do in a database-independent way by using CF to control the looping over the query, in general that would not be a good idea. That's because in order to do this, CF has to retrieve all the rows of the query and then filter out the rows that it does not want. As a result, a large amount of memory and network bandwidth is consumed to process unwanted rows. It is a much better idea to get the database to do the work of selecting and returning only those results that you want.

To illustrate our next examples, we'll be using the small Employees table shown in Table 1. The SQL to create the table is located at the end of the article.

Using the sample employees table as an example, I would run the following queries in SQL Server to retrieve the names and salaries of the three highest-paid employees:

SQL Server:
SELECT TOP 3 name, salary FROM Employees ORDER BY salary DESC

In Oracle, it's slightly more complicated. We have to make use of a derived table to sort the results first (a derived table is a select query that is used in place of a table). Then we use the ROWNUM pseudocolumn to select the first 3 results (a psuedocolumn is a column that doesn't actually exist in the table that nonetheless has a value that can be used in a query).

Oracle:
SELECT name, salary
FROM (
SELECT name, salary
FROM Employees
ORDER BY salary desc
) WHERE ROWNUM <=3;

Note that since I want the three highest salaries, I have to order my results in descending order of salary. The ROWNUM condition on the outer query restricts the results to the first three.
Both queries return:

Name salary
George 100000
Ling 90000
Sandeep 60000

For the sake of consistency, the ORDER BY clauses in top N queries are not optional. Without an ORDER BY clause, the database is free to return results in any order it chooses, so the top N may be very different from one minute to the next depending on the internal state of the database, even if the data itself does not change.

Derived Tables and Results by Pages
A common variation on the previous question is: How do I retrieve only the first N through M results of a query? For instance, this kind of question might come up when paging through a large query 100 results at a time. In both SQL Server and Oracle, this can be accomplished by using a derived table.

In SQL Server, to retrieve the first N through M results, we must select the top M results in a derived table, reverse the order, and then select the top M-N+1 results of the reversed derived table. As an example, the following query selects the third through seventh highest paid employees.

SQL Server:
SELECT TOP 5 name, salary
FROM (
SELECT TOP 7 name, salary
FROM Employees
ORDER BY salary desc
)
ORDER BY salary asc

Now, how would we get the same results with Oracle? You might think that by modifying the previous Oracle query that the following would work:

SELECT name, salary FROM (
SELECT name, salary FROM Employees
ORDER BY salary, desc
) WHERE ROWNUM >=3 and ROWNUM <=7

Unfortunately, there are some oddities with the use of Oracle's ROWNUM pseudocolumn that cause the above query to return zero results. The query that actually works requires the use of a derived table within a derived table:

Oracle:
SELECT name, salary
FROM (
SELECT ROWNUM as rnum, name, salary
FROM (
SELECT name, salary
FROM Employees
ORDER BY salary DESC
)
)
WHERE rnum <= 7 and rnum >= 3;

Note: it is very important that the ROWNUM in the derived table is given an alias, otherwise the query will return zero results.

Both queries return:

NAME SALARY
Sandeep 60000
Akane 55000
Bjorn 35000
Jean 35000
Manuel 30000

NULL
NULL behavior is an extremely tricky issue. To the average CF developer, NULLs are often dealt with unobtrusively - a column with a value of NULL returns an empty string. For example, the following CF code results in "Shaya likes []".

<cfquery name="myQuery">
SELECT hobbies FROM Employees WHERE name='Shaya'
</cfquery>
<cfoutput>Shaya likes [#myQuery.hobbies#]</cfoutput>

Things get trickier when there are numeric expressions involving NULL. ANSI standards dictate that NULL should propagate during a calculation. In layman's terms, this means that any expression involving a NULL should evaluate to a NULL, thus 8+NULL and cos(NULL) should both evaluate to a NULL, which to CF looks like an empty string. Both SQL Server and Oracle are compliant in this respect.

However, with string expressions involving NULL, we see noncompliant behavior from Oracle. According to ANSI standards, concatenating a string to a NULL should also produce a NULL. In Oracle, a NULL will automatically be converted to a zero-length string for concatenation. For SQL Server, the default ANSI-compliant behavior can be overridden by disabling the concat null yields null setting of sp_dboption.

Oracle:
SELECT 'Shaya likes [' || hobbies || ']' FROM Employees WHERE name='Shaya'
returns "Shaya likes []"- not ANSI-compliant

SQL Server:
SET CONCAT_NULL_YIELDS_NULL ON
SELECT 'Shaya likes [' + hobbies + ']' FROM Employees WHERE name='Shaya'
returns NULL - ANSI-compliant
But
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT 'Shaya likes [' + hobbies + ']' FROM Employees WHERE name='Shaya'
returns "Shaya likes []"- not ANSI-compliant

Furthermore, Oracle states that this noncompliant behavior may change in future versions. Nonetheless, in my opinion, this noncompliant behavior is more user-friendly. Fortunately, though, there is a uniform way to be compliant as well as user-friendly in both databases using the COALESCE() function. The COALESCE() function takes a string of arguments and returns the first non-NULL argument. Thus, COALESCE(column_name,'') returns the column value if it is not NULL, and returns the second argument, the empty string, if the column is NULL. Rewriting the above queries as

Oracle:
SELECT 'Shaya likes [' || COALESCE(hobbies,'') || ']' FROM Employees WHERE
name='Shaya'

SQL Server:
SELECT 'Shaya likes [' + COALESCE(hobbies,'') + ']' FROM Employees WHERE ID=0

returns the same result "Shaya likes []" in Oracle and SQL Server regardless of future versions or of the setting of CONCAT_NULL_YIELDS_NULL.

However, there's one more wrinkle to Oracle's noncompliance. In Oracle's own words, "Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls." Consequently, when a row is inserted or updated with an empty string, what actually gets stored in Oracle is a NULL. By ANSI standards, however, a NULL and an empty string should be distinct objects. In our Employees table example this means that the statement

INSERT INTO Employees(ID,Name,Salary,reportsTo,hobbies)
VALUES(8,'Jean' ,35000 ,6,'');

actually inserts a NULL into the hobbies column for Jean rather than an empty string. As a result, the following two selects

SELECT count(*) FROM Employees WHERE hobbies IS NULL;
SELECT count(*) FROM Employees WHERE hobbies = '';

will return 1 and 1 in SQL Server but 2 and 0 in Oracle, respectively. Regrettably, this is one of those issues for which there is no satisfactory workaround, and it's just something to be aware of when developing for different database platforms.

CASE and Relabeling Results
CASE is one of those SQL features that doesn't get used as much as it should. Here are just two of its uses. The following simple example shows how CASE can be used to label the results of a query.

SELECT name, salary,
CASE
WHEN salary >= 60000 THEN 'Manager'
ELSE 'Grunt'
END
AS jobTitle
FROM Employees
WHERE name IS NOT NULL

returns

NAME SALARY JOBTITLE
George 100000 Manager
Ling 90000 Manager
Sandeep 60000 Manager
Manuel 30000 Grunt
Shaya 30000 Grunt
Akane 55000 Grunt
Bjorn 35000 Grunt
Jean 35000 Grunt

CASE and User-Defined Ordering
Another question that comes up in the ColdFusion mail list is: How do I return the results in an order that I define, which is neither alphabetical nor numeric? We'll use our Employees example to illustrate how this can be done. Suppose I want a list of all the employees ordered alphabetically, but I always want George to appear first. How do I accomplish that? I can do this by creating an ORDER BY attribute using CASE, which always places George first, as in the following query:

SELECT name FROM Employees
ORDER BY
CASE
WHEN name='George' THEN 1
ELSE 2
END, name;

which returns

NAME
George
Akane
Bjorn
Jean
Ling
Manuel
Sandeep
Shaya

Calculating Department Subtotals in a Single Query
Now, let's suppose we're given the following problem: retrieve a list of employees and their salaries sorted by department along with department salary subtotals in a single SQL query. While this is easy enough to do with a combination of SQL and CF, let's try to find a SQL-only solution.

It's fairly easy to retrieve a list of department subtotals. The query

SELECT dept, SUM(salary) FROM Employees GROUP BY dept

retrieves the subtotals

Finance 220000
IT 215000

However, we need to combine these results with the Employees table. This can be done with the UNION keyword, which simply combines the results of two queries. However, the use of UNION requires that the two queries match up exactly in column type and column order. Since we are retrieving name, salary, and department from Employees, we need to add an extra column to our subtotals query.

SELECT name, dept, salary FROM Employees
UNION
SELECT 'Total' as name, dept, SUM(salary) FROM Employees GROUP BY dept

The result of this query

NAME DEPT SALARY
Akane IT 55000
Bjorn IT 35000
George Finance 100000
Jean IT 35000
Ling IT 90000
Manuel Finance 30000
Sandeep Finance 60000
Shaya Finance 30000
Total Finance 220000
Total IT 215000

is almost what we want, but the order of the rows is wrong. We would like the employees to be ordered by department, and furthermore, have the department subtotals follow all the employees in the department. An ORDER BY department will take care of the first order condition, but how do we make the department subtotals follow the employees? We can take inspiration from how we used CASE for user-defined ordering by defining a new order_helper column to implement our ordering. The final query follows:

SELECT name, dept, salary, '1' AS order_helper
FROM Employees
UNION
SELECT ' Total' as name, dept, SUM(salary), '2' as order_helper
FROM Employees
GROUP BY dept
ORDER BY dept,order_helper;

returning in the desired order

NAME DEPT SALARY ORDER_HELPER
George Finance 100000 1
Manuel Finance 30000 1
Sandeep Finance 60000 1
Shaya Finance 30000 1
Total Finance 220000 2
Akane IT 55000 1
Bjorn IT 35000 1
Jean IT 35000 1
Ling IT 90000 1
Total IT 215000 2

Use Stored Procedures
This final tip isn't about SQL difference, but rather a tip about SQL development in general. I strongly recommend as a best practice that, as much as possible, all SQL be removed from ColdFusion code and placed in stored procedures. There are two very good reasons for this, neither of which involve performance:

• Security: Stored procedures can be restricted in privileges. This means that a stored procedure with only select privileges can never be used to delete or insert rows in your table via an SQL injection attack.
• Centralized organization: Having all the SQL in a central location means that the SQL can be easily modified and tuned. For example, during the prototype phase before the database design has started, all the stored procedures can contain query stubs. Then, during development, the query stubs are replaced with actual working queries. Finally, during the tuning phase just before deployment, the working queries can be replaced by optimized or database-independent queries. By placing SQL in stored procedures, all this SQL work can proceed independently of the ColdFusion development.

Conclusion
While Oracle and SQL Server have greatly improved their compliance with ANSI standards, true universal SQL portability remains elusive. This has put off many developers from using more advanced SQL due to compatibility concerns. Nonetheless, SQL is designed to manipulate query results and does so in a far more elegant and efficient manner than ColdFusion. Thus, it's best to retrieve the exact desired results from the database via correct SQL than to manipulate the results of a simple query via ColdFusion. Hopefully, this article has demonstrated some of the ways in which more portable SQL can increase the utilization of the database in CF applications.

Employees Table creation script:

CREATE TABLE Employees (
ID INTEGER,
Name CHAR(20),
Salary NUMBER,
Dept CHAR(15),
hobbies CHAR(50)
);
INSERT INTO Employees(ID,Name,Salary,dept,hobbies)
VALUES(1,'George',100000, 'Finance','golf');
INSERT INTO Employees(ID,Name,Salary, dept,hobbies)
VALUES(2,'Ling ',90000, 'IT' ,'golf');
INSERT INTO Employees(ID,Name,Salary, dept,hobbies)
VALUES(3,'Sandeep' ,60000, 'Finance' ,'tennis, volleyball, ballooning');
INSERT INTO Employees(ID,Name,Salary, dept,hobbies)
VALUES(4,'Manuel ',30000 , 'Finance' ,'stamps');
INSERT INTO Employees(ID,Name,Salary, dept,hobbies)
VALUES(5,'Shaya',30000 , 'Finance',NULL);
INSERT INTO Employees(ID,Name,Salary, dept,hobbies)
VALUES(6,'Akane' ,55000 , 'IT','comic books');
INSERT INTO Employees(ID,Name,Salary, dept,hobbies)
VALUES(7, 'Bjorn',35000 ,'IT','cricket');
INSERT INTO Employees(ID,Name,Salary, dept,hobbies)
VALUES(8,'Jean' ,35000 , 'IT','');

Resources

T-SQL reference at msdn online: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp

Oracle Technology Network online documentation (free registration required): http://otn.oracle.com/pls/db92/db92.homepage

Oracle SQL Reference online (free registration required): http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm

Add Your Feedback

In order to post a comment you need to be registered and logged in.

Register | Log in

Please wait while we process your request...