October 22, 2018 at 2:39 pm
drew.allen - Monday, October 22, 2018 9:43 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable aliases (in Join statements) -- Just Say No!
Your coding speed is probably not limited by your typing speed (thinking time is greater). Table aliases in Join statements make code more obfuscated. In my opinion. A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.Table aliases are sometimes required (such as self joins). I prefer to be consistent and always use aliases rather than to only use aliases when they are necessary.
I've also seen table names that are ridiculously long. I find that it obfuscates code much more to wade through long table names to find the columns being referenced.
Drew
Table aliases in a Join statement are ALMOST never necessary. Only in the case of self-joins and a few other, very limited instances.
October 22, 2018 at 2:56 pm
David Walker-278941 - Monday, October 22, 2018 2:39 PMdrew.allen - Monday, October 22, 2018 9:43 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable aliases (in Join statements) -- Just Say No!
Your coding speed is probably not limited by your typing speed (thinking time is greater). Table aliases in Join statements make code more obfuscated. In my opinion. A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.Table aliases are sometimes required (such as self joins). I prefer to be consistent and always use aliases rather than to only use aliases when they are necessary.
I've also seen table names that are ridiculously long. I find that it obfuscates code much more to wade through long table names to find the columns being referenced.
Drew
Table aliases in a Join statement are ALMOST never necessary. Only in the case of self-joins and a few other, very limited instances.
And this is where people will have a difference of opinion. IMO, they are very useful and should be used in all queries.
October 22, 2018 at 3:01 pm
David Walker-278941 - Monday, October 22, 2018 2:39 PMdrew.allen - Monday, October 22, 2018 9:43 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable aliases (in Join statements) -- Just Say No!
Your coding speed is probably not limited by your typing speed (thinking time is greater). Table aliases in Join statements make code more obfuscated. In my opinion. A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.Table aliases are sometimes required (such as self joins). I prefer to be consistent and always use aliases rather than to only use aliases when they are necessary.
I've also seen table names that are ridiculously long. I find that it obfuscates code much more to wade through long table names to find the columns being referenced.
Drew
Table aliases in a Join statement are ALMOST never necessary. Only in the case of self-joins and a few other, very limited instances.
Table aliases are ALWAYS acceptable. In production code, I prefer to go with ALWAYS rather than "ALMOST".
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 22, 2018 at 3:24 pm
David Walker-278941 - Monday, October 22, 2018 8:47 AMYour coding speed is probably not limited by your typing speed (thinking time is greater).
I would actually disagree with this statement for multiple reasons. First and foremost, I would not classify thinking time in coding. I think that falls under planning and/or troubleshooting. When I'm coding, there is actually very little additional thought required. Your coding speed may not be limited by your typing speed, but mine most definitely is. Aliases also reduce the potential for typos.
PS: MS Intellisense is a joke. I frequently have completed typing the names before MS Intellisense figures out what the options are. RedGate's SQL Prompt is infinitely better, but I can't get it on my machine, because I'm only a contractor.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 22, 2018 at 3:43 pm
David Walker-278941 - Monday, October 22, 2018 2:39 PMdrew.allen - Monday, October 22, 2018 9:43 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable aliases (in Join statements) -- Just Say No!
Your coding speed is probably not limited by your typing speed (thinking time is greater). Table aliases in Join statements make code more obfuscated. In my opinion. A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.Table aliases are sometimes required (such as self joins). I prefer to be consistent and always use aliases rather than to only use aliases when they are necessary.
I've also seen table names that are ridiculously long. I find that it obfuscates code much more to wade through long table names to find the columns being referenced.
Drew
Table aliases in a Join statement are ALMOST never necessary. Only in the case of self-joins and a few other, very limited instances.
Correct. They usually aren't necessary for the code to function. BUT, they are necessary for humans reading the code to function quickly when troubleshooting or making modifications in which case, it saves HUGE amounts of time. Code won't make it through a peer review with me if aliases aren't used on every column in the code in the presence of a join. I've also been known to add a table alias for code that has a large number of columns just in case I need to quickly add a join in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2018 at 8:16 pm
drew.allen - Monday, October 22, 2018 3:24 PMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMYour coding speed is probably not limited by your typing speed (thinking time is greater).I would actually disagree with this statement for multiple reasons. First and foremost, I would not classify thinking time in coding. I think that falls under planning and/or troubleshooting. When I'm coding, there is actually very little additional thought required. Your coding speed may not be limited by your typing speed, but mine most definitely is. Aliases also reduce the potential for typos.
PS: MS Intellisense is a joke. I frequently have completed typing the names before MS Intellisense figures out what the options are. RedGate's SQL Prompt is infinitely better, but I can't get it on my machine, because I'm only a contractor.
Drew
For me, I'll have to disagree with your disagreement. While I agree that there's some planning time, some of that planning time comes in the form of Proof-Of-Principle code time, then writing the real code where you really should be thinking as you're coding along with when to test, etc, But that's just me.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2018 at 8:09 am
Ken McKelvey - Monday, October 22, 2018 9:52 AMJustMarie - Monday, October 22, 2018 9:16 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable aliases (in Join statements) -- Just Say No!
Your coding speed is probably not limited by your typing speed (thinking time is greater). Table aliases in Join statements make code more obfuscated. In my opinion. A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.I strongly disagree with your aversion to table aliases. I DO agree that using the very lazy 'a', 'b', 'c' kin of alias is spectacularly unhelpful but using 'cust' for InternalSource74CustomerData is very helpful indeed. Not using AS when performing an alias is also quite lazy and makes the alias less obvious.
Oracle does not like AS alias which is why the AS tends to be omitted in SQL Server as well.
AS is an ANSI standard (I believe) which means Oracle is the one that needs to step up their game. Just because one system's flavor of SQL doesn't support the standard doesn't mean the standard is wrong.
October 23, 2018 at 8:45 am
JustMarie - Tuesday, October 23, 2018 8:09 AMKen McKelvey - Monday, October 22, 2018 9:52 AMJustMarie - Monday, October 22, 2018 9:16 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable aliases (in Join statements) -- Just Say No!
Your coding speed is probably not limited by your typing speed (thinking time is greater). Table aliases in Join statements make code more obfuscated. In my opinion. A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.I strongly disagree with your aversion to table aliases. I DO agree that using the very lazy 'a', 'b', 'c' kin of alias is spectacularly unhelpful but using 'cust' for InternalSource74CustomerData is very helpful indeed. Not using AS when performing an alias is also quite lazy and makes the alias less obvious.
Oracle does not like AS alias which is why the AS tends to be omitted in SQL Server as well.
AS is an ANSI standard (I believe) which means Oracle is the one that needs to step up their game. Just because one system's flavor of SQL doesn't support the standard doesn't mean the standard is wrong.
True enough. I don't follow ANSI/ISO standards for other reasons. 😀 I use the power in the extensions of each RDBMS I may work on rather than being limited by standards that either don't work everywhere or are limiting when used on any given RDBMS. And, yes, true portability is a myth when it comes to things like stored procedures, view, functions, triggers, use of variables, temporary structures, performance, etc, etc, etc. AND, if all RDBMS's precisely followed only ANSI/ISO standards, there would be no competition to innovate and do things better and most everything would suck because the standards are actually pretty far behind the rest of the world. :p
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2018 at 4:27 am
Jeff Moden - Monday, October 22, 2018 8:16 PMdrew.allen - Monday, October 22, 2018 3:24 PMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMYour coding speed is probably not limited by your typing speed (thinking time is greater).I would actually disagree with this statement for multiple reasons. First and foremost, I would not classify thinking time in coding. I think that falls under planning and/or troubleshooting. When I'm coding, there is actually very little additional thought required. Your coding speed may not be limited by your typing speed, but mine most definitely is. Aliases also reduce the potential for typos.
PS: MS Intellisense is a joke. I frequently have completed typing the names before MS Intellisense figures out what the options are. RedGate's SQL Prompt is infinitely better, but I can't get it on my machine, because I'm only a contractor.
Drew
For me, I'll have to disagree with your disagreement. While I agree that there's some planning time, some of that planning time comes in the form of Proof-Of-Principle code time, then writing the real code where you really should be thinking as you're coding along with when to test, etc, But that's just me.
I used to think that it didn't matter as much either. Then I started using PyCharm for my Python IDE of choice. It measures the time saved from typing speeds and using shortcuts provided by the IDE. It calculates this as time saved currently and projects time saved if you continue the course. The amount of time saved is crazy good when you actually see the differences between someone who can code/think fast versus someone who cannot.
When it comes to thinking about code, I consider that coding as well. Mostly because if I'm on the clock, that time is getting charged to the coding task. Not another task assigned to non-coding/thinking.
October 24, 2018 at 5:10 am
JustMarie - Monday, October 22, 2018 9:16 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable aliases (in Join statements) -- Just Say No!
Your coding speed is probably not limited by your typing speed (thinking time is greater). Table aliases in Join statements make code more obfuscated. In my opinion. A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.I strongly disagree with your aversion to table aliases. I DO agree that using the very lazy 'a', 'b', 'c' kin of alias is spectacularly unhelpful but using 'cust' for InternalSource74CustomerData is very helpful indeed. Not using AS when performing an alias is also quite lazy and makes the alias less obvious.
I think that the key thing is actually clarity. p.Name is drivel, Patient.Name is not - the maintenance programmer - even if it's future you - will have an easier job if a meaningful object name is used. a. c. i. are not in any sense meaningful or useful and use of this approach basically strikes me a nothing more than willful obfuscation.
On the other hand, ttdsls040500 is less meaningful to just about anyone (even if you recognise where that comes from) than <Company>SalesOrder. In that case, certainly use an alias for the sake of clarity. s. or t1 does not, in any way, provide clarity there.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 24, 2018 at 5:28 am
andrew gothard - Wednesday, October 24, 2018 5:10 AMJustMarie - Monday, October 22, 2018 9:16 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable aliases (in Join statements) -- Just Say No!
Your coding speed is probably not limited by your typing speed (thinking time is greater). Table aliases in Join statements make code more obfuscated. In my opinion. A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.I strongly disagree with your aversion to table aliases. I DO agree that using the very lazy 'a', 'b', 'c' kin of alias is spectacularly unhelpful but using 'cust' for InternalSource74CustomerData is very helpful indeed. Not using AS when performing an alias is also quite lazy and makes the alias less obvious.
I think that the key thing is actually clarity. p.Name is drivel, Patient.Name is not - the maintenance programmer - even if it's future you - will have an easier job if a meaningful object name is used. a. c. i. are not in any sense meaningful or useful and use of this approach basically strikes me a nothing more than willful obfuscation.
On the other hand, ttdsls040500 is less meaningful to just about anyone (even if you recognise where that comes from) than <Company>SalesOrder. In that case, certainly use an alias for the sake of clarity. s. or t1 does not, in any way, provide clarity there.
I don't think that matters too much; all the reader need do is glance down at the table name in the from and see that Patient is aliased to p then they are away. If you have no alias and use the entire table name as a prefix to every reference to every column the reading of the SQL becomes more difficult as it's overloaded with table names everywhere. If you have different schemas in your query do you also include the schema name to each reference to a column?
October 24, 2018 at 7:05 am
Jonathan AC Roberts - Wednesday, October 24, 2018 5:28 AMandrew gothard - Wednesday, October 24, 2018 5:10 AMJustMarie - Monday, October 22, 2018 9:16 AMDavid Walker-278941 - Monday, October 22, 2018 8:47 AMTable aliases (in Join statements) -- Just Say No!
Your coding speed is probably not limited by your typing speed (thinking time is greater). Table aliases in Join statements make code more obfuscated. In my opinion. A long Join statement with several tables is FAR easier for a human to read if NONE of the tables are aliased.I strongly disagree with your aversion to table aliases. I DO agree that using the very lazy 'a', 'b', 'c' kin of alias is spectacularly unhelpful but using 'cust' for InternalSource74CustomerData is very helpful indeed. Not using AS when performing an alias is also quite lazy and makes the alias less obvious.
I think that the key thing is actually clarity. p.Name is drivel, Patient.Name is not - the maintenance programmer - even if it's future you - will have an easier job if a meaningful object name is used. a. c. i. are not in any sense meaningful or useful and use of this approach basically strikes me a nothing more than willful obfuscation.
On the other hand, ttdsls040500 is less meaningful to just about anyone (even if you recognise where that comes from) than <Company>SalesOrder. In that case, certainly use an alias for the sake of clarity. s. or t1 does not, in any way, provide clarity there.I don't think that matters too much; all the reader need do is glance down at the table name in the from and see that Patient is aliased to p then they are away. If you have no alias and use the entire table name as a prefix to every reference to every column the reading of the SQL becomes more difficult as it's overloaded with table names everywhere. If you have different schemas in your query do you also include the schema name to each reference to a column?
That last method has actually been deprecated so, to emphasize your point, no. There will come a day when only two part naming is allowed in the SELECT list and other places where column names are cited in the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2018 at 1:08 pm
xsevensinzx - Wednesday, October 24, 2018 4:27 AMI used to think that it didn't matter as much either. Then I started using PyCharm for my Python IDE of choice. It measures the time saved from typing speeds and using shortcuts provided by the IDE. It calculates this as time saved currently and projects time saved if you continue the course. The amount of time saved is crazy good when you actually see the differences between someone who can code/think fast versus someone who cannot.When it comes to thinking about code, I consider that coding as well. Mostly because if I'm on the clock, that time is getting charged to the coding task. Not another task assigned to non-coding/thinking.
I utilize SQL Assistant - which is an intellisense tool that works across multiple IDE's and systems. Once configured - it actually reduces time spent on looking up column names, primary keys, join criteria and other shortcuts to coding. It also has the ability to format the code according to the style(s) I prefer - which has the benefit of allowing me to review someone else's code much faster.
Whether or not I utilize AS for the table alias all depends on formatting.
FROM dbo.MyTable AS mt
INNER JOIN dbo.OtherTable AS ot ON ot.key = mt.key
FROM dbo.MyTable mt
INNER JOIN dbo.OtherTable ot ON ot.key = mt.key
In the second example - for me at least - it is very clear what alias is assigned to the table regardless of whether or not AS is included and is actually easier to identify the alias and join criteria than in the former example.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 24, 2018 at 4:01 pm
Jeffrey Williams 3188 - Wednesday, October 24, 2018 1:08 PMxsevensinzx - Wednesday, October 24, 2018 4:27 AMI used to think that it didn't matter as much either. Then I started using PyCharm for my Python IDE of choice. It measures the time saved from typing speeds and using shortcuts provided by the IDE. It calculates this as time saved currently and projects time saved if you continue the course. The amount of time saved is crazy good when you actually see the differences between someone who can code/think fast versus someone who cannot.When it comes to thinking about code, I consider that coding as well. Mostly because if I'm on the clock, that time is getting charged to the coding task. Not another task assigned to non-coding/thinking.
I utilize SQL Assistant - which is an intellisense tool that works across multiple IDE's and systems. Once configured - it actually reduces time spent on looking up column names, primary keys, join criteria and other shortcuts to coding. It also has the ability to format the code according to the style(s) I prefer - which has the benefit of allowing me to review someone else's code much faster.
Whether or not I utilize AS for the table alias all depends on formatting.
FROM dbo.MyTable AS mt
INNER JOIN dbo.OtherTable AS ot ON ot.key = mt.keyFROM dbo.MyTable mt
INNER JOIN dbo.OtherTable ot ON ot.key = mt.keyIn the second example - for me at least - it is very clear what alias is assigned to the table regardless of whether or not AS is included and is actually easier to identify the alias and join criteria than in the former example.
That's how I format aliases in the FROM clause, as well, except that I also vertically align the table names. The vertical alignment (with or without the "AS") makes life quite a bit easier, especially in the face of many objects in the FROM clause.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2018 at 4:23 pm
Jeff Moden - Wednesday, October 24, 2018 4:01 PMJeffrey Williams 3188 - Wednesday, October 24, 2018 1:08 PMxsevensinzx - Wednesday, October 24, 2018 4:27 AMI used to think that it didn't matter as much either. Then I started using PyCharm for my Python IDE of choice. It measures the time saved from typing speeds and using shortcuts provided by the IDE. It calculates this as time saved currently and projects time saved if you continue the course. The amount of time saved is crazy good when you actually see the differences between someone who can code/think fast versus someone who cannot.When it comes to thinking about code, I consider that coding as well. Mostly because if I'm on the clock, that time is getting charged to the coding task. Not another task assigned to non-coding/thinking.
I utilize SQL Assistant - which is an intellisense tool that works across multiple IDE's and systems. Once configured - it actually reduces time spent on looking up column names, primary keys, join criteria and other shortcuts to coding. It also has the ability to format the code according to the style(s) I prefer - which has the benefit of allowing me to review someone else's code much faster.
Whether or not I utilize AS for the table alias all depends on formatting.
FROM dbo.MyTable AS mt
INNER JOIN dbo.OtherTable AS ot ON ot.key = mt.keyFROM dbo.MyTable mt
INNER JOIN dbo.OtherTable ot ON ot.key = mt.keyIn the second example - for me at least - it is very clear what alias is assigned to the table regardless of whether or not AS is included and is actually easier to identify the alias and join criteria than in the former example.
That's how I format aliases in the FROM clause, as well, except that I also vertically align the table names. The vertical alignment (with or without the "AS") makes life quite a bit easier, especially in the face of many objects in the FROM clause.
Ah, indentation! That's a subject in its own right.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply