My Adopted SQL Best Practices

by Bouton Jones

Introduction

DISCLAIMER:  I don't claim authorship for these best practices and guidelines.  They originate elsewhere --- from other developers, college courses, books, web sites, and professional experience.  But I've adopted them in practice and in that sense they're "mine."

To developers who already follow them, they might seem like common sense. But from my experience maintaining legacy code, I've observed that they are not common practice. Many ColdFusion developers could benefit from an awareness of these guidelines.

Render Unto SQL That Which is SQL's

My primary best practice for SQL Server is simply this: Render unto SQL that which is SQL's and unto ColdFusion that which is ColdFusions's. Put in less biblical language: Don't use ColdFusion to do what SQL can do better or by expending fewer resources.

ColdFusion is a powerful language. It can do many of the same things that SQL can do. But where ColdFusion and SQL Server server share the same functionality, usually SQL can do it faster and more efficiently.

For example: Converting an empty string (a NULL) to a zero.

ColdFusion converts null values from SQL into empty strings. Normally we need a zero instead of an empty string. It's possible to use ColdFusion to convert your empty strings using NumberFormat or the VAL function.

If the parameter value is an empty string, NumberFormat returns 0.

<CFSET number = NumberFormat(getNumber.number)>

The VAL() function converts numeric characters that occur at the beginning of a string to a number. If conversion fails, it returns zero.

<CFSET number = VAL(getNumber.number)>

But a more elegant solution would be to use SQL's IsNull function --- or Oracle's NVL function --- inside the SQL query.

SELECT ISNULL(Number, 0) AS Number
FROM tblNumbers
WHERE ID = <cfqueryparam null="true" value="#Session.NumberID#" cfsqltype="cf_sql_integer">

On one project where I was working in an Oracle shop, the stakeholders wanted an interface for adding objectives for an annual todo list. They wanted an option for prioritizing the objectives as they added them. But they also wanted the priorities of the objectives to default to the highest available number if the priority field was left blank. So for the first object they added the default priority value would be 1. For the next object the default priority would be 2. For a novice developer the obvious solution was to use two queries. First run a SELECT query to identify the first available priority value for that year and then include that value in an INSERT query. But by combining a sub query, a CFIF condition, and Oracle's NVL function, it was only necessary to run a single query.

<!--- This query adds a new objective to the database. If the user does not select the display order for the objective, the highest available display order is selected automatically. --->

<cfparam name="FORM.YEAR" type="range" min="1999" max="2100">

<cfquery name="insObjective" datasource="#request.db#" username="#cookie.cfusername#" password="#cookie.cfpassword#">
    INSERT INTO tblObjectives
        (
                Obj_ObjID_Num,
                Obj_Year_Num,
                Obj_Description_Txt,
                Obj_DisplayOrder_Num,
                Obj_DeleteFlag_Bin,
                Obj_UserID_Txt,
                Obj_TimeStamp_Dt
         )
    VALUES
        (
                #request.userspace#.Obj_ObjID_num.NEXTVAL,
                <cfqueryparam value="#trim(FORM.YEAR)#" cfsqltype="CF_SQL_NUMERIC">,
                <cfqueryparam value="#trim(ucase(FORM.Obj_Description_Txt))#" cfsqltype="cf_sql_char">,
                <cfif FORM.Obj_DisplayOrder_Num EQ "">
                        (SELECT NVL(Max(Obj_DisplayOrder_Num), 0) + 1
                         FROM tblObjectives
                         WHERE YEAR = <cfqueryparam value="#trim(FORM.YEAR)#" cfsqltype="CF_SQL_NUMERIC">
                                AND Obj_DeleteFlag_Bin = 'N'),
                <cfelse>
                        <cfqueryparam value="#trim(FORM.Obj_DisplayOrder_Num)#" cfsqltype="CF_SQL_NUMERIC">,
                </cfif>
                'N',
                <cfqueryparam value="#trim(UCASE(cookie.cfusername))#" cfsqltype="CF_SQL_VARCHAR">,
                SYSDATE
        )
</cfquery>

There are many other examples of ColdFusion functions duplicating the work performed by SQL functions. Often they have the same name. Here are a few.

Purpose ColdFusion Functions SQL Server Functions
Get Current Date #Now()# GETDATE()
Increment Date #DateAdd('M', 3, Now())# DATEADD(month, 3, GetDate())
Determine Date Range #DateDiff(datepart, startdate, enddate)# DATEDIFF(datepart, startdate, enddate)
Validate Date #IsDate(expression)# ISDATE(expression)

