|
Return to Newsletter Contents...
Standardizing the Standards
by: Troy Gottfried, Sr. BI Consultant, BECP, CRCP, MCP
Structured Query Language (SQL) is a standardized, low-level development
language used to query SQL-compliant databases.
The American National Standards Institute (ANSI) put forth considerable
effort to standardize the language so that code could be ported between all
SQL-compliant database platforms.
However, the standards developed by ANSI are of little help for devising our own
internal standards with respect to formatting, commenting, and maintaining SQL
code within our organizations. The
benefits of developing internal standards by which all developers within an
organization should comply are tremendous compared to the minimal effort put
forth to create the standards themselves.
In this installment of SQL Talk, we will explore some of the variables to
consider when developing SQL standards, along with some suggestions on how to
make your SQL code more readable and supportable.
This article should by no means be considered an exhaustive discussion of
SQL standards. Rather, my aim is
that this be used as a primer for your own discussions on standards within your
environment.
For our example, I’ve chosen a very simple query that we can work into a more
manageable format. Keep in mind that
the more complex the statement, the more you will benefit from instituting
standards within your own organization.
Consider the following query:
select customer_name, order_id, order_amount
from Customer, Order
where Customer.customer_id = Order.customer_id
and customer.active_flag = 1
The first step we’ll take in our standardization is to make the code more
readable. To accomplish this, I will
uppercase all SQL commands and keywords, and use two-spaces for my indentations.
You could use tabs, but keep in mind that when nesting statements, you
can end up all the way across your page before long:
SELECT
customer_name,
order_id,
order_amount
FROM
Customer,
Order
WHERE
Customer.customer_id =
Order.customer_id
AND customer.active_flag = 1
The second step I will perform is certainly open to debate, but I feel that
keeping current on ANSI standards is important.
There have been several iterations of the ANSI standards for SQL, and
among the changes is the inclusion of joins in the FROM clause as opposed to the
WHERE clause. Additionally, I have
elected to more fully qualify the field names in an effort to minimize confusion
as to where the fields are being sourced.
Consider the above statement re-written with those modifications:
SELECT
customer.customer_name,
order.order_id,
order.order_amount
FROM
Customer INNER JOIN Order
ON customer.customer_id =
order.customer_id
WHERE
customer.active_flag = 1
While this particular statement does not change dramatically, moving our joins
to the FROM clause separates those filters which are join-based filters vs. the
filters which are based on individual fields.
This can also help in troubleshooting SQL code that is returning a
smaller record set than expected. We
could quickly comment out our entire WHERE clause without causing join issues
between tables.
While we’re on the subject of comments, I would normally modify the above code
so that all commas (,) are at the beginning of the next line, as opposed to the
end of the preceding line. Again,
this makes commenting out lines of code much easier, since we will not have to
worry about syntax errors with missing commas:
SELECT
customer.customer_name
,order.order_id
,order.order_amount
FROM
Customer INNER JOIN Order
ON customer.customer_id =
order.customer_id
WHERE
customer.active_flag = 1
Additionally, there are comments usually added to
complex code that may help identify the author
of the code, the date it was created, the date it was modified, and what
modifications were made and why.
Some developers prefer to use a double-hyphen (--) at the beginning of each
line, while others prefer to use a block-commenting indicator such as ‘/*
*/’. The decision you make
here is not a light one. While block
commenting has it’s advantages, other tools in which you write this SQL code
should have a bearing on which commenting paradigm you choose.
For example, when working with Business Objects Data Integrator, the SQL Script
object will not parse correctly (nor run correctly) should you decide to use the
‘/* */’ methodology.
Therefore, in environments that use DI, I recommend they use the
double-hyphen for commenting. Try to
keep in mind all products that will use your SQL code, and what each application
requires.
Creating aliases for tables can be a great time-saver if there are many tables
and columns being used in the statement.
Keep in mind, however, that it can also slow down supportability, and
that if aliases are used, there should be standards built to ensure that
different developers would alias the same table the same way in their own code.
Standards can also be built for the use of SET operators such as UNION,
INTERSECT and MINUS or EXCEPT. For
example, some organizations may elect to always use a NOT IN operator instead of
EXCEPT. While these two examples
actually produce the same performance in SQL Server, that may not be true of all
RDBMS platforms. Performance should
also be considered when developing standards for SQL code.
Stored Procedures and Views have their own set of reasons for adhering to
standards, and great care should be taken to weigh all of the above variables
when developing your own SQL standards.
The time and effort spent in creating and maintaining standards are
minimal compared to the time and effort you would spend supporting the resulting
code without standards in place.
Go to Top |
Return to Newsletter Contents
|
