Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Variable Array Table


Variable Array Table

Author
Message
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3858 Visits: 3276
Comments posted to this topic are about the item Variable Array Table

I run on tuttopodismo
lukus_g
lukus_g
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 80
so, does the transaction actually run and get rolled back?
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8931 Visits: 11740
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8931 Visits: 11740
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 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)
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3085 Visits: 3889
Got 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 BigGrin

--Original name from the create table statement:

VarArray[]](i int)
--GO
begin tran
insert into Vararray(i) SELECT 1
rollback
--GO
while 1 = 1


SELECT NAME FROM sys.objects WHERE name LIKE 'Var%'

VarArray[](i int)
--GO
begin tran
insert into Vararray(i) SELECT 1
rollback
--GO
while 1 = 1


Notice the 2nd closing square bracket in the first line is missing.
(The same issue in the object browser. You cannot delete

Best Regards,

Chris Büttner
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3544 Visits: 4408
This example is the query used to send a formatted html by xp_sendmail:

-- to test send output to text or file c:\temp\1.htm
SET NOCOUNT ON
select '' AS []
SELECT
' ' AS [ ]
...



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? :-)
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8931 Visits: 11740
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 BigGrin
(...)
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 Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Steve Hall
Steve Hall
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2264 Visits: 11368
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.

Steve Hall
Linkedin
Blog Site
jts2013
jts2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1186 Visits: 5009
Drat - tricked again Sad
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19401 Visits: 13250
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search