In addition, using Aggregate functions in SQL will eliminate performing the math in ColdFusion.

Lastly using SQL aliases for column names that match the ColdFusion variable names will eliminate the necessity of reassigning the values in ColdFusion.

In short, let SQL do the heavy lifting instead of ColdFusion.

Minimize Result Sets

Use SQL instead of ColdFusion as much as possible but don't use more SQL than you need.

SELECT DISTINCT Description
FROM Expenses
ORDER BY Description

Put JOIN Conditions in the ON Clause

Always use explicit joins and put join conditions in the ON clause instead of the WHERE clause. The performance is optimal because the join conditions are performed before the search conditions. While it's true that SQl Server will optimize the query automatically, it's easier to read and maintain in the optimized form. In addition, the code will be easier to read and therefore easier to maintain.

Acceptable: JOIN Condition in the WHERE clause

SELECT BookID, BookTitle, AuthorName
FROM tblBooks AS B JOIN tblAuthors AS A
WHERE B.AuthID = A.AuthID
     AND Genre = 'Horror'

Better: JOIN Condition in the ON clause

SELECT BookID, BookTitle, AuthorName
FROM tblBooks AS B JOIN tblAuthors AS A ON B.AuthID = A.AuthID
WHERE Genre = 'Horror'

 

Use Junction Tables

Junction tables define relationships, or connections, between other tables. A junction table is a SQL table containing two or more columns --- each of which contain foreign keys to other tables that have many-to-many relationships between them. The purpose of junction tables are to convert the single many-to-many relationship between the other tables into easier to manage one-to-many relationships. Junction tables are also called cross-reference tables, bridge tables, join tables, map tables, intersection tables, linking tables, many-to-many resolvers, link tables, association tables, and connector tables.

Using junction tables is a common database design practice for optimizing development. But if a developer hasn't explicitly learned the practice, he might waste time and effort before learning it on his own.

ER Diagram Showing the Use of Linking Tables

To make identifying junction tables easier during development, follow a consistent junction naming convention such as xref_[First Foreign Key]_[Second Foreign Key] or xref_[First Table Name]_[Second table Name] (E.G. xref_OppID_QuotNum and xref_Teacher_Student.)

Follow Consistent Naming Conventions

Following consistent naming conventions save much time and effort in developing both ColdFusion and SQL Server. By keeping the naming conventions consistent, the relationships between files names, cfquery names, and stored procedure names become intuitive. I don't need to refer back to my ER diagrams or other documentation nearly as often. As an illustration:

Type Example Names
Table tblBooks
tblAuthors
tblStudents
tblInstructors
Views vwName
vwClasses
Includes includes/qryCheckNSN.cfm
/sql/select/s_CheckNSN.cfm
../sql/select/selNSN.inc
CFQUERY names qryCheckNSN
s_CheckNSN selNSN
Stored Procedures spQryCheckNSN
sp_s_CheckNSN
spSelNSN

In the "Includes" examples above, the CodeFusion template includes another file called qryCheckNSN.inc located in the includes subdirectory:

includes/qryCheckNSN.inc

The content of qryCheckNSN.inc might be as follows:

<cfstoredproc procedure="spQryCheckNSN" dataSource="#DataSource#" result="qryCheckNSN">
        <cfprocparam type="IN" CFSQLType="cf_sql_varchar" value="#FORM.ID#" dbVarName="@NSNID">
</cfstoredproc>

It's calling a stored procedure in SQL named spQryCheckNSN.

Because of the naming convention it's easy to figure out the name of a CFQUERY and a SQL stored procedure based solely on the name of the include file. That makes maintenance easier.

Furthermore, stick with the house naming conventions --- even if you don't like them. It helps the team as a whole. In shops that don't have a naming convention in place, the resulting database can have multiple, conflicting database naming conventions that make understanding the relationships between tables difficult and counter intuitive. It is better a less effective name convention than a mish mash of conflicting conventions.

Use the Army Column Naming Convention

When I first saw --- what I later identified as --- the "Army Naming Convention," it confused and frustrated me. But after reading an explanation, I realized that it was extremely useful and informative when fully utilized.

The syntax is to name the columns in a table with three distinct parts --- where each part communicates a different aspect of the column. The parts are:

Underscores are placed between the table abbreviation & the purpose and between the purpose & data type.

