Coding Standards Part 2 - Formatting
-
Steve - a very good article - thanks very much! I also was struck by practicality of your use of commas ahead of each column in the SELECT.
I'd be interested in anyone's thoughts about formatting the SQL Server CASE expression
Best regards,
SteveR
Stephen Rosenbach
-
I tend to format CASE using indents (tabs) for the different lines.
SELECT
case
when x = 1
then 10
when x = 2
then 20
else 50
end 'my col'
As far as the GUI. Personally I do not think it works well for complex SQL, but that's me. I understand why people use it, but with outer joins and unions and cases, it falls down (to me). When I truly need to debug, it is often from Profiler or embedded SQL in an app and the the GUI doesn't really help me. Too slow. Don't really have a recommendation here because I do not use it.
I don't upper case the keywords because I'm lazy. Grew up on Unix/DOS and prefer lower case for most things. However, you should do what makes sense for you and be consistent (standard) for others in your environment.
Steve Jones
-
Hey, Vince's VB SQL Formatter Actually works, I like it. It could probably be enhanced a bit with a UI or something but it's great to find that someone has actually done most of the hard work to get to at least a consistent SQL format.
-
I really enjoyed this article as it is filled with great practical advice. It's going to be a link I'm going to forward to all our development groups.
I tend to do most of my SQL coding via a text editor. For me it is faster when compared to the time it takes when I include all the mouseclicks and screens to create relationships, indexes, and the like. Also, where I work we tend to reuse our code heavily. Since I do a lot of proofs of concept which get built upon by others, this is especially true of my stuff.
As a result, GUI generated-code works great... the first time. However, if we're taking scripts we'll need to use again and again, but with slight modifications, formatting is essential. It's not just for luddites. Consider taking a script that has to be used to create twenty databases across four servers all with minor tweaks based on the requirements of individual customers which differ slightly but not drastically. GUIs are too time consuming to build each database. Well-formatted code in scripts is priceless.
Also consider the more common case of building scripts to move from development, to QA, to production. If there is a DBA review, the scripts should be well-formatted. This allows a DBA to be able to follow the code a bit better, speeding up the approval process.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Completely agree with Brian (and thanks).
The GUI does cause slowdowns and issues in deployment. A few references (from my point of view):
http://www.sqlservercentral.com/columnists/sjones/wp_gui.asp
http://www.sqlservercentral.com/columnists/sjones/vcspart3.asp
Steve Jones
-
Good article and thoughtful responses -- one topic not covered: as a consultant, I write a lot of TSQL scripts and have been playing around with different file headers making use of SQL2K Query Analyzer's 'Replace Template Parameters' function. I've appended my basic template (which may look terrible in this post depending on how short your displayed line width is), but I'm curious what other people find useful.
/*
==========================================================================
Name: <Name of script, String, A Script>Version: 1.0
Author: <Author's name, String, Mark A. Denner>, Baker Robbins & Company (email <author's email, String, mdenner@brco.com>
Creation Date: <Date created, Date, Today's Date>
Created for: <Client's name, String, Client Name>
All rights reserved.
==========================================================================
DESCRIPTION
-----------
<Description of script, String, Description>
DEPENDENCIES
------------
<Dependencies or assumptions, String, Dependencies or assumptions>
ARGUMENTS
---------
<Arguments or options for the script, String, Arguments or options for the script>
UPDATE HISTORY
--------------
1.0<Date created, Date, Today's Date><Author's initials, String, MAD>Created
*/ -
Great point. I wrote an article on Templates in Query Analyzer and how they use them, but had to release copyright to SQL Server Magazine. The link is here and I highly recommend templates for any development.
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=21176
Steve Jones
-
I would go for something like this as it is very easy to read and
you can see the columns being selected,How many tables being referenced and
the join conditions pretty handy for documentation too:-)
SELECT
*
FROM
customers,
orders,
order_details
WHERE
customers.order_id = orders.order_id and
orders.order_id = order_detials.order_id and
order_details.amount > 100
Edited by - ramesh on 08/20/2002 7:39:41 PM
Ramesh -
I think you should go with what works. Personally I find the indentations easier to read, but that's me.
Steve Jones
-
We've recently started prefixing column names with a two or three letter prefix to identify table. This makes writing joins much easier and helps instantly identify columns and their respective tables in a more complex query. This makes every column name unique across the database and does away with the need for aliases in everything except triggers. So a basic join might look like this:
SELECT
CUS_Customer_ID
, CUS_Customer_Name
, COUNT(ORD_Order_ID)
, SUM(ORD_Order_Value)
FROM
CUSTOMER
INNER JOIN
ORDER
ON
CUS_Customer_ID = ORD_Customer_ID
GROUP BY
CUS_Customer_ID
, CUS_Customer_Name
This works for us but I would be interested to hear what others think.
Greg
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa -
What are your opinions on numer 1, 5 and 6 on http://www.intelligententerprise.com/001205/celko1_1.shtml
?
-
>>What are your opinions on numer 1, 5 and 6 on http://www.intelligententerprise.com/001205/celko1_1.shtml?<<
Hi Michael!
Thanks for the great link to Joe Celko's article, "Ten Things I Hate About You - SQL needs to break old habits and find its own voice" Excellent reading as always from Mr. Celko.
Although I concede that Joe is one of the guru-est of all SQL gurus, I must beg to differ with him on item 1 "Punch Card formatting." In my experience, formatting a SQL statement in the way Joe says *not* to, makes it more readable, not less. It is exactly the same argument that he makes for using mixed case - the structure and patterns help make the statement more readable.
On Item 5 - "Prefixes on variables and schema objects" - I would generally agree with Joe. There just isn't a good reason to do it. Moreover, putting prefixes on column names to show data type can really mess you up if you ever have to change the data type - you may have to go all through the database changing constraints, relations, etc.
I don't use "tbl" anymore as a prefix for table names (coming from MS Access, this was a habit hard to break.) But, I think using something like "vw" as a prefix for a view name still makes sense. My reasoning is that it's nice to instantly know that there is a view involved when you see something like
...
FROM vwTrendingByMonth vt
INNER JOIN IncidentCodes ic
ON vt.IncidentCode = ic.IncidentCode
...
As to Item 6 - "Different Names for the Same Data Element" - I must plead guilty of doing this both ways at times. I think I see Joe's point about an attribute being an attribute no matter which entity it belongs to. Also, his point about prefixes making it hard to find, for example, all of the address columns in the database - in case you need to put contraints on them - is a good one. I have to think about this one some more.
I agree with Joe on his items 2, 3, 4 and 7.
On Item 8 - "Needlessly proprietary code" - I also agree - and the keyword is "needlessly". I think portability of SQL is sometimes overemphasized -- why not take advantage of ANSI 92 join syntax in SQL Server, for example, if there is virtually no possibility that you will ever move to Oracle, which only supports "WHERE clause" joins? But I agree there is no good reason for *not* using the ANSI 92 syntax in SQL Server in cases where it also supports a non-standard or older syntax.
BTW, one of the things I enjoyed most about Joe's article was this statement in Item 8: "The poor Oracle people are forced into proprietary code because their product is so bad, which is another issue." Thanks, Joe! I always wondered why such a high-performance DBMS continues to have such a poor implementation of SQL.
Best regards,
SteveR
Stephen Rosenbach
Arnold, MD
-
I read a bunch of Mr. Celko's posts in Usenet when I was getting started and learned a lot.
However, as mentioned above. I like the punch card formatting for #1. For precisely the reason Joe mentions in his article. I can easily remove a line or two with a simple comment and can easily read the statement.
I'll admin that in a long column list this is harder to work with since the whole statement no longer fits on the screen, but faster for editing and testing.
#5 - Agree. No reason to prefix things. With .NET, MS has gone away from this notation.
#6 - Agree. I thought it was interesting when I worked in a database (3rd party) the had a "person" table. with an ID column. Every other table that referred to this was "personid". Same for product. The product table had "id", and other tables had "productid". Made sense. Ran into problems when there were two foreign keys. Like "studentid" and "teacherid" both referring to student.id.
Seemed good, got slightly confusing over time. Not that it was a bad design idea, just not implemented consistently.
Personally I like writing student.studentid = class.studentid rather than student.id = class.studentid.
Steve Jones
-
Excellent Celko article (as always).
Does anyone have a link for the Metadata Standards Committee naming conventions he discusses in that articl?
Cheers,
. . Greg
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply