Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server 13
#SQLServer 7
DBA 7
BRH 5
indexes 4
Power Shell 2
object naming 1
joke 1
whitespaces 1
training 1

Archive · View All
July 2011 6
August 2011 4
March 2012 2
February 2012 1
December 2011 1
October 2011 1
September 2011 1

Szymon Wojcik's Blog

Object whitespace-naming madness

Feb 16 2012 6:18PM by Szymon Wojcik   

Have you ever tried creating objects and giving them strange names? I made some experiment at one of lab servers, main focus was space (you know, the long key at the bottom of the keyboard :D). So, without further ado - the first object to be tested is a database. Run:

create database [ ]

and you get your favourite Command(s) completed successfully response. A quick look into Object Explorer shows that a space-named database was indeed created. Let’s script it and see what a monster we just spawned:

USE [master]   
GO  /****** Object:  Database [ ]    Script Date: 02/15/2012 22:21:54 ******/   
CREATE DATABASE [ ] ON  PRIMARY     
( NAME = N'', FILENAME = N'C:\SqlData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ .mdf' , SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )   
 LOG ON     
( NAME = N' _log', FILENAME = N'C:\SqlData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ _log.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)    
GO  ALTER DATABASE [ ] SET COMPATIBILITY_LEVEL = 100   
GO  IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))   

begin    
EXEC [ ].[dbo].[sp_fulltext_database] @action = 'enable'    
end    
...all the ALTERs go here...

The filenames on the HDD are .mdf (space.mdf) and _log.ldf (space_log.ldf), which are perfectly valid filenames as far as NTFS is concerned, but clearly database data file has a name of an empty string (weird). It seems that in this case some kind of trimming has taken place, but not in the case of database log file name which starts with a space. We can go forward and try creating another space-named database. Try

create database [  ]  

Notice slight difference in both scripts – first database is called one space, while second is two spaces. The result is at least weird (first result from SQL Server 2005, second from 2008 R2):

Msg 1801, Level 16, State 3, Line 1  
Database ‘  ‘ already exists. 

or

Msg 1801, Level 16, State 3, Line 1   
Database '  ' already exists. Choose a different database name.    

Funny, isn’t it? It seems as if SQL Server trims the database name of whitespaces before actually attempting to create such an object. I’m not wondering about added value that space-named databases might give, but certainly something is not right. If I could create a database with a name of “ “, why not “ “? Maybe other whitespaces will work as well? In fact, they do. Let’s try with a bell character (ASCII code 7). First obstacle is that SSMS editor does not let you type in bell character, so you have to work our way around with standard T-SQL CHAR function. But then operating system will not let you create a file with bell inside of a name, so you have to change the file names. In the end, the code is like this:

declare @DBName sysname   
declare @createQuery nvarchar(2000)  select @DBName = CHAR(7)   
select @createQuery = '    
CREATE DATABASE [' + @DBName + '] ON  PRIMARY     
( NAME = N''1'', FILENAME = N''C:\SqlData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\1.mdf'' , SIZE = 2048KB , FILEGROWTH = 1024KB )    
 LOG ON     
( NAME = N''1_log'', FILENAME = N''C:\SqlData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\1_log.ldf'' , SIZE = 1024KB , FILEGROWTH = 10%)    
'  execute sp_executesql @createQuery

I expected the bell character to act like one, so I opened SQLCMD and issued standard

SELECT * FROM sys.databases

But, to my surprise bell-character-named database is displayed as new line in the result set and my hopes for audible SQL are gone. :)

Let's go into our space-named database just to try to create a schema and a table inside with a column of type int. Try following piece of script:

use [ ]    
go    
create schema [ ]    
go    
create table [ ].[ ] ( [ ] int)    
go    

It really works, and the Object Explorer does prove it. You might actually test by issuing

INSERT INTO [ ].[ ].[ ].[ ] ([ ]) VALUES (1)

It would be even funnier if the column was of char type, then you could something like that (just don't get mixed with delimiters and text escaping :P):

INSERT INTO [ ].[ ].[ ].[ ] ([ ]) VALUES (' ')

Wrapping it all up - since the post contained a lot of humour - I would rather expect whitespaces not to be permitted in object names at all, or at least that an object name has to contain one or more characters. For the sake of clarity I would even go further – to prevent crazy people from going rampant let object names be like variable names in most programming languages – contain only letters, digits and underscore, but cannot start with a digit. On the other hand, you might say it's just how flexible SQL Server is.

Tags: SQL Server, whitespaces, joke, object naming,


Szymon Wojcik
67 · 3% · 877
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • Valid, wierd & true! A nice post to read on a lazy Saturday afternoon.

    On a more serious note, I agree. Trailing whitespaces should not be allowed for object names. I cannot count the number of times we had issues wherein replicaton would just not work on our development servers because a rookie had entered the database name as [DBName ] instead of [DBName].

    commented on Feb 18 2012 3:33AM
    Nakul Vachhrajani
    4 · 36% · 11645
  • Thanks Nakul.

    I had the same experience while trying to migrate a database server used by web apps - suddenly I couldn't get one of the apps to work due to space at the end of database name.

    commented on Feb 19 2012 6:50AM
    Szymon Wojcik
    67 · 3% · 877
  • Thanks Nakul

    These are the things that I live for and which present challenges bring light to the sometimes gloomy but necessary repetitiveness of our daily tasks. Only usually in hindsight though, and usually followed by a few choice words :-)

    Other little gems that have brightened my life lately, especially when trying to write some very challenging dynamic sql are:

    • Table names with periods in eg SIS.EXPORT_TABLE. Not a schema, just someone's unhelpful way of grouping objects. Try convincing the SQL parser that a 5-part name is valid, especially with some of the DMVs and system functions :-(

    • Column Names with spaces and single quote eg Nom de l'objet. This one really makes a mess of dynamic code.

    These were in 2 databases on the same server, so I might suspect the same culprit.

    Andy

    commented on Feb 27 2012 1:42PM
    andyhughes
    2852 · 0% · 3

Your Comment


Sign Up or Login to post a comment.

"Object whitespace-naming madness" rated 5 out of 5 by 4 readers
Object whitespace-naming madness , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]