Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Careful with table name alias


Careful with table name alias

Author
Message
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1474 Visits: 3059
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
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21143 Visits: 18259
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

jlennartz
jlennartz
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 1197
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
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21143 Visits: 18259
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

Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1809
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
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10777 Visits: 12019
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
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21143 Visits: 18259
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

sharath.chalamgari
sharath.chalamgari
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 798
Good Example John
deepak.a
deepak.a
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 863
Interesting and Good Question
Thanks Smile
ADY
ADY
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 24
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']
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search