Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««12345

Variable Array Table Expand / Collapse
Posted Wednesday, November 3, 2010 6:33 PM



Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 23,522, Visits: 37,751
Okay, 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.

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1015646
Posted Monday, November 22, 2010 2:34 PM

Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, April 13, 2015 4:52 AM
Points: 579, Visits: 27,695
This question fooled me

Thanks Hugo for the great walkthrough, I learned something new :)

Post #1024716
Posted Tuesday, February 1, 2011 1:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 1:59 AM
Points: 1,042, Visits: 234
A tricky question indeed.

And very good explaination by Hugo. Thanks Hugo. Learnt about delimiters.
Post #1056650
Posted Friday, August 12, 2011 12:16 AM


Group: General Forum Members
Last Login: Monday, November 28, 2016 9:58 AM
Points: 2,674, Visits: 601
Hugo Kornelis (10/12/2010)
lukus_g (10/12/2010)
so, does the transaction actually run and get rolled back?

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 line
create table [VarArray[]](i int)
you will get an error message:
Msg 105, Level 15, State 1, Line 1
Unclosed 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:
VarArray[](i int)
begin tran
insert into VarArray(i) select 1

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

Thank you for your explanation, I couldn't understand the original explanation.

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Post #1158979
Posted Wednesday, September 18, 2013 10:44 AM


Group: General Forum Members
Last Login: Friday, November 18, 2016 5:59 AM
Points: 174, Visits: 271
Thank you for the explanation, Hugo!

Yet I totally disagree with you in one point: This question definitely is NOT useless!

I find it helpful to train me on being careful and look twice before answering something. And that _you_ didn't learn something: OK, accepted. But please do not talk for me: I was surprised by the result and it was good for a laugh; additionally it improved my understanding for delimiters.

If you set out to do something, something else must be done first.
Post #1496015
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse