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 6:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589
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.
Post #712545
Posted Thursday, May 7, 2009 9:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 31, 2013 7:27 AM
Points: 1, Visits: 74
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

Post #712585
Posted Thursday, May 7, 2009 9:52 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 5:03 PM
Points: 10, Visits: 136
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!
Post #712592
Posted Thursday, May 7, 2009 10:17 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:52 PM
Points: 36,773, Visits: 31,229
42

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #712602
Posted Thursday, May 7, 2009 11:10 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 10, 2014 3:10 AM
Points: 796, Visits: 245
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

Post #712628
Posted Friday, May 8, 2009 12:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:06 AM
Points: 337, Visits: 1,981
Jan Van der Eecken (5/7/2009)[hr
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.


Whoah! calm down

I said it looked gramatically wrong, not it is grammatically wrong

Leading 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 :)
Post #712650
Posted Friday, May 8, 2009 1:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:10 PM
Points: 2,441, Visits: 6,430
Samuel Vella (5/8/2009)
Jan Van der Eecken (5/7/2009)[hr
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.


Whoah! calm down

I said it looked gramatically wrong, not it is grammatically wrong

Leading 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 :)


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.


--------------------------------------------------------------------------
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 #712683
Posted Friday, May 8, 2009 1:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:10 PM
Points: 2,441, Visits: 6,430
rodgear (5/7/2009)
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



LOL But I'd prefer to have a backspace key as well.


--------------------------------------------------------------------------
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 #712694
Posted Friday, May 8, 2009 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 12:17 PM
Points: 8, Visits: 66
The 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.

SELECT
usr.UserAccount
, usr.UserName
, ugm.SecurityRightsMask
FROM 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'
)

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:

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 )

Critics have at it, but the fact is, you can tell what the routine is doing.

It's quite annoying to deal with:

SET @Multiplier = CAST (decimal(23,10),@MultiplierText)+COALESCE(
@OffsetDefault,@OffsetUserIndex,(SELECTMAX(OffsetCoefficient) FROM OffsetList WHERE
MultiplierFlag=@MultiplierFlag),0)

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.



If technology is supposed to give us more freedom and empower us to pursue the more important things in life, why do so many people allow themselves to become enslaved by it? Always remember, the truly important people cannot be reached... except when they want to reach you.
Post #712951
Posted Thursday, May 14, 2009 8:07 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:32 PM
Points: 1,045, Visits: 2,722
Sylvia, 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".




Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #717532
« Prev Topic | Next Topic »

Add to briefcase «««45678

Permissions Expand / Collapse