July 25, 2016 at 11:05 pm
Comments posted to this topic are about the item How does SQL work with blanks?
July 26, 2016 at 12:47 am
How curious! Thanks for the question. But surely it is a question of blanks being suppressed?
...
July 26, 2016 at 12:59 am
In my eyes that entire syntax is a bug.
It should not be possible to create a database who name consists of nothing but spaces.
Anybody that creates a database with spaces as a name should be fired.
July 26, 2016 at 1:11 am
It's not a bug and nor a feature.
The first database is stored in sys.databases with double blank.
As you know, the compare condition and len function don't matter of trailing spaces.
So, zero, one o more spaces are equal to an empty string.
These query find the database named [ ] -- 2 spaces
create database [ ] -- 2 spaces
SELECT datalength(name) AS STORED_LEN, len(name) NAME_LEN,* FROM sys.databases
WHERE name = '' -- empty string
-- or
SELECT datalength(name) AS STORED_LEN, len(name) NAME_LEN,* FROM sys.databases
WHERE name = ' ' -- lot of spaces
-- same result
July 26, 2016 at 1:11 am
It also works with schemas, tables, types and columns.
This makes for a great statement such as:
select [ ] from [ ].[ ].[ ]
I guess it is a good way to test bracketing works.
I haven't tried with linked server...
July 26, 2016 at 1:26 am
As I remember, table names are stored as a sysname column, and looking at the Technet page for special data types https://technet.microsoft.com/en-us/library/ms191240(v=sql.105).aspx it states
sysname is functionally the same as nvarchar(128)
Therefore, as with all variable length character fields, trailing spaces are ignored.
Philip
July 26, 2016 at 1:44 am
philip.cullingworth (7/26/2016)
Therefore, as with all variable length character fields, trailing spaces are ignored.Philip
That's not true!
Trailing spaces are stored also in nvarchar e varchar datatype.
Compare and len function ignore trailing spaces.
July 26, 2016 at 2:01 am
This was removed by the editor as SPAM
July 26, 2016 at 2:07 am
When i saw question i really shocked, and i was start thinking, is it correct question?, written syntax is true or not?. But when i run, i got it's working. But i didn't get why not working both create syntax.
But really it's nice question. it's looking like simple but many more explanation required for understand logic behind.
Thanks. 🙂
July 26, 2016 at 7:06 am
Interesting question, but rather badly put together - it could have been a good question if it had simply included a statement saying that the four statements are executed as four separate batches. As it stands, it looks as if they are a single batch, and none of the answer options is correct in that case.
Actually the question is ambiguous and there are 3 possible answers depending how the statements batched for execution; only two of theose answers are given as options, but bith are correct depending on the batching:-
The last 3 statements are all in the same batch: one database is created and not deleted. That wasn't an answer option, so obviously they were meant to be split into batches rather than executed together as shown.
The statements are split into batches so that the second and third statements are in a batch which doesn't contain the fourth statement: one database is created and deleted by the same name. That's one of the answer options, but is stated to be incorrect although the question doesn't include any condition that excludes it.
The statements are split into batches and the second and third statements are not in the same batch: one database is created and deleted by a different name. That's the option that the question has chosen, but how are we supposed to guess that?
Tom
July 26, 2016 at 7:39 am
TomThomson (7/26/2016)
Interesting question, but rather badly put together - it could have been a good question if it had simply included a statement saying that the four statements are executed as four separate batches. As it stands, it looks as if they are a single batch, and none of the answer options is correct in that case.Actually the question is ambiguous and there are 3 possible answers depending how the statements batched for execution; only two of theose answers are given as options, but bith are correct depending on the batching:-
The last 3 statements are all in the same batch: one database is created and not deleted. That wasn't an answer option, so obviously they were meant to be split into batches rather than executed together as shown.
The statements are split into batches so that the second and third statements are in a batch which doesn't contain the fourth statement: one database is created and deleted by the same name. That's one of the answer options, but is stated to be incorrect although the question doesn't include any condition that excludes it.
The statements are split into batches and the second and third statements are not in the same batch: one database is created and deleted by a different name. That's the option that the question has chosen, but how are we supposed to guess that?
I agree Tom. If there had been batch separators it would have helped but truly the "correct" is wrong no matter how you look at it. The database was not dropped using a different name. It was dropped and the string conversion determined the name supplied matches the database name.
Regardless of the ambiguity of the question and the answer it was certainly an interesting question and made the brain wake up a little bit.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 26, 2016 at 7:54 am
TomThomson (7/26/2016)
Interesting question, but rather badly put together - it could have been a good question if it had simply included a statement saying that the four statements are executed as four separate batches.
That's not true.
Same result if run all stms in the same batch.
Try (choose output to text instead of grid):
create database [ ] print '1'
create database [ ] print '2'
drop database [ ] print '3'
drop database [ ] print '4'
July 26, 2016 at 8:28 am
In any case, very witty and refreshing. Thanks, Brett!
July 26, 2016 at 10:48 am
Carlo Romagnano (7/26/2016)
TomThomson (7/26/2016)
Interesting question, but rather badly put together - it could have been a good question if it had simply included a statement saying that the four statements are executed as four separate batches.That's not true.
Same result if run all stms in the same batch.
Try (choose output to text instead of grid):
create database [ ] print '1'create database [ ] print '2'
drop database [ ] print '3'
drop database [ ] print '4'
If I run that against SQL Server 2016 the only print statement that fires is "print 1"; The second create statement causes an error which aborts the batch, so only the first create statement and print satement are executed. So I think that what I wrote is perfectly true and your contradiction of it is certainly false at least some of the time 😉
Tom
July 26, 2016 at 11:07 am
Thank you for the post,... well.. really creepy.
I was just reading the Rules on Regular Database Identifiers and some rules it fails to imply, seems like a bug.
(may be this is fixed in 2016 or the item is queued for SP1 or SP2)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply