October 11, 2010 at 11:27 pm
Comments posted to this topic are about the item Variable Array Table
October 12, 2010 at 12:09 am
so, does the transaction actually run and get rolled back?
October 12, 2010 at 12:29 am
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.
October 12, 2010 at 12:59 am
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.
October 12, 2010 at 1:04 am
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 😀
--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
October 12, 2010 at 1:10 am
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.htmSET 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? 🙂
October 12, 2010 at 1:25 am
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 😀
(...)
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.
October 12, 2010 at 1:37 am
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.
October 12, 2010 at 1:51 am
Drat - tricked again 🙁
October 12, 2010 at 1:59 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 12, 2010 at 2:20 am
Hugo Kornelis (10/12/2010)
The short explanation is that, within a [delimited identifier], you have to double the ] to get a single ] character.
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.
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Schema],
tbl.name AS [Name]
FROM
sys.tables AS tbl
WHERE
(tbl.name=N'VarArray[](i int)
--GO
begin tran
insert into Vararray(i) SELECT 1
rollback
--GO
while 1 = 1' and SCHEMA_NAME(tbl.schema_id)=N'dbo'
)
Best Regards,
Chris Büttner
October 12, 2010 at 3:00 am
oh....what a answer...i selected onlyxxxxxxxxxxxxxxxxxxx........But my answer was wrong
October 12, 2010 at 3:20 am
Hugo 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!
October 12, 2010 at 3:32 am
What a bizarre question!
October 12, 2010 at 4:10 am
Splendid work!!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply