Careful with table name alias

Question

Post reply

Careful with table name alias

Viewing 15 posts - 16 through 30 (of 44 total)

You must be logged in to reply to this topic. Login to reply

hmi SSC Enthusiast Points: 116 More actions · Answer 1

Creative statement good for braintraining but with a high theoretical fineness.

Not any SQL developer with some common sense would name a table [where] nor would invent the alias [flag ='y' ]

nycdotnet Right there with Babe Points: 799 More actions · Answer 2

There is nothing special about flag='y'... it's text. It could be anything He's just aliasing the table.

The point is that by using square brackets you can alias something to even something crazy that looks like an expression.

--These are all the same...

Select * from [where] [flag ='y' ]

Select * from [where] [Beer]

Select [flag ='y' ].* from [where] [flag ='y' ]

Select [Beer].* from [where] [Beer]

Select table1.some_data from [where] table1

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

When I actually realized that

[flag ='y' ]

was an alias....I actually laughed. This is a situation so unlikely that it is humorous, but it teaches a good lesson. Great question for a Friday. Thanks.

Richard M. SSCertifiable Points: 7401 More actions · Answer 4

Nice one.... also good to use as an interview question 😀

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Chad Crawford SSChampion Points: 11610 More actions · Answer 5

I love it! This ranks right up there with the Middle Join QOD, which is one of my all time favorites. I think someone actually tried to write a SQL Statement once that read as a poem using carefully named tables and a ton of table and column aliases, but I don't remember where I saw that now.

This is great, nicely done Roshan!

Chad

dunnjoe SSCrazy Points: 2180 More actions · Answer 6

Richard M. (5/14/2010)
Nice one.... also good to use as an interview question 😀

Good point. Adding to the list of interview questions!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

hmi (5/14/2010)
Creative statement good for braintraining but with a high theoretical fineness.
Not any SQL developer with some common sense would name a table [where] nor would invent the alias [flag ='y' ]

I've seen stranger things.;-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Geraldo Dominguez-450858 SSC Rookie Points: 26 More actions · Answer 8

Geraldo Dominguez-450858

SSC Rookie

Points: 26

May 14, 2010 at 8:29 am

#1167574

The correct is 'Careful with ' not 'careful with '

webrunner SSC-Dedicated Points: 31720 More actions · Answer 9

Aside from the minor case correction of 'Careful with' that several astute observers noticed, this is a great question. It's even possible it will help in diagnosing something crazy like that showing up in legacy code. I know I have lots of room for improvement, but I would never actually write aliases like that or use [where] as a table name. But it is important to know that with the brackets, such statements would work.

And I got the question right, to boot!

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 10

Nice question, thank you. It would be nice though if the database engine team would limit some of the square brackets allowances and throw sev 20 error. Something like:

Msg 8421, Level 20, State 1, Line 1

Abuse of the database engine encountered.

The connection has been forcibly closed by the server.

Oleg

UMG Developer SSChampion Points: 13482 More actions · Answer 11

I think this was a good question, but the title "Careful with table name alias" sort of gave everything away.

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 12

Thanks for the question. Very creative, I would never have thought to write a question like this.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 13

Very interesting question! The alias name puzzled me for a while.

Amol Naik

jlennartz SSCommitted Points: 1574 More actions · Answer 14

So if you can alias without use of the AS clause why have it?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 15

When using the AS to alias a Table, it makes it more clear to anybody coming after you to read what your intent was.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events