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.

lukus_g (10/12/2010)so, does the transaction actually run and get rolled back?

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:create table [VarArray[]](i int)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 linecreate table [VarArray[]](i int)you will get an error message:Msg 105, Level 15, State 1, Line 1Unclosed quotation mark after the character string 'VarArray[](i int)'.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:while 1=1)]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:

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?

Christian Buettner-167247 (10/12/2010)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.

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' )

(The same issue in the object browser. You cannot delete

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.

Hugo, 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.

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