September 2, 2015 at 5:59 am
Stewart "Arturius" Campbell (9/2/2015)
Methinks thisis going to turn into an interesting discussion.Thanks fot the question, Steve
I'm thinking that your thinking is right. 😉
I wonder if this counts as an aliasing twice or three times:
WITH cte(EmployeeID, Name) AS (
SELECT ID UselessAlias, Name
FROM dbo.Employees
)
SELECT EmployeeID SomeID, Name
FROM cte;
Inside the CTE, the ID column is being aliased as UselessAlias.
Its being returned from the CTE as EmployeeID. Would you call this an alias?
In the outer SELECT, its then being aliased as SomeID.
So, would you call this 2 or 3 aliases?
September 2, 2015 at 6:43 am
Steve,
The "correct answer" is incorrect depending upon how you define 'different'.
The 'correct answer' is incorrect depending upon how you define "different".
The 'correct answer' is incorrect depending upon how you define [different].
.....
😀
September 2, 2015 at 6:46 am
Bobby Russell (9/2/2015)
Steve,The "correct answer" is incorrect depending upon how you define 'different'.
The 'correct answer' is incorrect depending upon how you define "different".
The 'correct answer' is incorrect depending upon how you define [different].
.....
😀
It's yet another skunked question.
September 2, 2015 at 6:50 am
I answered based on remembering that blog, but personally think the complete list is 13
(=, AS, or ' ') * (no delimiter, ', ", or []) + 1 for CTE.
September 2, 2015 at 7:28 am
I am guessing that Aaron did NOT convince Steve to use = instead of as. 😉
I have to say that I disagree with Aaron on this. I find it obtuse to say alias = column. It looks like setting a value to a variable to me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 2, 2015 at 7:32 am
Sean Lange (9/2/2015)
I am guessing that Aaron did NOT convince Steve to use = instead of as. 😉I have to say that I disagree with Aaron on this. I find it obtuse to say alias = column. It looks like setting a value to a variable to me.
+1. I always use AS, and try to make sure my colleagues do, too. = looks like an assignment to a variable or a comparison; while using neither makes it less clear, and sometimes looks like they forgot a comma instead of wanting an alias.
September 2, 2015 at 7:33 am
Ed Wagner (9/2/2015)
Stewart "Arturius" Campbell (9/2/2015)
Methinks thisis going to turn into an interesting discussion.Thanks fot the question, Steve
I'm thinking that your thinking is right. 😉
I wonder if this counts as an aliasing twice or three times:
WITH cte(EmployeeID, Name) AS (SELECT ID UselessAlias, Name
FROM dbo.Employees
)
SELECT EmployeeID SomeID, Name
FROM cte;
Inside the CTE, the ID column is being aliased as UselessAlias.
Its being returned from the CTE as EmployeeID. Would you call this an alias?
In the outer SELECT, its then being aliased as SomeID.
So, would you call this 2 or 3 aliases?
The question ask about SELECT and not CTE.
September 2, 2015 at 7:45 am
handkot (9/1/2015)
I think the same thing
AS AliasAndAS 'Alias'also i can write
AS [Alias], or write CTEso there are only three ways: "=", "AS" and column
This is where I went with it also, but included '' (i.e. no column name) as a 4th option:
minutes/60.0,
I realize this isn't naming a column, but thought it might be a "trick" question.
September 2, 2015 at 8:01 am
Xavon (9/2/2015)
Sean Lange (9/2/2015)
I am guessing that Aaron did NOT convince Steve to use = instead of as. 😉I have to say that I disagree with Aaron on this. I find it obtuse to say alias = column. It looks like setting a value to a variable to me.
+1. I always use AS, and try to make sure my colleagues do, too. = looks like an assignment to a variable or a comparison; while using neither makes it less clear, and sometimes looks like they forgot a comma instead of wanting an alias.
I agree with both of you. To me, column AS myname is the easiest to read quickly and understand you are seeing a column alias.
September 2, 2015 at 8:14 am
david.gugg (9/2/2015)
Xavon (9/2/2015)
Sean Lange (9/2/2015)
I am guessing that Aaron did NOT convince Steve to use = instead of as. 😉I have to say that I disagree with Aaron on this. I find it obtuse to say alias = column. It looks like setting a value to a variable to me.
+1. I always use AS, and try to make sure my colleagues do, too. = looks like an assignment to a variable or a comparison; while using neither makes it less clear, and sometimes looks like they forgot a comma instead of wanting an alias.
I agree with both of you. To me, column AS myname is the easiest to read quickly and understand you are seeing a column alias.
Depends on the one writing the code:
SELECT
field1 = 'string',
fieldname2 = 'a longer string',
longerfieldname3 = case
when condition then result
else otherresult
end
FROM table;
versus
SELECT
'string' AS field1,
'a longer string' AS fieldname2 ,
case
when condition then result
else otherresult
end AS longerfieldname3
FROM table;
Which of the above examples makes it easier to identify the column name?
There is no solution to this one. My recommendation is to develop a coding standard for you shop, and stick with it.
September 2, 2015 at 8:18 am
Haha this is the most pedantic thing ever 🙂 Sometimes I learn a lot from these questions, but this is just abstruse trivia
I picked 3 b/c I didn't know you could use string literals... not sure why that would ever be useful though. I wonder if you could pass in a variable as the alias?
September 2, 2015 at 8:22 am
RLilj33 (9/2/2015)
david.gugg (9/2/2015)
Xavon (9/2/2015)
Sean Lange (9/2/2015)
I am guessing that Aaron did NOT convince Steve to use = instead of as. 😉I have to say that I disagree with Aaron on this. I find it obtuse to say alias = column. It looks like setting a value to a variable to me.
+1. I always use AS, and try to make sure my colleagues do, too. = looks like an assignment to a variable or a comparison; while using neither makes it less clear, and sometimes looks like they forgot a comma instead of wanting an alias.
I agree with both of you. To me, column AS myname is the easiest to read quickly and understand you are seeing a column alias.
Depends on the one writing the code:
SELECT
field1 = 'string',
fieldname2 = 'a longer string',
longerfieldname3 = case
when condition then result
else otherresult
end
FROM table;
versus
SELECT
'string' AS field1,
'a longer string' AS fieldname2 ,
case
when condition then result
else otherresult
end AS longerfieldname3
FROM table;
Which of the above examples makes it easier to identify the column name?
There is no solution to this one. My recommendation is to develop a coding standard for you shop, and stick with it.
It also depends on the one reading the code. I think the second is much easier to read and tell the column names.
September 2, 2015 at 8:47 am
The sixth one should have had brackets. That's been corrected. However, I missed a few since there could be no joiner (=/AS) in there, so it's probably more like 12. The CTE thing is interesting. Not sure I think this is an alias, as it's really a column name specified for the position, not an alias, but it is debatable.
The subquery one is also interested.
I'll award back points later. This likely isn't a great question.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 2, 2015 at 8:49 am
I do agree with the = and try to use it, precisely because complex code becomes hard to read when the name is buried far to the right of my screen. However my habit is "as", so I struggle to change and get some inconsistent code.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 2, 2015 at 9:05 am
Steve Jones - SSC Editor (9/2/2015)
I do agree with the = and try to use it, precisely because complex code becomes hard to read when the name is buried far to the right of my screen. However my habit is "as", so I struggle to change and get some inconsistent code.
LOL! And here I thought you had it all figured out 😉
We went back and forth on this with each developer doing something a little different. I, too, liked using = but found I was the only one doing so. In the end, we met as a team over the course of a year and developed a tSQL coding standard that we now adhere to. New code is consistent across developers (we will politely point out discrepancies to each other), and old code will be changed through attrition.
These standards provide us with stability. Over time I had been adopting new practices, so that my code today looks different from what I wrote a few years ago. The biggest driver of the standards is that we want to be able to compare older versions of a module against recent changes (using a text comparison tool) so we can quickly isolate the logic changes, without needing to filter through the "formatting" changes.
Viewing 15 posts - 16 through 30 (of 59 total)
You must be logged in to reply to this topic. Login to reply