TSQL syntax - Is aliasing with a quoted prefix depricated?

Question

Can anyone tell me if writing a query in the following tsql syntax is either (1) currently -- or going to be soon -- deprecated by MSFT, or (2) in opposition to some best practice of which I'm not aware?

SELECT 
'CustName' = (SELECT Lastname + ', ' + Firstname FROM Cust WHERE CustID = O.CustID),
'ProdName' = (SELECT ProductName FROM Product WHERE ProductID = O.ProductID)
FROM Orders O

The specific question is putting the new column name all the way to the "front" or left of the line as opposed to writing the subquery and putting the new column name in square brackets after the subquery. Obviously both will work, but the DBAs reviewing my database code typically give me a WTF look when they see this, even though I tell them it's far more readable because all of your column names are on the left...

Is there something wrong with writing queries in this manner?

CLARIFICATION: The point isn't the subqueries in the SELECT statement, it's whether the syntax:

'NewColumnName' = OldColumnName

is going away anytime soon. I chose to demonstrate the question with a pair of subqueries rather than using the giant and esoteric custom function calls and case statements that are actually in the production code I'm using.

Answer 1

I typically see this syntax when using a subquery to set a variable, @MyValue = (subquery) for example. So there was a bit of a WTF from me for a minute as well. However, I see your point, and overall I can't imagine that it is something that would be not supported in the future.

Personally though I prefer a more formatted manner, and a distinct "AS" definition. I would write it something like this.

SELECT 
    (SELECT Lastname + ', ' + Firstname 
     FROM Cust 
     WHERE CustID = O.CustID
    ) AS CustName,
    (SELECT ProductName 
     FROM Product 
     WHERE ProductID = O.ProductID
    ) AS ProdName
FROM Orders O

I personally find this easier to read....but more than likely that is just me...

Answer 2

You are aliasing a column name in a strange, unfamiliar way. i had to run it to see if it was even valid.

Rather than doing what's strange and uncommon, do what people expect:

SELECT 
   (SELECT Lastname + ', ' + Firstname FROM Cust WHERE CustID = O.CustID) AS CustName,
   (SELECT ProductName FROM Product WHERE ProductID = O.ProductID) AS ProdName
FROM Orders O

Answer 3

There's absolutely nothing wrong with the

column alias = expression

syntax and it isn't deemed to be either deprecated soon or bad practice!

The main problem with the query, is that you should be using joins to connect the Orders table to the Cust and Product tables. The query looks like you are assuming there is only one Customer and one Product per order - think about what would happen if that wasn't true....

SELECT 
     'CustName' = C.Lastname + ', ' + C.Firstname,
     'ProdName' = P.ProductName 
FROM Orders O 
JOIN Cust C on C.CustID = O.CustID 
JOIN Product P on P.ProductID= O.ProductID

Answer 4

My guess is people balk at it because it's specific to MSSQL, not supported by other systems (at least, not the ones I have available to me right now). It's like listening to an unfamiliar dialect - you can see what the person is getting at, but you can't see why on Earth they would choose to speak that way.

Answer 5

According to SQL 2005 BOL here (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/c10eeaa5-3d3c-49b4-a4bd-5dc4fb190142.htm) and here: http://msdn.microsoft.com/en-us/library/ms143729(SQL.90).aspx and in the 2008 doc here: http://msdn.microsoft.com/en-us/library/ms143729.aspx (look under "Transact-SQL" features) this will be deprecated in a future release (unspecified).

However, it's a bit subtle. This deprecation warning actually only applies to the use of the quotation marks in this context, not the column-alias-first format. I.E., this will be deprecated:

'AliasName' = NewValue

However, this is still valid and even listed as a replacement for it:

AliasName = NewValue

So just take the apostrophes out and you're good.