InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 02:57:28 GMT20http://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxI like questions from which I learnWed, 21 Mar 2012 11:23:52 GMTIgorMihttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxNow this is a good question....:-DFrom 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.Tue, 22 Nov 2011 01:18:09 GMTterrykzncshttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxGood question.Wed, 22 Dec 2010 04:07:58 GMTjaganmohan.raohttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspx[quote][b]saranya.megalai (5/14/2010)[/b][hr]if flag is alias then can we use next statement like select [flag='y'].some_data from [where]?[/quote]actually:Select [anything].some_data from [where] [anything]Wed, 14 Jul 2010 10:34:07 GMTforjonathanwilsonhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxYes, u can use like thatSelect [flag ='y'].some_data from [where] [flag ='y']Tue, 18 May 2010 00:12:49 GMTanil.sharma 8548http://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxNo 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']Mon, 17 May 2010 00:15:36 GMTADYhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxInteresting and Good QuestionThanks :)Sun, 16 May 2010 22:45:30 GMTdeepak.ahttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxGood Example JohnSun, 16 May 2010 21:47:59 GMTsharath.chalamgarihttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspx[quote][b]Oleg Netchaev (5/14/2010)[/b][hr][quote][b]CirquedeSQLeil (5/14/2010)[/b][hr][quote][b]john.arnott (5/14/2010)[/b][hr]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).[code="sql"]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][/code][/quote]How many people would be fooled by that chicanery?[/quote]Cool, I just learned a new word, thank you Jason ([b]chicanery[/b] - the use of clever but tricky talk or action to deceive or evade someone).Oleg[/quote]Woot :w00t:Fri, 14 May 2010 13:24:01 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspx[quote][b]Oleg Netchaev (5/14/2010)[/b][hr]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:[code="plain"]Msg 8421, Level 20, State 1, Line 1Abuse of the database engine encountered. The connection has been forcibly closed by the server.[/code]Oleg[/quote]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?Fri, 14 May 2010 12:43:57 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspx[quote][b]CirquedeSQLeil (5/14/2010)[/b][hr][quote][b]john.arnott (5/14/2010)[/b][hr]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).[code="sql"]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][/code][/quote]How many people would be fooled by that chicanery?[/quote]Cool, I just learned a new word, thank you Jason ([b]chicanery[/b] - the use of clever but tricky talk or action to deceive or evade someone).OlegFri, 14 May 2010 12:04:35 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxYou're welcome.Fri, 14 May 2010 11:15:03 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspx[quote][b]CirquedeSQLeil (5/14/2010)[/b][hr]When using the AS to alias a Table, it makes it more clear to anybody coming after you to read what your intent was.[/quote]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.Fri, 14 May 2010 11:11:52 GMTjlennartzhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspx[quote][b]john.arnott (5/14/2010)[/b][hr]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).[code="sql"]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][/code][/quote]How many people would be fooled by that chicanery?Fri, 14 May 2010 11:11:42 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxThis 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).[code="sql"]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][/code]Fri, 14 May 2010 11:07:06 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxWhen using the AS to alias a Table, it makes it more clear to anybody coming after you to read what your intent was.Fri, 14 May 2010 11:05:48 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxSo if you can alias without use of the AS clause why have it?Fri, 14 May 2010 11:03:43 GMTjlennartzhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxVery interesting question! The alias name puzzled me for a while.Fri, 14 May 2010 11:02:08 GMTAmolNaikhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxThanks for the question. Very creative, I would never have thought to write a question like this.Fri, 14 May 2010 09:54:23 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxI think this was a good question, but the title "Careful with table name alias" sort of gave everything away.Fri, 14 May 2010 09:45:38 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxNice 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:[code="plain"]Msg 8421, Level 20, State 1, Line 1Abuse of the database engine encountered. The connection has been forcibly closed by the server.[/code]OlegFri, 14 May 2010 09:06:39 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxAside 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,webrunnerFri, 14 May 2010 08:51:28 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxThe correct is 'Careful with ' not 'careful with 'Fri, 14 May 2010 08:29:42 GMTGeraldo Dominguez-450858http://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspx[quote][b]hmi (5/14/2010)[/b][hr]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' ][/quote]I've seen stranger things.;-)Fri, 14 May 2010 08:15:57 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspx[quote][b]Richard M. (5/14/2010)[/b][hr]Nice one.... also good to use as an interview question :-D[/quote]Good point. Adding to the list of interview questions!Fri, 14 May 2010 07:49:04 GMTdunnjoehttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxI 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!ChadFri, 14 May 2010 07:46:10 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxNice one.... also good to use as an interview question :-DFri, 14 May 2010 07:32:37 GMTRichard M.http://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxWhen I actually realized that [quote][flag ='y' ][/quote] 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.Fri, 14 May 2010 07:05:59 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxThere 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.[code]--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[/code]Fri, 14 May 2010 06:50:41 GMTnycdotnethttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxCreative 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' ]Fri, 14 May 2010 06:46:59 GMThmihttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspx[quote][b]saranya.megalai (5/14/2010)[/b][hr]if flag is alias then can we use next statement like select [flag='y'].some_data from [where]?[/quote]Yes it would look like:[code="plain"]Select [flag ='y' ].some_data from [where] [flag ='y' ][/code]Fri, 14 May 2010 06:04:43 GMTDaveD-128295http://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxInteresting question, it was the alias that almost caught me out. Have seen plenty of attributes named as reserved words in the past which has caused a few issues unless squared brackets were used. Thinking back I had an interview question once where there was a table called ORDER and the question was what would happen.Fri, 14 May 2010 05:04:46 GMTRichardDouglashttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxReally its a good question about square brackets.we can use square brackets efficiently at any time.Fri, 14 May 2010 04:56:24 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxReally interesting and good question. :Wow:Fri, 14 May 2010 04:27:34 GMTKari Sureshhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspx[quote][b]saranya.megalai (5/14/2010)[/b][hr]if flag is alias then can we use next statement like select [flag='y'].some_data from [where]?[/quote]No :-)I haven't tested it, but the following should work:[code="sql"]select [flag='y'].some_data from [where] [flag='y'][/code]Fri, 14 May 2010 03:10:38 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxif flag is alias then can we use next statement like select [flag='y'].some_data from [where]?Fri, 14 May 2010 02:58:57 GMTsaranya.megalaihttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxI learnt another thing, thanks to the Question of the day. :-)Fri, 14 May 2010 02:48:20 GMTKratoshttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxReally good question, create confusion in mind for the "=" sign used in alias.Fri, 14 May 2010 01:11:56 GMTkeyur.patelhttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxfine, but correct is 'Careful with '(no rtrim and lower used)Fri, 14 May 2010 00:45:48 GMTTh. Fuchshttp://www.sqlservercentral.com/Forums/Topic921732-1557-1.aspxvery interesting, this table alias in select clause got me puzzled for a moment. :)Fri, 14 May 2010 00:04:45 GMTmuhammad.mazhar