Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Variable Array Table Expand / Collapse
Author
Message
Posted Monday, October 11, 2010 11:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:53 AM
Points: 2,529, Visits: 2,402
Comments posted to this topic are about the item Variable Array Table
Post #1002549
Posted Tuesday, October 12, 2010 12:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:22 PM
Points: 188, Visits: 76
so, does the transaction actually run and get rolled back?
Post #1002563
Posted Tuesday, October 12, 2010 12:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 6,002, Visits: 8,267
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
Post #1002570
Posted Tuesday, October 12, 2010 12:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 6,002, Visits: 8,267
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
Post #1002574
Posted Tuesday, October 12, 2010 1:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:05 AM
Points: 2,842, Visits: 3,875
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 :D

--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
Post #1002576
Posted Tuesday, October 12, 2010 1:10 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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?
Post #1002578
Posted Tuesday, October 12, 2010 1:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 6,002, Visits: 8,267
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 Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1002583
Posted Tuesday, October 12, 2010 1:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:30 AM
Points: 1,542, Visits: 8,255
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.


BrainDonor
Linkedin
Blog Site
Post #1002587
Posted Tuesday, October 12, 2010 1:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:28 AM
Points: 1,100, Visits: 4,898
Drat - tricked again
Post #1002589
Posted Tuesday, October 12, 2010 1:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 13,735, Visits: 10,705
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1002590
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse