Hugo Kornelis (10/12/2010)

---

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:

[font="Courier New"]create table [VarArray[]](i int)[/font]

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

[font="Courier New"]create table [VarArray[]](i int)[/font]

you will get an error message:

[font="Courier New"]Msg 105, Level 15, State 1, Line 1

Unclosed quotation mark after the character string 'VarArray[](i int)

'.[/font]

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:

[font="Courier New"]while 1=1)][/font]

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:

VarArray[](i int)

GO

begin tran

insert into VarArray(i) select 1

rollback

GO

while(1=1)

and with one single column, named print_i and typed as integer.