Careful with table name alias

Question

Careful with table name alias

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

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

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

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 2

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 3

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 4

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 5

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: 281344 More actions · Answer 6

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 7

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: 32073 More actions · Answer 8

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 9

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 10

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: 4846 More actions · Answer 11

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 12

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

Amol Naik

jlennartz SSCommitted Points: 1574 More actions · Answer 13

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 14

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

john.arnott SSChampion Points: 11882 More actions · Answer 15

This was a good way to point out the risk of using reserved words within brackets as a user-defined object. While I would suppose few people would want to name a table "WHERE" as in the QOD, some other words like "Name" may be more attractive.

Alternatively, the use of brackets does offer the opportunity to have some fun...... (yes, this works).

create table [where]([Select from there] varchar(50))

insert into [where] values ('Careful with this')

select [from there].[Select from there] [from there] from [where] [from there]