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.
- When writing queries for an application --- queries that will be run repeatedly --- keep the result set as small as possible. Don't include column names that you don't need. For example: If a table contains 29 columns then using "SELECT *" will return a result set with 29 columns even if the application only needs one column such as "ID." It would be a better use of database resource to specify only the column names that are required. That would be accomplished by using "SELECT ID" instead.
- Reduce the number of calls to SQL --- by using subqueries, nested queries, UNIONs, JOINs, etc., --- where possible
- Use DISTINCT in SELECT statements when you don't want to retrieve duplicate rows from your tables
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.
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:
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:
- table abbreviation
- column content
- data type
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.
| Common Naming Conventions | Army Naming Convention |
|---|---|
| dbo.User.UserID | dbo.tblUserID.UserID_UserID_tx |
| dbo.Address.UserID | dbo.tblAddress.Address_UserID_tx |
| dbo.Notes.UserID | dbo.tblNotes.Notes_UserID_tx |
| dbo.Notes.Notedate | dbo.tblNotes.Notes_Notedate_dt |
| dbo.User_to_Proj.UserID | dbo.tblUserID_to_ProjID.UtoP_UserID_tx |
| dbo.User_to_Proj.ProjID | dbo.tblUserID_to_ProjID.UtoP_ProjID_tx |
There are several advantages to this:
- The name of the table is obvious even while working with the fieldnames alone.
- There is no need to use aliases with keys because no keys have exactly the same name. (E.G. Normally two columns --- in different tables --- containing the ID for an organization might share the name OrgID. But with the Army Naming Convention the names are different --- Orgs_OrgID_tx and Members_OrgID_tx.
- The primary key / foreign key relationships are instantly recognizable. (E.G. Orgs_OrgID_tx would be a primary key and Members_OrgID_tx would be a foreign key.)
- It is easier to remember the datatypes when writing queries --- without referring back to documentation or the database client software. Column names that end in tx or dt require single quotes for their values in INSERT and UPDATE statements. All other columns don't.
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 | ||
|---|---|---|
| 3 | Stuff from Italy | food |
| 6 | Stuff from France | art |
| 7 | Stuff from Germany | toys |
| Domestic Stuff | ||
| 1 | Stuff from NJ | garden supplies |
| 2 | Stuff from TX | Tex-Mex |
| 4 | Stuff from LA | neat |
| 5 | Stuff from CA | sunny |
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
- Render unto SQL
- Minimize Result Sets
- Put JOIN Conditions in the ON Clause
- Use junction (assignment) tables for many to many relationships
- Follow Consistent Naming Conventions. Stick with the shop style.
- Use the Army Column Naming Convention
- Get the Identity Column Value From a New Record
- Group Result Output with Nested CFOUTPUT Tags
- Indent, Comment, and Document
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.