Take for example a column named Orgs_OrgID_tx. The first part of the name --- table abbreviation --- is orgs and that indicates that the field is part of a table named Organizations, Orgs, tblOrgs, or some similar variation. The second part of the name --- content --- is OrgID and that indicates that the column contains the identification of an organization. The third part of the name --- tx in this example --- indicates the data type such as tx for text, nm for numeric, bi for binary, or dt for datetime.

Illustrating the Differences Between Naming Conventions
Common Naming ConventionsArmy Naming Convention
dbo.User.UserIDdbo.tblUserID.UserID_UserID_tx
dbo.Address.UserIDdbo.tblAddress.Address_UserID_tx
dbo.Notes.UserIDdbo.tblNotes.Notes_UserID_tx
dbo.Notes.Notedatedbo.tblNotes.Notes_Notedate_dt
dbo.User_to_Proj.UserIDdbo.tblUserID_to_ProjID.UtoP_UserID_tx
dbo.User_to_Proj.ProjIDdbo.tblUserID_to_ProjID.UtoP_ProjID_tx

There are several advantages to this:

CAUTION: If you decide to use the same names for the datafields in HTML forms, you should rename the fields shortly before making the application live. Otherwise your HTML source will reveal more about your database to potential crackers then you'd want.

Get the Identity Column Value From a New Record

There are several ways on getting the value of an identity column when adding a new record. But it's best to do it in a single transaction and a single call to the database.

SET NOCOUNT ON
BEGIN TRANSACTION
INSERT INTO tblStuff(
        ID,
        Name)
        VALUES (
        #Session.ID#,
        #HTMLEditFormat(Session.District_Name)#
)
SELECT @@IDENTITY AS NewID
COMMIT TRANSACTION
SET NOCOUNT OFF

(Alternatively you can use the result attribute in CF 8 but getting the value through SQL eliminates extra coding if you ever need to port your apps to another language.)

Group Result Output with Nested CFOUTPUT Tags

Previous to ColdFusion 7 it was incorrect to nest CFOUTPUT tags. But with ColdFusion 7 and later it became possible --- and advisable --- to do so. Nesting tags reduced the number of queries necessary to collect information. Instead of having separate queries for different countries, the developer can get a result set for all the counties and then separate the information by country by using nested CFOUTPUT tags.

<cfparam name="URL.startRow" default="1">
<cfparam name="URL.maxRows" default="3">

<cfquery name="qrySelStuff" datasource="#DataSource#">
    SELECT Stuff_Category_txt,
            Stuff_SID_nm,
            Stuff_SNum_nm,
            Stuff_Type_txt,
            Stuff_Descript_txt
      FROM tblStuff
      WHERE SectionNumber = 1
         AND FiscalYear = '2011'
      ORDER BY Stuff_Category_txt,
            Stuff_SID_nm,
            Stuff_SNum_nm,
            Stuff_Type_txt,
            Stuff_Descript_txt
</cfquery>

 

<table border="1" style="border-collapse: collapse">

    <cfoutput query="qrySelStuff" group="Stuff_Category_txt">

        <tr>
             <th colspan="3" class="special">#Stuff_Category_txt#</th>
        </tr>

        <cfoutput>
            <tr>
                <td>#Stuff_SNum_nm#</td>
                <td>#Stuff_Descript_txt#</td>
                <td>#Stuff_Type_txt#</td>
            </tr>
        </cfoutput>
  
    </cfoutput>

</table>

 
Imported Stuff
3Stuff from Italyfood
6Stuff from Franceart
7Stuff from Germanytoys
Domestic Stuff
1Stuff from NJgarden supplies
2Stuff from TXTex-Mex
4Stuff from LAneat
5Stuff from CAsunny

Indent, Comment, and Document

Writing legible, properly commented, and clearly commented code will benefit not only the developers who will work after you, but it will benefit you in the short term. Used judiciously, the extra time spent in making your code more transparent will save maintenance time in the long term.

Caveat: In writing code, a developer has to balance best practices with economy.  For example, it might not justifiable to spend time indenting and commenting code for an small application that is due to be discontinued soon.  On the other hand making legible, comprehensible code is important for a heavily used application that developers will be maintaining for years to come.

Summary

In Closing

I hope these suggestions help developers as much as they've helped me.

SQL is an essential skill for ColdFusion developers to have.

See Also

Some Tips and Techniques for Debugging SQL in ColdFusion