SQL Clone
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
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3512 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 Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64367 Visits: 18570
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! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)

Group: General Forum Members
Points: 936 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 Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64367 Visits: 18570
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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2175 Visits: 1817
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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25340 Visits: 12488
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 Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64367 Visits: 18570
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
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1952 Visits: 798
Good Example John
deepak.a
deepak.a
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: 1296 Visits: 863
Interesting and Good Question
Thanks Smile
ADY
ADY
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 25
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