InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 15:15:44 GMT20http://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspx[quote][b]Hugo Kornelis (10/12/2010)[/b][hr][quote][b]lukus_g (10/12/2010)[/b][hr]so, does the transaction actually run and get rolled back?[/quote]No.The author of the question uses a long explanation to expand on how usefull the possibility to use non-standard characters in identifiers is. (It can be - but only in some cases, it can also be very confusing and introduce errors, so take care. And if you care for ANSI standards and portable code, consider using "double quotes" instead of [bracktes] to delimit identifiers). Unfortunately, he completely forgot to explain the actual question.First, focus on the first line:[size="3"][font="Courier New"]create table [VarArray[]](i int)[/font][/size]The first [ starts a delimited identifier. That means that from there on, every character is considered part of the table name, and al characters are allowed. With one exception. The ] character will be considered the end of the delimited identifier. So what if we want to use a ] character as part of the identifier? The answer to that question is to escape it. In a [delimited identifier], you can escape the ] character by doubling it, so you get ]]. This can be very confusing. A human reader would interpret an identifier such as [identifier]]] as being terribly unmatched, but the SQL Server parser replaces the first two closing brackets with a single ] symbol as part of the identifier, and interprets the third closing identifier as the brackets that signifies the end of the delimited identifier.Carlo played a trick on us by adding a [ sign and a double ] to the identifier. We tend to pair up the identifiers and conclude that the last ] ends the identifier, and (i int) is the column list. SQL Server simply treats the [ as one character in the identifier, then treats the ]] as one character in the identifier, and then also treats (i int) as part of the identifier.If you execute ONLY the line[size="3"][font="Courier New"]create table [VarArray[]](i int)[/font][/size]you will get an error message:[size="2"][font="Courier New"]Msg 105, Level 15, State 1, Line 1Unclosed quotation mark after the character string 'VarArray[](i int)'.[/font][/size]If you add a third ] right after the second and before (i int), the code will succeed and you will create a table with name VarArray[] and one integer column named i.Since the first line does not end the delimited identifier, the end of line character and the next line is considered part of the identifier as well. That even includes the GO lines - SSMS has its own parser that also sees that the delimiter has not ended, so it will not interpret these specific GO lines as batch seperators, but will include them in the batch. All lines in the code are sent as one single batch.This long delimited identifier finally ends on this line:[size="3"][font="Courier New"]while 1=1)][/font][/size]The extra ] at the end is easily missed by humans, but the SQL Server parser does recognise it, and interprets it as the end of the identifier. The lines that come after this line look like a single statement with two syntax errors (both enclosing in parentheses and the statement itself would violate syntax rules if used in a real WHILE loop), but are actually interpreted as a column list.So, what this code really does is - it creates a table with this hideous name:[code="plain"]VarArray[](i int)GObegin tran insert into VarArray(i) select 1rollbackGOwhile(1=1)[/code]and with one single column, named print_i and typed as integer.[/quote]Thank you for your explanation, I couldn't understand the original explanation.Fri, 12 Aug 2011 00:16:50 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxA tricky question indeed.And very good explaination by Hugo. Thanks Hugo. Learnt about delimiters.Tue, 01 Feb 2011 01:01:39 GMTtejaswini.patilhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxThis question fooled me :(Thanks Hugo for the great walkthrough, I learned something new :)CheersMon, 22 Nov 2010 14:34:49 GMTBrigadurhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxOkay, finally got around to answering questions, and hit this one.I got stuck on the title and started looking to see if something new had been added to SQL Server 2008 R2.Usually I'll support individuals that post questionable QotD; but this one, once I saw the explaination, I knew I had been tricked. Of course you would get a column with a totally worthless column name.This is what I consider a trick question.Wed, 03 Nov 2010 18:33:47 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspx[quote][b]natalie.ignatieva (10/27/2010)[/b][hr]As I understand the point of this question is to make us develop skills in typing scripts from the image and don't make errors, right? I failed. Am I a bad SQL developer after that?[/quote]No. I fully agree with you that this question is totally useless.Wed, 27 Oct 2010 16:30:35 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspx[quote]The color coding is actually correct. The first [ character starts a table name that only ends at the final ], so everything in between them is part of the table name. Black is the default color used for (a.o.) table names.Please read the rest of the discussion for a more comprehensive explanation (and for many people agreeing with our dislike of the question)[/quote]Yes, the color is correct, but I didn't have a chance to copy the script, so I had to type it. I missed ] in the line with 'while' because you don't expect developers to create tables with such weird names and thought it was typo or something. but even if I didn't miss it and executed the statement, I would see that author just wanted to create a table with such weird name. My point is that if you have your app generating these kind of statements and you try to understand what actually is wrong (yes, author wanted to look us for some errors in the script as if it's supposed actually run infinite loop or begin transaction), anyway you copy it from profiler to studio, see 'BEGIN' and 'INSERT' in black and just then you look for syntax issues.As I understand the point of this question is to make us develop skills in typing scripts from the image and don't make errors, right? I failed. Am I a bad SQL developer after that?Wed, 27 Oct 2010 15:54:43 GMTnatalie.ignatievahttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspx[quote][b]natalie.ignatieva (10/27/2010)[/b][hr]Management studio shows me this script mostly in black meaning there is something wrong in the script even if it's completed successfully.[/quote]The color coding is actually correct. The first [ character starts a table name that only ends at the final ], so everything in between them is part of the table name. Black is the default color used for (a.o.) table names.Please read the rest of the discussion for a more comprehensive explanation (and for many people agreeing with our dislike of the question)Wed, 27 Oct 2010 15:36:27 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxManagement studio shows me this script mostly in black meaning there is something wrong in the script even if it's completed successfully. I guess even colorblind people can see it. I don't see the point of this question. I hope nobody will post these kind of 'questions' any more. It's not CS spirit.Wed, 27 Oct 2010 15:04:13 GMTnatalie.ignatievahttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxThanks for the question.Mon, 18 Oct 2010 10:18:40 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxdeletemeThu, 14 Oct 2010 22:56:34 GMTElectricHobohttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxthanks Hugo... :-)[quote][b]Hugo Kornelis (10/12/2010)[/b][hr]I got two points for answering a question that tests no useful skill whatsoever? Come on, Steve! Even one point would have been more than enough for this one!Carlo, thanks for the effort of submitting a QotD (not cynical!). But next time, please submit a question about something that actually has any real use for SQL Server professionals.I can imagine someone accidentally not matching opening and closing brackets (especially if []] is used somewhere in the bracketed text). But then also having an extraneous closing bracket in another batch, that is sent at the same time, and with code after that bracket that "just happens" to make the original code complete so that no error is thrown? The chance of that happening anytime, anywhere, is infenitely small. So noone will learn anything useful from this question.Since you obviously (based on the explanation) wanted to educate visitors of this site about the usefulness of using brackets to delimit identifiers, you should have submitted a question that did just that, instead of deliberately adding nonsensical code to ensure as little people as possible get it right. The aim of this site should not be to get as many people as possible to fail, but to show as many people as possible the great stuff SQL Server can do.[/quote]Wed, 13 Oct 2010 02:39:55 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspx[quote][b]Carlo Romagnano (10/13/2010)[/b][hr]In the example, unfortunately, most of syntax has been lost because of html tag.[/quote]Could you please zip that script and attach it to your next message? :-)Wed, 13 Oct 2010 01:56:40 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxObvious! In the daily work you do not need a special syntax. But, sometime my clients ask me for something special. And thanks to the delimiter I can extract data and send them without writing an application outside sqlserver.In the example, unfortunately, most of syntax has been lost because of html tag.So, I provide another simplest one. The following script build syntax to expand sp, functions or views, comment or uncomment the type of object and substitute %my_proc% with the name of object, run it and than select all the result copy and paste and execute in the query window.set nocount onselect 'exec sp_helptext ' AS [--],o.namefrom sysobjects o(readuncommitted)where o.xtype IN('','P '--: stored procedure'--,'IF'--: inline function'--,'FN'--: scalar function'--,'TF'--: table function'--,'V '--: view'--,'TR'--: trigger')and o.name like '%my_proc%'and o.name not like 'dt[_]%'order by o.Name set nocount offAt last, the 84% of users learned 3 new things: 1) Read carefully the Qotd.2) Use of delimiter.3) Only lack of fantasy is the limit.Wed, 13 Oct 2010 01:12:45 GMTCarlo Romagnanohttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxTrick question but confused with explanation & query posted in the answer because it throws an error. Thanks Hugo for explanation. I learn usage of delimiters.Tue, 12 Oct 2010 22:31:04 GMTHardy21http://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxThat was a realy trick question. Thank you Hugo for the explanation. I had to read it twice, but I finaly got it... Sure I know how a ] works now!Tue, 12 Oct 2010 21:28:07 GMTtilew-948340http://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspx[quote][b]dogramone (10/12/2010)[/b][hr]First time I've ever joined a QOTD discussion.[/quote]No, there have been far worse. At least in this one the answer was not wrong. And for irrelevance to real work - one QoTD that I set beats this one for irrelevance (and Hugo quite properly and politely pointed out its irrelevance).Tue, 12 Oct 2010 18:15:08 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxFirst time I've ever joined a QOTD discussion. In almost 10 years on the site this must be the worst QOTD yet. I quite regularly get them wrong as I am a part time user of SQL Server now days having got promoted to PM roll. I enjoy the learnings and encourage my teams to spend time on the forums and to always do the QOTD. Well this one is a total waste of time as it is a situation that will NEVER occur in a real world situation. I know that it is voluntary to submit a question and there are probalby times when only one is submitted ut I would prefer to have no QOTD that ones as bad as this.Tue, 12 Oct 2010 15:17:32 GMTdogramonehttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxDogpile on Carlo. Give him a dutch rub. ;-)Tue, 12 Oct 2010 13:36:57 GMTpjdillerhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxInteresting question with totally useless (or worse than that) explanation. Thank heavens for Hugo's clear and simple explanation!Tue, 12 Oct 2010 12:13:05 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxI think I wasted 15 min of my time today on this question learning nothing new, thanks Hugo for taking the time to explain the delimiter concepts.Tue, 12 Oct 2010 10:49:40 GMTShrinikethttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxI thought the ending ] was a typo, it just didn't make sense with it there :)Tue, 12 Oct 2010 10:41:11 GMTShrinikethttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxHugo, thank you for your explanation. I was totally confused by the square brackets.Tue, 12 Oct 2010 09:10:38 GMTEnigma475http://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxYou are right. The parser in SSMS 2008 is a little different from 2005. The table name has the actual insert statement in it and then some. I wasted 20-30 minutes trying to get it to work like the posted answer.Tue, 12 Oct 2010 08:04:56 GMTjcarsleyhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxI just think it's hilareous what the SQL 2008 intellisense looks like (newline characters and all) when you perform a select against this 'table':select dbo.[VarArray[]](i int)GObegin tran insert into VarArray(i) select 1; print 1;rollbackGOwhile(1=1)].print_i from dbo.[VarArray[]](i int)GObegin tran insert into VarArray(i) select 1; print 1;rollbackGOwhile(1=1)]What a strange question... My first impression was that this was done by mistake when trying to use VARARRAY from Oracle (or someone was testing to see if VARARRAY was valid in that version of SQL Server).Tue, 12 Oct 2010 07:59:32 GMTfanningmhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxCarlo, nice try with this Question of the Day, but I really learned more with Hugo's explanation......Tue, 12 Oct 2010 07:55:34 GMTRichard M.http://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxI felt it was worth two points , sure stumped me.Make you think outside the box.Thank youTue, 12 Oct 2010 07:31:42 GMTjwbart06http://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxThis question completely wasted my time. It serves little use to anybody.Tue, 12 Oct 2010 07:16:11 GMTjcarsleyhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxInteresting question. Good follow on discussion.Tue, 12 Oct 2010 06:54:31 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspx[quote][b]lukus_g (10/12/2010)[/b][hr]Hugo Kornelis, you are a ninja![/quote]True! Well, except for the trendy clothes, the superior stealth, the extreme strength and endurance, the ability to kill without getting killed or caught, and the generic sheer awesomeness of course.:-DBut thanks anyway!:-)Tue, 12 Oct 2010 04:16:42 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxSplendid work!!Tue, 12 Oct 2010 04:10:17 GMTfree_mascothttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxWhat a bizarre question!Tue, 12 Oct 2010 03:32:01 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxHugo Kornelis, you are a ninja!i feel like i learned something now :)i was pretty confused by the question, and even the explanation of why i got it wrong, but now i can see clearly what's goin on.Thanks!Tue, 12 Oct 2010 03:20:18 GMTlukus_ghttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxoh....what a answer...i selected onlyxxxxxxxxxxxxxxxxxxx........But my answer was wrongTue, 12 Oct 2010 03:00:41 GMTSree Arjun Divhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspx[quote][b]Hugo Kornelis (10/12/2010)[/b][hr]The short explanation is that, within a [delimited identifier], you have to double the ] to get a single ] character.[/quote]Ah, that makes sense... Well, it doesn't completely, since I would expect that the opening square bracket should be escaped as well then for conistentcy. But whatever :)Still, SMO seems to have some issues dropping the table. But it looks like it's not the delimiter that is causing the problem, but a unix like linefeed instead of carriage return with linefeed between the "statements" in the name.Following is the statement that returns no rows because of the linefeeds when trying to drop the table via SSMS (recorded via profiler). The linefeeds (0x0A) can be seen by converting to varbinary(max) for example.[code]SELECTSCHEMA_NAME(tbl.schema_id) AS [Schema],tbl.name AS [Name]FROMsys.tables AS tblWHERE(tbl.name=N'VarArray[](i int)--GObegin tran insert into Vararray(i) SELECT 1rollback--GOwhile 1 = 1' and SCHEMA_NAME(tbl.schema_id)=N'dbo')[/code]Tue, 12 Oct 2010 02:20:18 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxAlthough it was a very tricky question (misleading header and code), I did find it useful to learn something about the delimiters. I also think it is worth 2 points because the answers were checkboxes (and there were many misleading answers), so it is harder to get the right answer.Tue, 12 Oct 2010 01:59:41 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxDrat - tricked again :(Tue, 12 Oct 2010 01:51:50 GMTjts_2003http://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxHugo, an excellent explanation, thank you. Whenever I see a QOTD that looks like it's trying to trick the reader I just leave it now. Learning something new I'm all in favour of, but just being tricked by a syntax error is of no interest to me. I see enough of those in my normal work.Tue, 12 Oct 2010 01:37:18 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspx[quote][b]Christian Buettner-167247 (10/12/2010)[/b][hr]But what "stuns" me is the fact that SQL Server does not deal correctly with this object name.Looks like it is too complicated :D(...)Notice the 2nd closing square bracket in the first line is missing.[/quote]Please check my previous reply for the long explanation.The short explanation is that, within a [delimited identifier], you have to double the ] to get a single ] character.(Just as you double a quote in a string to get a single quote character - i.e. SET @Name = 'O''Brien' )[quote](The same issue in the object browser. You cannot delete[/quote]You can always delete the table from the query window. Simply use the same SQL you used to create the table, but change "create" to "drop", and remove the column list.Tue, 12 Oct 2010 01:25:23 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspx[quote]This example is the query used to send a formatted html by xp_sendmail:[code="sql"]-- to test send output to text or file c:\temp\1.htmSET NOCOUNT ONselect '' AS []SELECT ' ' AS [ ]... [/code] [/quote]What is the purpose of this example? It fails with the message 'An object or column name is missing or empty. ... Aliases defined as "" or [] are not allowed. ...'When I removed the first SELECT statement, I didn't get any formatted HTML. I suppose that HTML tags in the example got mixed up with HTML tags in the QotD page.Carlo, could you please post a message with the proper example attached? :-)Tue, 12 Oct 2010 01:10:09 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic1002549-1299-1.aspxGot it wrong - did not see the final closing square bracket. But what "stuns" me is the fact that SQL Server does not deal correctly with this object name.Looks like it is too complicated :D[code]--Original name from the create table statement:[/code][quote]VarArray[][b]][/b](i int)--GObegin tran insert into Vararray(i) SELECT 1rollback--GOwhile 1 = 1[/quote][code]SELECT NAME FROM sys.objects WHERE name LIKE 'Var%'[/code][quote]VarArray[](i int)--GObegin tran insert into Vararray(i) SELECT 1rollback--GOwhile 1 = 1[/quote]Notice the 2nd closing square bracket in the first line is missing.(The same issue in the object browser. You cannot deleteTue, 12 Oct 2010 01:04:45 GMTChristian Buettner-167247