February 2, 2012 at 5:39 am
Gazareth (2/2/2012)
Gary Varga (2/2/2012)
I totally understand that. Following reading the article, if I was part of designing the SQL as a new language then with the old 20:20 hindsight I would have suggested that the AS keyword was used but that the alias came first i.e. SELECT x AS 1 instead of SELECT 1 AS x. Obviously this change could never happen and nor should it. I would prefer another keyword\operator than = but cannot think of one.
That just makes me think "computed column" 🙂
How about AKA, as in: SELECT x AKA 1 ?
Yep. Good point. Can see that too.
AKA would work...never get past a committee though!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 2, 2012 at 5:48 am
Gary Varga (2/2/2012)
AKA would work...never get past a committee though!!!
Let's spike their punch! 😀
February 2, 2012 at 8:04 am
Someone above said that overloading the "=" sign to do the alias would be even more confusing... I'd argue that it's not, it's a reuse of the Assignment, but instead of assigning to a variable it's assigning to a column name (alias).
As to the editorial, I agree that learning something new helps. Favoring the Column = Value syntax over the Value AS Column or Value Column syntax does make the code more readable. Since columns have a limit on length, but values of that column do not have a maximum length (well, 64K is really really big for a single column) it makes sense in debugging to say "Column xyz" has an issue, then you simply scroll to that column and there is your entire statement without having to do extensive searching through the code to find it.
February 2, 2012 at 8:57 am
Sybase:
SELECT column_list
FROM table1, table2
WHERE table1.key_column1 *= table2.key_column1 AND
table1.key_column2 *= table2.key_column2
Oracle:
SELECT column_list
FROM table1, table2
WHERE table1.key_column1 = table2.key_column1(+) AND
table1.key_column2 = table2.key_column2(+);
SQL Server and MySQL:
SELECT column_list
FROM table1
LEFT JOIN table2
ON (table1.key_column1 = table2.key_column1 AND
table1.key_column2 = table2.key_column2)
The point is here that they are trying to move us away from the different flavors of syntax to a point where you can move SQL between the different platforms and not only have the SQL work, but not have to relearn their flavor of SQL all over again. What do they call that? Oh yeah, "standardization".:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 2, 2012 at 9:18 am
Semicolons will at some point be required in T-SQL? Really? I've seen it used in some cases, but am completely uncertain as to when they are to be used. I write C# code, and before that C and some C++. It's easy to know when to use semicolons in those languanges. So, when do you use semicolons in T-SQL?
Kindest Regards, Rod Connect with me on LinkedIn.
February 2, 2012 at 9:20 am
I prefer AS for aliasing because I think there is a semantic difference between = and AS. = implies an assignment--copying a value into a new place, while AS implies an alias, show me <col> AS <alias>. LastName = m.surname looks like you should be able to manipulate LastName and m.surname independently.
I also agree with the overloading arguement to some extent, adding a Non-Scalar meaning for = makes it more mentally taxing to process the code because assignments and aliases are not the same thing but can be done in the same places (unlike the comparison = which requires a keyword nearby).
Yes, AS is overloaded in the CAST() expression but that is an argument for changing CAST, not for adding a new meaning to =.
--
JimFive
February 2, 2012 at 9:23 am
Rod at work (2/2/2012)
Semicolons will at some point be required in T-SQL? Really? I've seen it used in some cases, but am completely uncertain as to when they are to be used. I write C# code, and before that C and some C++. It's easy to know when to use semicolons in those languanges. So, when do you use semicolons in T-SQL?
My understanding is that they are currently an optional delimiter but can be used at the end of each statement just like the C family of languages where they are mandatory.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 2, 2012 at 9:24 am
TravisDBA (2/2/2012)The point is here that they are trying to move us away from the different flavors of syntax to a point where you can move SQL between the different platforms and not only have the SQL work, but not have to relearn their flavor of SQL all over again. What do they call that? Oh yeah, "standardization".:-D
Nice but try to find a standard for simple date time functions like:
MS SQL: getdate()
Oracle: CURRENT_DATE
or
MS SQL: getdate() + 10
Oracle: TIMESTAMPADD(SQL_TSI_DAY, 10, CURRENT_DATE)
Even simple things like Select top versus rownum make code less portable:
MS SQL: Select Top 100 * from whatever
Oracle: Select * from whatever where rownum < 100
February 2, 2012 at 9:29 am
Let's face it, until there is a BIG effort for SQL standardisation there will not be portable SQL. Of course the "our syntax is better so lets all standardise on that" argument will be rolled out by...all of them!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 2, 2012 at 9:34 am
Gary Varga (2/2/2012)
It was when I looked at the side-by-side stored procedures example that it became clear. Literally!!!?
isn't that just down to formatting though? the fact that the article has coloured the ColumnNames in green, which wouldn't be the case normally, just highlights it even more.
we could write it like this:
select
DATEADD(day,15,getdate())AS myTime
,DATEADD(day,13,getdate())AS myExtraTime
and it would be just as readable with the added bonus of having the AS as a different colour
February 2, 2012 at 9:42 am
paul s-306273 (2/2/2012)
I've used the SELECT 1 x; format for years. (Oracle background).I've never found it confusing - I don't now discard that format just because somebody else finds it confusing.
Matter of taste really.
ditto, but that's being deprecatated. Knowing the pace of SQL releases, I suspect this will break in 2014/2015.
February 2, 2012 at 9:43 am
Gary Varga (2/2/2012)
It was when I looked at the side-by-side stored procedures example that it became clear. Literally!!!I shall be using the = syntax moving forward. My reasoning? The intention is clearer to the reader. Not only of the alias but of the structure being returned. Removing ambiguity from code is the key to delivering a maintainable system.
That's what I thought once I used it with a few functions in the column list. Much clearer to read.
February 2, 2012 at 9:46 am
Gary Varga (2/2/2012)
Rod at work (2/2/2012)
Semicolons will at some point be required in T-SQL? Really? I've seen it used in some cases, but am completely uncertain as to when they are to be used. I write C# code, and before that C and some C++. It's easy to know when to use semicolons in those languanges. So, when do you use semicolons in T-SQL?My understanding is that they are currently an optional delimiter but can be used at the end of each statement just like the C family of languages where they are mandatory.
Correct, but the SQL team has stated as the language matures and they add more features, semicolons will be required at some point. Not sure when. I would guess 2-3 versions from now.
February 2, 2012 at 9:49 am
davidandrews13 (2/2/2012)
isn't that just down to formatting though? the fact that the article has coloured the ColumnNames in green, which wouldn't be the case normally, just highlights it even more.
Two issues there. First, width. As you get more complex, it's not necessarily fitting easily on a screen. second, scanning for a column, based on a result, I think it's easier to see.
select
TotalSales = sum( itemquantity * unitprice) - salestax
, Profit = sum( itemquantity * unit price) - salestax - sum( itemcost * itemquantity)
- shippingcost - othercost
, itemquantity
, customername = (firstname + ' ' + substring( middlename, 1, 1) + ' ' + lastname + ' ' + suffix
from ...
February 2, 2012 at 9:52 am
paul.knibbs (2/2/2012)
As a set in my ways C programmer, I find it irritating enough that SQL overloads the = operator to mean both assignment and a test of equality (I mean, seriously, was the designer of SQL an old BASIC programmer? :-P), without adding yet another different meaning to it!
Ditto. I write in C#/C++ half the time and SQL the other half. I often find myself using == when writing TSQL out of habit. That is one of the things I dislike about VB as well.
I used to use the = instead os AS in select statements years back but made myself switch to AS for consistency.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 15 posts - 16 through 30 (of 92 total)
You must be logged in to reply to this topic. Login to reply