Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»

Enhancing the readability of your code: Table aliasing in sql Expand / Collapse
Author
Message
Posted Thursday, May 7, 2009 10:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
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
Post #712219
Posted Thursday, May 7, 2009 10:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:51 AM
Points: 328, Visits: 2,001
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
Post #712225
Posted Thursday, May 7, 2009 11:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 15, 2011 11:59 AM
Points: 4, Visits: 45
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.


Post #712242
Posted Thursday, May 7, 2009 11:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
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
Post #712263
Posted Thursday, May 7, 2009 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:43 AM
Points: 2, Visits: 56
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.
Post #712292
Posted Thursday, May 7, 2009 12:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:25 PM
Points: 31,279, Visits: 15,740
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.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #712355
Posted Thursday, May 7, 2009 1:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:01 AM
Points: 2,116, Visits: 6,441
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.


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #712397
Posted Thursday, May 7, 2009 2:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:01 AM
Points: 2,116, Visits: 6,441
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.




--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #712406
Posted Thursday, May 7, 2009 4:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:26 PM
Points: 81, Visits: 219
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.....

Post #712500
Posted Thursday, May 7, 2009 4:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:11 AM
Points: 5,366, Visits: 8,981
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

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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #712510
« Prev Topic | Next Topic »

Add to briefcase «««45678»»

Permissions Expand / Collapse