The second theme of
2005 for the Coding-in-SQL newsletter is T-SQL Coding Style.
That is, how T-SQL statements and comments are presented.
You may have already noticed that the formatting of the SQL
statements in this newsletter is slightly unusual. Over the last
few years, I've come to use a system that I call “Separator First
Formatting” or SFF. I've seen a few other programmers use it, so
I'm not the only one.
The principal practice in SFF is to put separators, such as
commas and “and” and “or” operators at the start of a new line.
We'll go into the details in a second.
There are several reasons for using the SFF system:
·
Documentation. SFF doesn't add documentation;
it's intended to promote it by making it easier to include
documentation on each line to explain the code.
·
Typability. Ease of adding and removing lines
in a series.
·
Visibility. Some of the separators such as join
operators and “and” logical operators are extremely important and
get visual prominence by starting the line.
·
Flexibility. By putting separators at the
beginning of the line it becomes easy to comment it out by adding a
double-dash at the beginning of the line. (Query Analyzer has a
hot-key, CTRL-SHIFT-C, that inserts the double-dash)
When formatting with separators first, almost all separators
start their own line. That leads to a lot of lines but most
importantly, when combined with T-SQL's double-dash comment, it
makes it easier to put an explanatory comment on each, and sometimes
every, line. Here's an example:
-- SELECT with
Seperator First Firmatting
SELECT
a.au_id
, [dbo].[udf_Name_FullWithComma](au_fname,
null
,
au_lname, null)
as Name
-- so it's sortable
, au_fname,
au_lname
-- sent so client grid can sort
, t.title
, ta.royaltyper
FROM
pubs..authors
a with (nolock)
RIGHT OUTER JOIN
pubs..titleauthor ta WITH (NOLOCK)
on
a.au_id
= ta.au_id
INNER JOIN
pubs..titles
t WITH (NOLOCK)
on
ta.title_id
= t.title_id
WHERE
t.type
= 'trad_cook'
and
ta.royaltyper
> 50 -- 50 cents. authors
aren't paid well.
ORDER BY Name
, t.title
-- multiple titles per author.
GO
For starters, each subclause (FROM, WHERE,
ORDER BY) of the SELECT starts a new line indented one
tab stop from the start of the SELECT. The first entry in
the list goes on the line with the subclause. That's a compromise
to keep the code slightly more compact.
In most circumstances, every entry in a list after the first
goes on its own line. There will be exceptions. For one, the
arguments to a function call rarely belong on their own line.
Therefore the lines:
, dbo.udf_NameFullWithComma(au_fname, null
, au_lname, null) as
Name –-- so it's sortable.
are broken only when it was necessary to do so for
publication. Sometimes space considerations are more important and
several lines are combined. For example this line has two fields on
it because they're covered by the same comment:
, au_fname, au_lname
-- sent so client grid
can sort on these columns.
My overriding decision criteria for layout is to make the
code more readable. It's just too bad that I don't get paid by the
line of code.
I try and put each table in the FROM clause on its
own line with any WITH clause. WITH clauses are hints
instructing SQL Server how to perform the query. This line pulls in
information from the pubs.titleauthor table:
RIGHT OUTER JOIN pubs..titleauthor ta
WITH (NOLOCK)
Notice that I almost always use a table alias. I also use
column aliases for every expression but avoid them when the column
is not an expression.
When I write SQL for publication I try to put SQL keywords
in upper case. I do that so that they are visually distinct. When
writing SQL for any purpose except publication, I type in all
lowercase except variable names that have upper case letters
embedded. The coloring that Query Analyzer uses is sufficient to
differentiate the parts of SQL. Besides, typing all those upper
case characters is harder on the fingers. After 30+ years of
programming my digits are pretty tired and they appreciate any break
that they get.
You may discover almost as many formatting conventions as
there are programmers. SFF works for me because it makes the SQL
more readable and easier to change. For example, to add another
column to the end of the SELECT list all that is necessary is
to put in the new line. There's no need to change the line before
the new one to add a comma after the expression. The same works in
reverse. By putting the separators at the start of a line, it's
only necessary to put a double dash at the start of a line to
eliminate it. There's no need to go to the previous line and adjust
the commas. For example, to eliminate the second conditional
expression from the WHERE clause, just add a double dash as
in:
WHERE t.type = 'trad_cook'
-- and ta.royaltyper > 50 -- 50 cents. authors aren't paid
well.
The 2nd set of double dashes that delimited the
original comment is ignored.
In a function creation script, SFF comes into play in the
parameter list, as you can see from this function declaration:
CREATE FUNCTION
dbo.udf_Name_FullWithComma (
@sFirstName
nvarchar(20) =
N''
-- First Name
, @sMiddleName
nvarchar(20) =
N''
-- Middle or Initial
, @sLastName
nvarchar(40) =
N''
-- Last Name
, @sSuffixName
nvarchar(20) =
N''
-- Suffix name, like Jr or MD
) RETURNS nvarchar(128)
WITH SCHEMABINDING
-- Or comment why not
/*
* The function comment block goes here
***************************************************************/
AS BEGIN
To make the CREATE FUNCTION script easy to maintain:
·
Put the opening parenthesis on the line with the
function name.
·
Skip a line
·
Put each parameter on its own line.
·
Use default values when possible.
·
Comment each parameter.
·
Put the right parenthesis that closes the parameter
list on a line with the RETURNS clause.
·
The WITH SCHEMABINDING
line. It has the WITH SCHEMABINDING line, or a comment that
says why the function is not schema bound. If you add WITH
ENCRYPTION it also goes here.
·
Finally, the AS BEGIN goes on its own line.
Not only do these
practices make formatting a little easier, they document the
parameters. One of the reasons to get every parameter on its own
line is to leave room for the comment describing how the parameter
is used. Unless the name, “says it all”, each parameter deserves a
comment describing how to use it.
This article is the start of the theme of "T‑SQL Coding Style". That
is, how to write T-SQL code so that it's readable and easily
modified. As always, if you have comments about the
newsletter and in particular, the new direction that it's taking, please let me know at
coding-in-sql@novicksoftware.com.
Transact-SQL User-Defined Functions has been published! Take a look at it
now!
Do you have an interesting stored procedure, user-defined-function,
or other sample code? If you'd like to share them please send
them in an email to:
coding-in-sql@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andy
Andrew Novick
