Column aliases

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  September 3, 2015 at 4:04 pm

  #1824502

  Was an easy nice question. Thanks.

  Thanks.

• SQLWinther

  SSCertifiable

  Points: 6294

  More actions

  September 6, 2015 at 5:50 am

  #1824856

  Well it's still possible to write the alias in 12 different ways in SQL2014 (deprecated or not) 🙂

  SELECT object_id AS test

  FROM sys.columns AS mt;

  SELECT object_id AS 'test'

  FROM sys.columns AS mt;

  SELECT object_id AS [test]

  FROM sys.columns AS mt;

  SELECT object_id test

  FROM sys.columns AS mt;

  SELECT object_id [test]

  FROM sys.columns AS mt;

  SELECT object_id 'test'

  FROM sys.columns AS mt;

  SELECT test = object_id

  FROM sys.columns AS mt;

  SELECT 'test' = object_id

  FROM sys.columns AS mt;

  SELECT [test] = object_id

  FROM sys.columns AS mt;

  SET QUOTED_IDENTIFIER ON;

  SELECT OBJECT_ID "test"

  FROM sys.columns AS mt;

  SELECT OBJECT_ID AS "test"

  FROM sys.columns AS mt;

  SELECT "test" = OBJECT_ID

  FROM sys.columns AS mt;

  /Håkan Winther
  MCITP:Database Developer 2008
  MCTS: SQL Server 2008, Implementation and Maintenance
  MCSE: Data Platform

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  September 8, 2015 at 2:44 pm

  #1825260

  Quite a few ways.

• dm 16849

  SSC Journeyman

  Points: 94

  More actions

  September 9, 2015 at 10:51 am

  #1825478

  I agree. 3 or 6 is the correct answer. Very bad example for explanation. 😎

• laugur

  SSC Rookie

  Points: 34

  More actions

  September 10, 2015 at 9:39 am

  #1825725

  The answer should be 3, don't like the question; however, I'm not going to complain too much since you take the time to put these questions together.

• zymos

  SSCommitted

  Points: 1960

  More actions

  September 10, 2015 at 10:11 am

  #1825738

  Interesting, simple yet tricky question.

  Thanks Steve.

• stephen.long.1

  SSCrazy

  Points: 2582

  More actions

  September 11, 2015 at 7:51 am

  #1825981

  I agree with a lot of people here, the correct answer should be 3, based upon structure, not decorations:

  SELECT x AS y

  SELECT x y

  SELECT y = x

  Brackets, quotes, or double quotes around names are just variations on these three (and they make the list uglier, in my opinion; when Management Studio generates a query using brackets around every single column name, I usually select the entire mess and do search and replace to strip them out).

  I used to usually use SELECT x AS y, because the AS makes the syntax more explicit. However, after seeing Aaron's blog post a while back, I've switched to using SELECT y = x for new code, because it makes it easier to scan a list of column names. I've seen "stream of consciousness" (SELECT x, y, z, a, b, c) queries too many times, and they drive me nuts.

  I think this is a good question, not because it showed a "perfect" way to do something (or NOT to do something), but because it sparked an excellent discussion.

  The best thing is that the SQL standard allows all of these variants based upon personal preference, and doesn't force only one way upon everyone.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  September 11, 2015 at 8:04 am

  #1825988

  stephen.long.1 (9/11/2015)

  ---

  I agree with a lot of people here, the correct answer should be 3, based upon structure, not decorations:

  SELECT x AS y

  SELECT x y

  SELECT y = x

  I agree with the number, but not the list 😀

  1 and 2 in your list are the same, just with an optional [AS]

  The true third alias form is the positional alias you can use after a subquery, in a CTE, or in a view

  Positional:

  select foo.bar from ( select 1 ) as foo( bar );

  create view foo( bar ) as select 1;

  with foo( bar ) as ( select 1 ) select foo.bar from foo;

  All three:

  with foo[highlight="#ffff11"](bar)[/highlight] as (select [highlight="#ffff11"]hutch =[/highlight] starsky from (select 1 [highlight="#ffff11"]as starsky[/highlight]) as tv ) select foo.bar from foo;

  😛

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]