May 7, 2009 at 10:44 am
I read this thread in total amazement. It has become quite obvious that there are not only a variety of styles of code development but developers have become quite sensitive towards their coding style.
To add my 2 cents worth, someone who has been writing SQL code for a very long time I find that
formatting code is probably the most significant for code readability than anything else. As someone pointed out in an earlier post, formatted code is first.
Leading commas has nothing to do for readability but for ease of development. To me, I will typically use trailing commas but if the code has leading commas then I will use them. This, I see, is more of a preference than anything else.
CAPS for SQL reserved words... bahhh humbug! I have found that I can write SQL code much faster if I everything is in lower case.
so folks... SO FLAME ON!!!!
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
May 7, 2009 at 10:49 am
This thread reminds me of a first week at a new job I had.
I had to write a bit of C for something (I've been a DB guy since I left uni) and one of the senior programmers said:
"I hope your not one of those ******* who doesn't put the opening curly bracket on a new line"
Luckily for both our sakes I wasn't 😉
May 7, 2009 at 11:04 am
I disagree with most of this article suggests:
1) Entire tablenames as alias make the code harder to read and understand. There's nothing wrong with aliasing an orders table as o, in fact it makes the code more readable. Only in the most incredibly complex join scenarios is is necessary to use more than three characters as an alias.
2) No aliases at all is equally ill advised. Anyone who has used the SQL 2008 Management Studio extensively knows that aliasing a table in even the most simple query lets you take full advantage of intellisense (you get a finite list of the columns in the table after you type the period, rather than a list of all commands that start with that letter interspersed with the column names), whereas if you do not alias your table intellisense does not help you as often and you're more likely to make mistakes. Aliasing all tables with a one or two-letter alias also allows you to avoid future problems when you attempt to add a column to one of your tables later on, and it causes ambigous column name errors.
In fact since SQL 2008 came out I've been aliasing all my table names even in the case of no joins, because I ge the beneift of inaccurate intellisense.
May 7, 2009 at 11:26 am
Gregg Murray (5/7/2009)
I disagree with most of this article suggests:1) Entire tablenames as alias make the code harder to read and understand. There's nothing wrong with aliasing an orders table as o, in fact it makes the code more readable. Only in the most incredibly complex join scenarios is is necessary to use more than three characters as an alias.
2) No aliases at all is equally ill advised. Anyone who has used the SQL 2008 Management Studio extensively knows that aliasing a table in even the most simple query lets you take full advantage of intellisense (you get a finite list of the columns in the table after you type the period, rather than a list of all commands that start with that letter interspersed with the column names), whereas if you do not alias your table intellisense does not help you as often and you're more likely to make mistakes. Aliasing all tables with a one or two-letter alias also allows you to avoid future problems when you attempt to add a column to one of your tables later on, and it causes ambigous column name errors.
In fact since SQL 2008 came out I've been aliasing all my table names even in the case of no joins, because I ge the beneift of inaccurate intellisense.
I would just like to point out that 2008 is where intellisense is being used. Maybe I've been doing this way too long but my opinion is that as nifty as intellisense is, why did Microsoft take so long to put it into SQL Server Management Studio? To me it is a gadget I can live without.
Many years ago I learned a very important lesson. Making code readable is probably the most important. If you are dealing with over 100k lines of code a developer digging into a problem will solve that problem when the code is readable. I have taken the effort to do this in all code development. A real, true production environment a developer can waste precious time having to decipher ugly code before making a fix.
I made every effort to make all newly developed code easy to understand, not because it looks pretty but if I have to go back to it 1 or 2 years later I can easily recognize where a problem area is and fix it with minimal about of time.
Case in point. Many years ago I was asked to look into a problem a senior developer was having with a communications program he wrote. It was truly a mess and very difficult to read. Fortunately it wasn't very many lines of code. I took about 1 hour reformatting the code before digging into what the problem was. Within 5 minutes the problem was fixed and I fixed 3 additional problems the developer didn't even know existed. Big Fat Lesson Learned here.
For years coders have developed their own "style" of coding. Each takes pride in that style and feels very comfortable supporting the code. Ask yourself 2 simple questions, will you or someone else be supporting the code 2-4 yrs down the road? If so do you really think you will be able to resolve issues in it? Now try to support legacy code someone else wrote 5 years ago. Development standards go a long way for maintainability.
If you follow simple and basic techniques so either yourself or the next person down the road can support code then you've acquired the right skill set.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
May 7, 2009 at 11:49 am
You must be looking at my code.
The joins on the end of a line really goof me up.
Also when you are shooting a Prod problem you can comment out the joins with a leading -- which makes debugging easier.
May 7, 2009 at 12:53 pm
Kurt,
I've tended to agree. I never missed Intellisense in T-SQL, and never wanted it, but I have used it and liked it in VS. I think part of that is not being as familiar with the languages as I am in T-SQL.
that being said, I've seen some people use SQL Prompt from Red Gate and really have their coding fly since they know how to take advantage of the features.
May 7, 2009 at 1:47 pm
karlsandfort (5/7/2009)
Yes, but V for Vendor and VA for VendorAddress would make it more meaningful and understandable (if you know the schema), wouldn't it?
[/quote]
I agree, but.....
I always found it easier to visualize the execution plan by referring to the driving table as "a". Plus, I wouldn't have to think about things like "well, I've got two tables that start with 'cl', so I'll call one 'cla' and other one 'cli' and...", well, pretty soon the syntax becomes cluttered again.
So for me it's always been abc. Simple.
What else I find interesting that, in the Oracle world, I got used to underscores and either all upper-case for keywords and all lower-case for indentifiers. I call it "Oracle style".
But now I work for a company that uses SQL Server and it's all mixed case.
So I no longer use "abc" and I code in SQL Server style. I tend to go with the flow.
[/quote]
You got a point there Karl about the driving table being aliased as "A", never thought of that. However, I still prefer aliases to be consistent across multiple statements, i.e. in my next query the driving table (which you would call "A") is different to the one in the previous query.
May 7, 2009 at 2:02 pm
Samuel Vella (5/7/2009)
tlehner (5/7/2009)
What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...
Leading commas looks grammatically wrong
compare (Addresses used to be written with comma's to seperate each address element - at least thats how I was taught)
Queen of England,
Buckingham Palace,
London,
England
with
Queen of England
,Buckingham Palace
,London
,England
Samuel, no, I don't put ANDs and ORs at the end of the line. They go where they are most easily visible and comprehensible for the reader who comes after me, i.e. at the beginning of a new line of code AND properly indented.
Re Grammatically looking wrong, no, it is not. There are no rules in any natural language I know of that impose a rule as to whether a comma must be at the end of a line or at the beginning. If we are talking grammar, then only the rules as to where a comma must be placed inline to make syntactical or sementical sense are defined. It's only some older computer languages that imposed rules that forced one to put certain statements at certain offsets from the begiinning of a new line. Darn, I hated those.
May 7, 2009 at 4:22 pm
Hi,
I once worked with a database where the table design (primary key in particular) had been made with alias's in mind. I really liked it and found it very useful to work with, and made deciding which alias to write very easy.
eg
SELECT
..
FROM
PATIENTS PATNT INNER JOIN
REFERRALS REFRL ON
PATNT.PATNT_REFNO = REFRL.REFRL_REFNO INNER JOIN
WARD_STAY SSTAY ON
PATNT.PATNT_REFNO = SSTAY.SSTAY_REFNO
Hope you get the drift.....
May 7, 2009 at 4:35 pm
David Burrows (5/7/2009)
And, heh... don't get me started on the practice of using leading commas in code.
Oh! Come on Jeff don't be coy now :w00t:
For the record, when I code SQL I,
Use trailing commas
Aliases when more than one table/subqeury
Aliases always in subqueries
Try to make alias meaningful (ie o for Order, ol or OrderLine etc)
Uppercase SQL keywords
Indent JOIN beneath FROM
Indent ON/AND beneath JOIN (each comparison on separate line)
Indent subqueries and subquery SQL
I agree with you on most of this...
However, I don't like all UPPERCASE or lowercase SQL keywords. I prefer mixed case, especially for keywords that are, in fact, multiple words: CharIndex, RaisError (I hate how that is mis-spelled), DatePart, etc. A nested set of functions quickly becomes unreadable when in all uppercase, and is almost as bad in all lowercase.
I use uppercase to draw attention, ie. select DISTINCT, FOR XML, LEFT OUTER JOIN, etc.
I also like to spell things out when given a chance, ie. DateAdd(day, 1, GetDate()) vs. DateAdd(dd, 1, GetDate()).
To add to your list:
Indent or/and beneath where.
I try to line up key phrases of select statements and variables in declare statements:
declare
@Variable1 tinyint,
@Variable2 varchar(50)
select
@Variable1 = 250,
@Variable2 = 'Hello World, what have we here?'
select
ColumnA,
ColumnB
from MyTable
where ColumnA = @Variable1
and ColumnB = @Variable2
To each their own... as long as it's consistent.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 7, 2009 at 6:37 pm
Readability cannot ever be the concern because each has a different level of comprehension. If uppercase or lowercase throws one off, then the level of comprehension is probably not the main concern.
Table aliases are meant for two things: to uniquely identify columns and to be able to quickly substitute a different table name in the from clause when developing and testing. If either of these needs is not required in a given situation, then all the aliasing, commas, upper/lower case issues are just syntatic sugar -- don't waste time.
In fact there is one line out thought that we should always use fully qualified object names in production code so the substitution time can be avoided, especially when using recompile directive.
May 7, 2009 at 9:33 pm
I've solved the comma argument for you all. I've taken them out all together. 😀
The best part is that you only need a keyboard with three keys.
01010011010001010100110001000101010000110101010000100000000011010000101000100000001000
00001000000010000000100000010000010010111001001010011101010110111001101011001011000000
11010000101000100000001000000010000000100000001000000100001000101110010011010110111101
11001001100101010010100111010101101110011010110000110100001010010001100101001001001111
01001101001000000101010001101000011010010111001101010100011000010110001001101100011001
01001000000100000100001101000010100100100101001110010011100100010101010010001000000100
10100100111101001001010011100010000001010100011010000110000101110100010101000110000101
10001001101100011001010010000001000010000011010000101001001111010011100010000001000001
00101110010010010110110101100001011001110110100101101110011000010111001001111001010010
01010001000010000000111101001000000100001000101110010010010110110101100001011001110110
10010110111001100001011100100111100101001001010001000000110100001010010101110100100001
00010101010010010001010010000001000001001011100100101001110101011011100110101100100000
001000010011110100100000001001110100110101101001011011100110010100100111
May 7, 2009 at 9:52 pm
Well, I'm not laughing and I find one letter aliases just fine; you are entirely unconvincing. I personally like to line up everything which requires single character aliases.
I found your leading commas, single field on each row, and indentation most annoying and difficult to read. I'm sure in a team setting we could come to some accommodation. Frankly discussions of style are rather stupid except in a team or company settings and articles on coding standards a big waste of time unless it is just to emphasize that you should have one whatever your group thinks it should be.
And please, get rid of the lower case on everything and the underscore "_id", ugh!
May 7, 2009 at 10:17 pm
42
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2009 at 11:10 pm
I too agree that using meaningful aliases increase the readabilityof SQL queries.
In fact,i follow the same practices as David Burrows except that i dont indent ON/AND beneath JOIN .
Happy querying........
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
Viewing 15 posts - 61 through 75 (of 79 total)
You must be logged in to reply to this topic. Login to reply