T-SQL – Aliasing using “=” versus “as”

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:

  • How to extend the query to add 0 in the cell when no activity is performed
  • SQL - union two tables, each having a few unique columns
  • How do I run a script using a BAT file?
  • Use a LIKE clause in part of an INNER JOIN
  • SQL Server : query correlation from two tables
  • Using an Alias column in the where clause in ms-sql 2000
  • 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?

    14 Solutions collect form web for “T-SQL – Aliasing using “=” versus “as””

    ‘=’ 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.)

    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.

    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.

    = 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]
    

    “=” 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
         ...
    

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

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

    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
    

    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
    

    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.

    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 🙂

    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.

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

    e.g :

     column as alias1
    

    You don’t have to use either

    Drop the AS and use

    SELECT originalname alias
    FROM
       tablename
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.