InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 04:11:05 GMT20http://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxSylvia, a well written article, and I can agree with you that using random aliases such as "a", "b", "c" is counterproductive. It's difficult to read and even more difficult to troubleshoot.However, I have to disagree with another of your key ideas. Your suggestion that it's not worth aliasing table names simply to save a few keystrokes trivializes the amount of keystrokes one may save over a year, or even a week, by using sensible aliases. Many SQL Developers (myself included) must maintain vendor applications over which we have no power to name objects. For example, one of my central applications has table names such as TPB105_CHARGE_DETAIL and TSM180_MST_COD_DTL. It is much easier and faster to type, and frankly easier to read, if I alias those table names using "charges" and "codes".Thu, 14 May 2009 20:07:22 GMTTim Mitchellhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxThe topic was use of aliases, and for me, I just make it a habit of using 3 meaningful chars in ALL cases, 4-5 when necessary. Make it a habit and COMMON throughout the entire database, and ALL your code becomes easier to read. (e.g. UserAccounts > usr, WorkPlanGroups > wpg, etc.).As far as leading commas vs. trailing, people coming from conventional C++ programmer backgrounds will prefer trailing commas, because it makes things more confusing, makes it easy to miss commas in huge sections of code, etc., and they love that.With a leading comma AND a space, code becomes incredibly neat and organized.[code]SELECT usr.UserAccount, usr.UserName, ugm.SecurityRightsMaskFROM UserAccounts AS usr WITH ( NOLOCK ) INNER JOIN UserGroupMembership AS ugm WITH ( NOLOCK ) ON ugm.UserGroupID = usr.UserGroupID AND ( ugm.UserGroupMemberMask & 4 ) = 4 OR ugm.UserAccount = 'superuser' )[/code]Note the extra spaces around parenthesis too, and double-indents for subcode sections.Whatever you can do to add white space (blanks, spaces, blank rows) ALWAYS makes things more readable. I take that to the extreme too. EVERY parenthesis has spaces around it, and every "continued thought" has its connector on the next line:[code]SET @Multiplier = CAST ( decimal ( 23, 10 ), @MultiplierText ) + COALESCE ( @OffsetDefault, @OffsetUserIndex, ( SELECT MAX ( oli.OffsetCoefficient ) FROM OffsetList AS oli WITH ( NOLOCK ) WHERE oli.MultiplierFlag = @MultiplierFlag ) , 0 ) [/code]Critics have at it, but the fact is, you can tell what the routine is doing.It's quite annoying to deal with:[code]SET @Multiplier = CAST (decimal(23,10),@MultiplierText)+COALESCE(@OffsetDefault,@OffsetUserIndex,(SELECTMAX(OffsetCoefficient) FROM OffsetList WHEREMultiplierFlag=@MultiplierFlag),0) [/code]The first thing I do when I find other programmer's code like that is add significant amounts of white space and separate it out onto different lines.Check any class on good writing techniques. White space is key. And leading commas, plus signs, multipliers, etc. is a excellent and instant shorthand way to identify continuance from a prior line.Bottom line is, to each their own. But I never have to spend minutes figuring out code that I did 4-5 years ago. It's all instantly obvious to me and anyone else who looks at it.Like it or not, coding is a form of communication. Take a good writing course (not fiction but how to write clear and consise text) and you'll be surprised how it improves your code formatting as well.:w00t:Fri, 08 May 2009 08:14:40 GMTCartoonHeadhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]rodgear (5/7/2009)[/b][hr]I've solved the comma argument for you all. I've taken them out all together. :-DThe best part is that you only need a keyboard with three keys.[code]010100110100010101001100010001010100001101010100001000000000110100001010001000000010000000100000001000000010000001000001001011100100101001110101011011100110101100101100000011010000101000100000001000000010000000100000001000000100001000101110010011010110111101110010011001010100101001110101011011100110101100001101000010100100011001010010010011110100110100100000010101000110100001101001011100110101010001100001011000100110110001100101001000000100000100001101000010100100100101001110010011100100010101010010001000000100101001001111010010010100111000100000010101000110100001100001011101000101010001100001011000100110110001100101001000000100001000001101000010100100111101001110001000000100000100101110010010010110110101100001011001110110100101101110011000010111001001111001010010010100010000100000001111010010000001000010001011100100100101101101011000010110011101101001011011100110000101110010011110010100100101000100000011010000101001010111010010000100010101010010010001010010000001000001001011100100101001110101011011100110101100100000001000010011110100100000001001110100110101101001011011100110010100100111[/code][/quote]LOL :-P But I'd prefer to have a backspace key as well.Fri, 08 May 2009 01:57:28 GMTJan Van der Eeckenhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]Samuel Vella (5/8/2009)[/b][hr][quote][b]Jan Van der Eecken (5/7/2009)[/b][hrSamuel, 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.[/quote]Whoah! calm downI said it [b]looked[/b] gramatically wrong, not it [b]is [/b]grammatically wrongLeading commas, for me are just not pleasing to the eye, it "looks wrong"Anyway, I'm sure we can argue about this till we're blue in the face and not come to an agreement about it :)[/quote]Apologies, Samuel, I must have misunderstood you. I wasn't getting upset by the way :-). And you are right, one can argue about style for ever and ever. The lowdown is, if you are working as an individual, do whatever pleases your eye, but make sure the next person working on your code (if ever) can actually read it without having to spend hours and hours reformatting before getting any real work done. If you work for a company and they do have a coding standard, either stick to it, or try to convince them to change (get your arguments worked out well first), or if that doesn't help, and you really can't live with those standards, you have only one choice: get yourself another job.Fri, 08 May 2009 01:43:50 GMTJan Van der Eeckenhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]Jan Van der Eecken (5/7/2009)[/b][hrSamuel, 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.[/quote]Whoah! calm downI said it [b]looked[/b] gramatically wrong, not it [b]is [/b]grammatically wrongLeading commas, for me are just not pleasing to the eye, it "looks wrong"Anyway, I'm sure we can argue about this till we're blue in the face and not come to an agreement about it :)Fri, 08 May 2009 00:32:59 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxI 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........Thu, 07 May 2009 23:10:18 GMTsunitkrishnahttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx42Thu, 07 May 2009 22:17:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxWell, 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!Thu, 07 May 2009 21:52:42 GMTCrankyRathttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxI've solved the comma argument for you all. I've taken them out all together. :-DThe best part is that you only need a keyboard with three keys.[code]010100110100010101001100010001010100001101010100001000000000110100001010001000000010000000100000001000000010000001000001001011100100101001110101011011100110101100101100000011010000101000100000001000000010000000100000001000000100001000101110010011010110111101110010011001010100101001110101011011100110101100001101000010100100011001010010010011110100110100100000010101000110100001101001011100110101010001100001011000100110110001100101001000000100000100001101000010100100100101001110010011100100010101010010001000000100101001001111010010010100111000100000010101000110100001100001011101000101010001100001011000100110110001100101001000000100001000001101000010100100111101001110001000000100000100101110010010010110110101100001011001110110100101101110011000010111001001111001010010010100010000100000001111010010000001000010001011100100100101101101011000010110011101101001011011100110000101110010011110010100100101000100000011010000101001010111010010000100010101010010010001010010000001000001001011100100101001110101011011100110101100100000001000010011110100100000001001110100110101101001011011100110010100100111[/code]Thu, 07 May 2009 21:33:09 GMTrodgearhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxReadability 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.Thu, 07 May 2009 18:37:31 GMTsjsubscribehttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]David Burrows (5/7/2009)[/b][hr][quote]And, heh... don't get me started on the practice of using leading commas in code.[/quote]Oh! Come on Jeff don't be coy now :w00t:For the record, when I code SQL I,Use trailing commasAliases when more than one table/subqeuryAliases always in subqueriesTry to make alias meaningful (ie o for Order, ol or OrderLine etc)Uppercase SQL keywordsIndent JOIN beneath FROMIndent ON/AND beneath JOIN (each comparison on separate line)Indent subqueries and subquery SQL[/quote]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:[code]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[/code]To each their own... as long as it's consistent.Thu, 07 May 2009 16:35:18 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxHi,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.egSELECT..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_REFNOHope you get the drift.....Thu, 07 May 2009 16:22:01 GMTScott Thornton-407727http://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]Samuel Vella (5/7/2009)[/b][hr][quote][b]tlehner (5/7/2009)[/b][hr]What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...[/quote]Leading commas looks grammatically wrongcompare (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,EnglandwithQueen of England,Buckingham Palace,London,England[/quote]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.Thu, 07 May 2009 14:02:02 GMTJan Van der Eeckenhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]karlsandfort (5/7/2009)[/b][hr][/quote]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.Thu, 07 May 2009 13:47:29 GMTJan Van der Eeckenhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxKurt,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.Thu, 07 May 2009 12:53:26 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxYou 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.Thu, 07 May 2009 11:49:32 GMTmark.finchhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]Gregg Murray (5/7/2009)[/b][hr]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. [/quote]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.KurtThu, 07 May 2009 11:26:31 GMTKurt W. Zimmermanhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxI 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. Thu, 07 May 2009 11:04:01 GMTGregg Murrayhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxThis 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 ;-)Thu, 07 May 2009 10:49:20 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxI 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!!!! KurtThu, 07 May 2009 10:44:13 GMTKurt W. Zimmermanhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxWell, I think leading commands are, well, easier to read:hehe:Thu, 07 May 2009 10:31:30 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxMy Opinion, which is the way i always do it, is not to confuse the issue but rather: to simply use grammer as gramer is meant to be ie:- not like this reply, which is difficult to read and understand the flow.:)Thu, 07 May 2009 10:31:01 GMTmike.wadehttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]Jeff Moden (5/7/2009)[/b]Heh... what's up with all the leading comma lovers? Look at the email you just wrote... no leading commas. ;-)[/quote]Lol...clever! I'll take it as a compliment that I'm able to switch between SQL and English so easily!Thu, 07 May 2009 10:28:19 GMTtlehnerhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]tlehner (5/7/2009)[/b][hr][quote][b]Steve Jones - Editor (5/7/2009)[/b][hr]For the record, I'm a leading commas guy, Jeff is wrong, and it's easier to read. All you trailing comma folk need to change!:-D:-D:w00t:However, I do think that in your group, you need to be consistent. I'd hate to upper case SQL, or go with trailing commas, but I would for consistency's sake.My preference is to camel case tables, go with 2-4 letter aliases that are meaningful (as in ALZDBA's example), and stick with lower case for most typing.[/quote]+1. This is almost exactly how I code SQL.What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...Tim[/quote]Heh... what's up with all the leading comma lovers? Look at the email you just wrote... no leading commas. ;-)Thu, 07 May 2009 10:16:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]Steve Jones - Editor (5/7/2009)[/b][hr]For the record, I'm a leading commas guy, Jeff is wrong, and it's easier to read. All you trailing comma folk need to change!:-D:-D:w00t:[/quote]BWAA-HAA!!! I was greasing up the ol' pork chop cannon for someone else, but I like big easy targets better. :-P I'll be back...Thu, 07 May 2009 10:14:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxI've come across code that used synomyns. Since synomyns are not common for me, at first glance I was like "What? Where's that table coming from? It's not listed in the database! And there's no alias in the code!?!?!" But, I'd figured it out eventually and, looking through the code, when you are constantly using a table from another database, it does help on the readability of the code once you realize a synomym is being used. As I get more used to it, it won't throw me for a loop as often.The problem I have with table aliases is not having the habit ingrained of where to look for the actual table names. Since I'm looking at the code in sequence, the aliases used in the select statement (the a.col1, a.col2, b.col1, c. col3) sometimes get confusing because the alias is defined later on in the code (in the FROM statement - from dbo.table1 as a) . I think the use of "as" in the defining of the alias is helpful because that makes it easier to spot to then understand where everything in the select part is coming from.But then, I am also pretty new to the SQL scene, so I'll probably get better at spotting the aliases the more experience I get.As for the leading commas, it's a preference thing mostly, but since I was forcibly taught in school that the comma has to go directly [b]AFTER[/b] the word, that there can be [b]NO SPACES[/b] between the word and the comma, I tend to go with the trailing commas. I have no wish to have my grammar school teacher haunting me because I put the comma on the next line. :crazy:So, sorry all you leading comma guys, I'm with Jeff on this one. :w00t:Thu, 07 May 2009 09:52:47 GMTKit Ghttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxHooooot!Leading commas all the way!CAPS for all sql keywords!Keep the aliases short and sweet!:-PI'd never have guessed the discussion would be this long, but it's a good read on a slow morning!Thu, 07 May 2009 09:48:41 GMTDavidLhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxMy .02:I prefer option B1. It's readable. The aliases don't get into the way of the column, which is what I'm really interested in in the Select.2. I know where the columns are coming from. I just scan down to the from clause.3. Aliasing to a fully qualified schema speeds up compiling.If you're worried about clarity, why not do (something like) this?[code]Select a.FirstName, --from Authors a a.LastName, a.AuthorID, t.Title, --from Titles t t.TitleIDFrom dbo.Authors ainner join dbo.Titles t on a.AuthorID = t.AuthorID[/code]Thu, 07 May 2009 09:46:43 GMTnathan.syversonhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxBe careful. I always try to alias tables in my code and mark every column with those aliases, even if the column names are unique to the objects in the statement *at that time*. It has happened to me before that a column with the same name as another column in the statement gets added to one of the other tables in the statement, thus breaking the code with an ambiguous column name error.Thu, 07 May 2009 09:34:23 GMTrsmith-796599http://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxI see your point, and use "proper grammar" rules for readability in most of my code, as in:[code]Left(ColumnName, 1)[/code]instead of[code]Left(ColumnName,1)[/code]This is just more natural to me.But at a certain point, that breaks down. Your example isn't SQL, so it's hard to judge SQL based upon it. I look at a select list much differently than parts of an address. I guess I use both leading and trailing commas depending upon situation. Trailing commas when the select list is simple (often w/o newlines for each column), and then at some point I'll switch to newlines and leading commas...especially when I'm not selecting:[code]select column1, column2, column3from...[/code]but rather[code]select , column1 , case when i.complete = '1901-01-01 01:00:00.000' then 'P' when i.complete = '1901-01-01 02:00:00.000' then 'M' when i.complete > '1901-01-01 02:00:00.000' then 'C' else 'I' end as status , right('00' + cast(datepart(minute, isnull(s.datecommitted, z.lastupdate) - s.datecreated) as varchar), 2) + ':' + right('00' + cast(datepart(second, isnull(s.datecommitted, z.lastupdate) - s.datecreated) as varchar), 2) as tot_durationFrom...[/code](BTW, I can't seem to control tabs well in the code blocks on the forum)The commas show my eye where each column to be returned starts.Thu, 07 May 2009 09:16:36 GMTtlehnerhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxSylvia is actually right when she says that there's no need for aliases in author-title query. But Jeff is right when he says that code is not written well for other people. Just as case, comma location, and aliasing should be consistent, so shoud the use of table prefixes. Either use them, or don't; don't just use them for ambiguous columns or for aliased tables.. I'd write the code this way [except I probably wouldn't put in these specific comments]:[code]select -- I prefer lowercase authors.Au_id, -- I prefer trailing commas authors.au_lname, authors.au_fname, titles.title, titles.title_id, titleauthor.royaltyperfrom titlesleft join titleauthor -- I use left, right, inner, or full outer before the join to keep my head straight on titles.title_id = titleauthor.title_idleft join authors on titleauthor.au_id = authors.au_id[/code]I find it interesting that there has been no mention yet of the most common usage for table aliases that I see: Multiple references to the same table. Here's a simplified example:[code]select loanapp.id, officer.name, processor.name, underwriter.namefrom loanappleft join contact officer on officer.id = loanapp.officer_idleft join contact processor on processor.id = loanapp.processor_idleft join contact underwriter on underwriter.id = loanapp.underwriter_id[/code]Does this just happen in my field or with the databases I have to work with?Thu, 07 May 2009 08:54:54 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]Gene Marini (5/7/2009)[/b][hr]Do they have computers in France?[/quote]Probably they do... we have some in Italy as well. I find this post very useful for the discussion.Thu, 07 May 2009 08:44:11 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]tlehner (5/7/2009)[/b][hr]What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...[/quote]Leading commas looks grammatically wrongcompare (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,EnglandwithQueen of England,Buckingham Palace,London,EnglandThu, 07 May 2009 08:30:55 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]Steve Jones - Editor (5/7/2009)[/b][hr]For the record, I'm a leading commas guy, Jeff is wrong, and it's easier to read. All you trailing comma folk need to change!:-D:-D:w00t:However, I do think that in your group, you need to be consistent. I'd hate to upper case SQL, or go with trailing commas, but I would for consistency's sake.My preference is to camel case tables, go with 2-4 letter aliases that are meaningful (as in ALZDBA's example), and stick with lower case for most typing.[/quote]+1. This is almost exactly how I code SQL.What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...TimThu, 07 May 2009 08:26:11 GMTtlehnerhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxFeeling hungry, pork chops sound good this am ;-)Thu, 07 May 2009 08:23:09 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx" I would need to be familiar with the database and the naming convention to know it."Maybe, im being harsh (no thing persona to anyone) but... To be able to be completely effective (as opposed to superficially) on complex queries an understanding of the database and data is essential. I never allow people the scope to change things they dont understand. Which is another reason why long winded aliassing is a waste of time.Thu, 07 May 2009 08:13:13 GMTmike.wadehttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxI tend to agree with careful use of aliases (make them readable and cogent) because:I have experienced the ambiguous reference reference error and reacted with "What do mean 'a' is ambiguous? Oh, I used 'a' twice. Need more coffee." I have used a lot of monitor space to spread out someone else's code to understand their references (a, t, tl, m, st all in one query wiht some for external references).It would almost be worth the inclusion of aliasing in a form of declare statement at the beginning of the code. (I know, I'll probably burn for that. But at least you would know where to look.)This goes along with the use of Temp, yes that makes it quick but not clear. I've shot myself in the foot often enough to now spend a moment or to more to use a more descriptive name. As I gain more experience and practice I find myself reviewing my own and others code with a more critical eye. The more we as a community write code the more we will be having to fix/modify/convert or in some way dig into someone elses code. Some form of standardization would be helpful as long as it is not restrictive to productivity.With all that said "How 'bout those synonyms?" Is any one using them? If you are using them would you alias them and if so would you do something special to identify the aliasing of a synonym?Thu, 07 May 2009 08:10:07 GMTal_kesslerhttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote][b]David Burrows (5/7/2009)[/b][hr][quote]... Jeff is wrong...[/quote]Wow! :Wow:No beating aound the bush eh Steve ;-)A poke in the eye, that'll trump his Pork Chops :w00t:[/quote]He'll start an anti leading comma campaign soon:CBAC (Pronounced cee-bac), Comma By Agonising Comma :-DThu, 07 May 2009 07:57:12 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspx[quote]... Jeff is wrong...[/quote]Wow! :Wow:No beating aound the bush eh Steve ;-)A poke in the eye, that'll trump his Pork Chops :w00t:Thu, 07 May 2009 07:54:06 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic711754-1224-1.aspxWe use a, b, c, etc as table aliases all the time in my SQL shop and we have never had trouble reading queries. Longer named aliases can make code look impossibly cluttered. Calling this way "wrong" is ridiculous.Thu, 07 May 2009 07:52:48 GMTgrendel19320