Careful with table name alias

  • 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]

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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

  • Good Example John

  • Interesting and Good Question

    Thanks 🙂

  • 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']

  • Yes, u can use like that

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

  • 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]

  • Good question.

    Regards,
    Jagan.

  • 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.

  • I like questions from which I learn

    Igor Micev,My blog: www.igormicev.com

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

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