June 18, 2010 at 3:15 pm
Here is how I would format the same SQL. I like all lowercase and indent my joins, which makes it easier to read when there are four or more joins.
select
a.column_name_1,
b.column_name_2
from table_1 a
join table_2 b on a.table_1_id = b.table_1_id
order by
a.column_name_1,
b.column_name_2;
If there are only a few columns, then sometimes a string them all on one line like this:
select a.column_name_1, b.column_name_2
from table_1 a
join table_2 b on a.table_1_id = b.table_1_id
order by a.column_name_1, b.column_name_2;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 18, 2010 at 3:20 pm
Different styles are different styles, neither good nor bad:
SELECT a.c1
,a.c2
,b.c1
FROM db.schema.tbla a
INNER JOIN db.schema.tbla b
ON b.col1 = a.col1
AND a.col2 = b.col2
June 18, 2010 at 4:07 pm
I appreciate all your comments. I learned a few things as well.
I agree that I like to keep my columns on one line unless it spans many lines inwhich I will indent the following lines. I also only add less than 10 columns per row to make sure it doesn't get too long. I also like to indent my joins. I keep all key words capitalized. Like my select statements I like to keep my where clauses the same.
Regarding commenting of code: I thought about adding it into this editorial but felt that it only relates to this editorial partially and really is a subject all on its own. Having good commentation does make your code readable but in a different way that what I intended. Commenting your code makes the business logic of the code readable.
June 18, 2010 at 9:03 pm
Nadrek (6/18/2010)
Different styles are different styles, neither good nor bad:SELECT a.c1
,a.c2
,b.c1
FROM db.schema.tbla a
INNER JOIN db.schema.tbla b
ON b.col1 = a.col1
AND a.col2 = b.col2
Heh... well, except for maybe that leading comma thing that some folks like to use. 😀
--Jeff Moden
June 18, 2010 at 10:07 pm
re: leading commas. It makes them much more visible than when they're off the right side of the screen. Also, it makes it easier to comment a single field from a select clause without having to visit the previous line-end (and uncomment moments later)
re: single field or multiple fields per line. Why does it matter how many there are? I don't like to make that decision for how many are too many for a single line - each gets its own line. If you don't have a mouse with a scroll wheel, get one. 🙂
re: fieldalias = expression vs expression as fieldalias. I know it's less portable to go off the standard, but for the same reason as leading commas I like the fieldalias to line up visually. I usually also put tabs before the equal sign so they line up too. Unlike web code (HTML/javascript/css), the cost of whitespace in source is negligible compared to the value of increased readability.
re: indentation. following on the value of whitespace, indentation of each component of a clause (Select/fields, From/tables, Where/conditions, etc) makes it easy to see where is occurs because the clause is effectively outdented from the rest. I also indent the ON clause so I can visually separate the table list from their relationship. I think it's also a good idea that the left side of the ON clause should be to the table just mentioned and the right side of the expression should be to the table mentioned previously. That convention is consistent, which also helps reduce confusion.
re: delimiters. I know it might seem tedious to square-bracket fields unconditionally but it improves readability especially to indicate the difference between primary keys [tableid] and table names (table). Make wrong code _look_ wrong [1] and you'll be able to spot errors very quickly.
select
[fieldA] = coalesce( t1.[fieldA], '')
,[fieldC] = convert( varchar(50), t2.[fieldC])
,t2.[fieldD]
from
table1 t1
join
table2 t2
on t2.[table1Id] = t1.[table1Id]
where
t1.[fieldB] = @LookupValue
[1] http://www.joelonsoftware.com/articles/Wrong.html <-- h/t to this author, excellent read.
June 19, 2010 at 4:35 pm
Simple thing to do, makes a lot of sense and saves a lot of time down the road.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2010 at 12:17 am
What I *really* hate is the clown who decided to name the columns with reserved words...
SELECT id
, database
, clientno
FROM dbo.Accounts
WHERE etc etc
has to become
SELECT [id]
, [database]
, clientno
FROM dbo.Accounts
Why couldn't they have named the columns a little more logically:
SELECT accountID
, client_group
, clientno
FROM dbo.Accounts
EDIT: This was lined up nicely in the preview window :angry:
June 21, 2010 at 2:41 am
Totally Agree..
I too had some hard time understanding the code.. in the mean while cursing the person who developed it so ....... ha ha
June 21, 2010 at 10:09 am
An interesting point in a style of coding that I’ve come across concerns input parameters in a Stored Procedure. “_in” was put on the end of every input parameter. So if you’re sending the Account number to the procedure, it would be
(@Account_in int)
I thought that was a pretty neat indicator to put into the stored procedure to easily find where your input parameters are being used. And then, for anything that is actually output, to put “_out” on the end.
Just makes the code a bit tidier in my opinion.
Formatted code is a definite plus. Makes debugging the code a lot easier. Whatever style one uses, it should be consistent. The database engine doesn’t worry about the code looking pretty, the guy reading the code afterwards and figuring out what’s now going wrong or how to modify it does.
Comments are a luxury the fellow after you wishes you had indulged in. Give that guy a break, comment code. It might be you swearing at the writer trying to figure out what he trying to do. In my job duties, I tend to write ad-hoc queries only, so I’m not commenting much stuff. But the ones that stick around for a while, I comment so I know what they do when I have to pull them out later and the guy who’s coming behind me can more easily figure out what I was doing.
-- Kit
June 21, 2010 at 10:48 am
What I *really* hate is the clown who decided to name the columns with reserved words...
Oh sure, we have that; though I can't take credit for it :-P.
Select [DateTime] from sometable --It's a case sensitive DB to so it must be "DateTime".
Keywords (or any construct) in all caps makes code look very busy to me; like there's more you have to dig thru :unsure:
June 21, 2010 at 11:20 am
Any thoughts about how to comment a block of T-SQL ?
I do this often, but over time I havn't been consistent with it.
/* begin: What I'm doing here is ... */
insert ...
update ...
select ...
/* end */
--<What I'm doing here is ... >
insert ...
update ...
select ...
--</>
/* 42: What I'm doing here is ... */
insert ...
update ...
select ...
/* 42 */
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 21, 2010 at 1:27 pm
I have been working with SQL Server for about 10 years. I have been in a SQL Server specialized job for about 6 months. I have refined my coding style substantially over the last 6 months. Things that I did occasionally did not really bother me, now I would never consider doing some of these things because I have seen issues in doing things that way when doing it daily. I also remember disliking a number of things that I do regularly now because I have seen the wisdom in that style.
Much of good formatting is learned from seeing what others do, and recognizing why that is a good practice. I think my code is much easier to read now than it was a couple of years ago.
Yes, I agree that good formatting is useful, even important, but few people start out formatting their code really well. It should be taught and learned.
June 23, 2010 at 2:35 pm
Where do I begin? The question is should we make our code readable? This is a no-brainer. YES! As a professional we have a responsibility to help the company run efficiently. Unreadable code doesn't accomplish that goal at all. In fact if it is unreadable the author may not be able to read it either!
Someone mentioned pride in ones work--They're absolutely correct. If you happen to work for another techy, you will be caught and brought in! At least if they're up to speed. If you don't, how would you expect to make 'manager' with sloppy work?
So much more to say, but the words all repeat the same answer. Of course code must be readable.
Larry
June 23, 2010 at 4:04 pm
Commenting:
I like to see a comment block/change register at the top of each stored proc/function, something like
/* ************************************
Name: pu_mytable
Purpose: Updates dbo.MyTable with values from dbo.MyOtherTable
Date Version Author Description
***********************************
1/1/2010 1.0 Great Coder Created
2/1/2010 1.1 Great Coder Removed cursor, changed to set based update
************************************ */
but within the code, I like to see explanations before something funky - I don't believe its necessary to comment where something is blatently obvious.
I also like to have my internal comments using 'single line' commenting (--) rather than block commenting (/* */)
Also, any time you make a change, mark it with the version number!!!!!
June 24, 2010 at 2:02 pm
That's nice. We often think about versioning our office documents and client program code this way but not our T-SQL procs. I comment in-line a lot but a version header might make it easier to get started looking at it. Isn't there documentation software that helps you do this?
For comments I start with -- but a soon as it gets to be longer than a line or 2 I switch to /* */.
Ken
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply