T-SQL - Aliasing using “=” versus “as” [closed]

问题:

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 7 months ago.

Is there any particular reason (performance or otherwise) to use AS ahead of = when aliasing a column?

My personal preference (for readability) is to use this:

select
alias1     = somecolumn
alias2     = anothercolumn
from
tables
etc...

instead of this:

select
somecolumn as alias1
anothercolumn as alias2
from
tables
etc...

Am I missing out on any reason why I shouldn't be doing this? What are other people's preferences when it comes to formatting their columns?

回答1:

‘=’ isn't valid ANSI SQL, so you'll have difficulty should you wish to run your application on a different DBMS.

(It's when ANSI form is used but the optional ‘AS’ is omitted I find the results difficult to read, personally.)

回答2:

To put in some counterweight, I prefer using =.

If I am the consumer of the query results in some way, I find it more convenient to see what columns I as a consumer can use.

I prefer this

SELECT
      [ElementObligationID] = @MaxElementObligationID + eo.ElementObligationID
      , [ElementID] = eo.ElementID
      , [IsotopeID] = eo.IsotopeID
      , [ObligationID] = eo.ObligationID
      , [ElementWeight] = eo.ElementWeight * -1
      , [FissileWeight] = eo.FissileWeight * -1
      , [Items] = eo.Items * -1
      , [Comment] = eo.Comment
      , [AdditionalComment] = eo.AdditionalComment
      , [Aanmaak_userid] = @UserID
      , [Aanmaak_tijdstip] = GetDate()
      , [Laatste_wijziging_userid] = @UserID
      , [Laatste_wijziging_tijdstip] = GetDate()
FROM  dbo.KTM_ElementObligation eo
      INNER JOIN dbo.KTM_ElementObligationArticle eoa ON 
          eoa.ElementObligationID = eo.ElementObligationID

over this

SELECT
      @MaxElementObligationID + eo.ElementObligationID AS [ElementObligationID]
      , eo.ElementID AS [ElementID]
      , eo.IsotopeID AS [IsotopeID]
      , eo.ObligationID AS [ObligationID]
      , eo.ElementWeight * -1 AS [ElementWeight]
      , eo.FissileWeight * -1 AS [FissileWeight]
      , eo.Items * -1 AS [Items]
      , eo.Comment AS [Comment]
      , eo.AdditionalComment AS [AdditionalComment]
      , @UserID AS [Aanmaak_userid]
      , GetDate() AS [Aanmaak_tijdstip]
      , @UserID AS [Laatste_wijziging_userid]
      , GetDate() AS [Laatste_wijziging_tijdstip]
FROM  dbo.KTM_ElementObligation eo
      INNER JOIN dbo.KTM_ElementObligationArticle eoa ON 
          eoa.ElementObligationID = eo.ElementObligationID

just my 2c.

回答3:

I wouldn't use it simply as it looks far too much like equality operation. 'AS' is clear inasmuch that it's not ambiguous to me.

Its the same as not using upper case in sql, I find it harder to read.

回答4:

"=" is just plain ambiguous.

If you indent to break out each select clause...

select
    alias1     = somecolumn,
    alias2     = anothercolumn,
    result     = column1 * column2
from
    table
....


select
    somecolumn as          alias1,
    anothercolumn as       alias2,
    column1 * column2 as   result
from
    tables
     ...

回答5:

= can be confused with assignment and equality; actually, the form I really don't like is when it looks like a string (usually when spaces are involved):

somecolumn as 'alias 1'

or

'alias 1' = somecolumn

I far prefer the alternative notation:

somecolumn as [alias 1]

回答6:

I'm not as lucky as others who have posted here. The code I work with is usually written by someone else and it is rare that there are not CASE statements or other calculations, concatenations or logic that cause a single entry to span over several rows of T_SQL script.

Using a equal sign instead of 'AS' is by far easier to read. With an equal sign you know the alias name you are looking for is in the first position of the row. When 'AS' is used and the T_SQL spans multiple lines, the alias name could literally be anywhere.

It is far, far, far easier to find the 'Items' alias when equals is used than when 'AS' is used.

    SELECT
        ElementObligationID = @MaxElementObligationID + eo.ElementObligationID
      , ElementID = eo.ElementID
      , IsotopeID = eo.IsotopeID
      , ObligationID = eo.ObligationID
      , ElementWeight = eo.ElementWeight * -1
      , FissileWeight = eo.FissileWeight * -1
      , Items = CASE WHEN eo.Items < 0 THEN eo.Items * -1
                     WHEN eo.Items > 0 THEN eo.Items
                     ELSE 0 END
      , Comment = eo.Comment
      , AdditionalComment = eo.AdditionalComment
      , Aanmaak_userid = @UserID
      , Aanmaak_tijdstip = GetDate()
      , Laatste_wijziging_userid = @UserID
      , Laatste_wijziging_tijdstip = GetDate()
FROM  dbo.KTM_ElementObligation eo
      INNER JOIN dbo.KTM_ElementObligationArticle eoa ON 
          eoa.ElementObligationID = eo.ElementObligationID

Now imagine having more than 5 times the amount of code that is here and needing to find the 'Items' alias.

SELECT
      @MaxElementObligationID + eo.ElementObligationID AS ElementObligationID
      , eo.ElementID AS ElementID
      , eo.IsotopeID AS IsotopeID
      , eo.ObligationID AS ObligationID
      , eo.ElementWeight * -1 AS ElementWeight
      , eo.FissileWeight * -1 AS FissileWeight
      , CASE WHEN eo.Items < 0 THEN eo.Items * -1
             WHEN eo.Items > 0 THEN eo.Items
             ELSE 0 END AS Items
      , eo.Comment AS Comment
      , eo.AdditionalComment AS AdditionalComment
      , @UserID AS Aanmaak_userid
      , GetDate() AS Aanmaak_tijdstip
      , @UserID AS Laatste_wijziging_userid
      , GetDate() AS Laatste_wijziging_tijdstip
FROM  dbo.KTM_ElementObligation eo
      INNER JOIN dbo.KTM_ElementObligationArticle eoa ON 
          eoa.ElementObligationID = eo.ElementObligationID

'AS' vs '=' is not a capricious and arbitrary preference. I am not exaggerating when I say there have been times when it would take several minutes to find the alias name I am looking for because the author of the script I am now in charge of maintaining did not use the equals sign with their alias. I cannot think of a bigger waste of time, money and resources than paying an IT professional to look for alias names in code!! There is a right and wrong answer if you care about maintainability, readability, and efficiency. Your job is to provide business value, not to spend your day looking for Waldo!

回答7:

The postfix alias form (with or without the "AS") is consistent between column and table aliases. Personally, I'd like an option to enforce the use of "AS", and then you wouldn't have the situation:

select
    columnA,
    columnB
    columnC
from
    table

producing a result set with two columns instead of the expected 3.

I'd also say that with the prefix "=" form, it can make it more difficult to read if you're mixing obtaining a result set and variable assignment:

select
    cA = columnA,
    @cB = columnB,
    cC = columnC
from
    table

回答8:

The three ways I know of to alias:

1. TableColumn AS MyAlias
2. TableColumn MyAlias
3. MyAlias = TableColumn

Re: 1), I prefer this as it is the most self-documenting code (IMO), and it lets me search for AS if I need to find aliases..

Re: 2), This is my second choice, but without the AS, I am never sure whether this is a cut-and-paste error or not, especially in long, badly-formatted queries.

Re: 3), I don't like this because a) it looks like an assignment, and b) it blends in too much with ON clauses and CASE statements

So, my vote is to use the AS keyword for your aliases.

回答9:

I prefer using AS since = is used in the where statement, and can be confusing in a long query.

回答10:

I prefer using neither of those. I just give the name of the column without any keyword in between

SELECT MAX(price_column) maximumprice FROM prices

回答11:

Column aliases declared by "=" syntax are deprecated in SQL Server 2008 and not supported in the next version. See MSDN article.

回答12:

While I have a preference for using AS, the really key thing here is to have a corporate standard and to follow it. If more of your people use AS than = then everyone should use it. Coding standards are what makes it easier to maintain code not the particular standard you pick. If everyone uses the same thing, then your eye gets used to picking it out.

回答13:

I like the

SELECT
 column1 = table.column1
 ,column2 = table.colum2
FROM table

I find AS not as easily noticable compared to a = sign (I can spot = quicker than AS)

Also when one just does SELECT column alias, sometimes it's confusing to know which one is which :)

回答14:

You don't have to use either

Drop the AS and use

SELECT originalname alias
FROM
   tablename

回答15:

Since I write SQL for several different relational database management systems, I prefer to use a syntax which works on all of them, which normally means writing ANSI compatible SQL. My normal formatting preference is:

SELECT S.name AS SchemaName, O.name AS ObjectName, C.column_id AS ColumnId, C.name AS ColumnName FROM sys.schemas AS S INNER JOIN sys.objects AS O ON S.schema_id = O.schema_id INNER JOIN sys.columns AS C ON O.object_id = C.object_id ORDER BY S.name ASC, O.name ASC, C.column_id ASC;

As an alternative formatting of the above, the following makes it easier to see the column alias names:

SELECT S.name AS SchemaName, O.name AS ObjectName, C.column_id AS ColumnId, C.name AS ColumnName FROM sys.schemas AS S INNER JOIN sys.objects AS O ON S.schema_id = O.schema_id INNER JOIN sys.columns AS C ON O.object_id = C.object_id ORDER BY S.name ASC, O.name ASC, C.column_id ASC;

回答16:

**even i prefer using 'as' instead of '=' . '=' makes confusion in code.

e.g :

 column as alias1