Careful with table name alias

Question

Careful with table name alias

Viewing 15 posts - 31 through 44 (of 44 total)

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

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

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]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

john.arnott (5/14/2010)
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]

How many people would be fooled by that chicanery?

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

jlennartz SSCommitted Points: 1574 More actions · Answer 3

CirquedeSQLeil (5/14/2010)
When using the AS to alias a Table, it makes it more clear to anybody coming after you to read what your intent was.

Thank you Jason.

That is why I assumed it wasn't aliasing because there was no AS.

This is why I do QotD, I alway learn something.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

You're welcome.

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

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 5

CirquedeSQLeil (5/14/2010)
john.arnott (5/14/2010)
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]
How many people would be fooled by that chicanery?

Cool, I just learned a new word, thank you Jason (chicanery - the use of clever but tricky talk or action to deceive or evade someone).

Oleg

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Oleg Netchaev (5/14/2010)
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

I agree 100% with that. Allowing the sort of incredibley illegible nonsense that can arise out of this is one of SQLS's faults.

Although sev 20 maybe is a bit OTT - just kill the query rather than the connection, maybe?

Tom

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Oleg Netchaev (5/14/2010)
CirquedeSQLeil (5/14/2010)
john.arnott (5/14/2010)
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]
How many people would be fooled by that chicanery?
Cool, I just learned a new word, thank you Jason (chicanery - the use of clever but tricky talk or action to deceive or evade someone).
Oleg

Woot :w00t:

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

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 8

sharath.chalamgari

SSCertifiable

Points: 5680

May 16, 2010 at 9:47 pm

#1168100

Good Example John

deepak.a SSCertifiable Points: 5330 More actions · Answer 9

deepak.a

SSCertifiable

Points: 5330

May 16, 2010 at 10:45 pm

#1168108

Interesting and Good Question

Thanks 🙂

ADY SSC Journeyman Points: 76 More actions · Answer 10

No You can't use it as [flag='y'].some_data, because you not give the table alias and fetching the column with reference to table alias, it can be work as select [flag='y'].some_data from [where] [flag='y']

anil.sharma 8548 Newbie Points: 2 More actions · Answer 11

Yes, u can use like that

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

forjonathanwilson SSC Enthusiast Points: 139 More actions · Answer 12

saranya.megalai (5/14/2010)
if flag is alias then can we use next statement like
select [flag='y'].some_data from [where]?

actually:

Select [anything].some_data from [where] [anything]

jaganmohan.rao SSC Eights! Points: 976 More actions · Answer 13

jaganmohan.rao

SSC Eights!

Points: 976

December 22, 2010 at 4:07 am

#1265420

Good question.

Regards,
Jagan.

terrykzncs Ten Centuries Points: 1324 More actions · Answer 14

Now this is a good question....:-D

From now on I will be more careful when using [] in my code...:w00t:

Thanks for something practical and not another trick question that hides something.

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 15

I like questions from which I learn

Igor Micev,My blog: www.igormicev.com