THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Another reason to hate quoted identifiers...

I was working with the Redgate compare tool today (something I am a big fan of I might add,) using it to take a snapshot of my database so I could drop all code and do a collation change to a database (an article I updated because I discovered a new wrinkle with computed columns needing to be modified.)

When I got to that point in the process to start adding back indexes, I noticed that a table said it was missing.  Oiks, that is not a good feeling.  This was a fairly large database that had taken well over an hour to do, and if I lost a table I was just going to...take a deep breath, you didn't drop a table by accident...I hope...

So I look at the other side of the comparison and hmm, a table with the same name is listed in the column of stuff only on the other side.  So in essence, it is saying that there is a table named fred on one database, and fred on the other.  This made me very unhappy, or at least curious.  So I went looking, and couldn't find anything at first.  I went into sys.objects, there were two objects that looked the same.

But then I noticed something in the scripts that were being displayed that told me the issue.  While it is generally a good idea to use quoted identifiers when generating a script using existing objects, the name you enter will be the name that is used, for better or for worse.  My problem turned out to be that there was just a space appended to the object name.  You would never ever notice in code because SQL Server does name comparisons just like it does strings:

create table fred
         fredId int

create table [barney ]
         barneyId int

select *
from   fred
             cross join barney

Returns data just like if the name was not quoted.  But if you quote the names:

select name, quotename(name)
from sys.objects
where name in ('fred','barney')

-------------------------- ----------------------------
fred                           [fred]
barney                       [barney ]

Now it would be clear that [barney ] <> [barney] and if you were comparing the two names, the names would not be the same.

Crossposted to

Published Sunday, October 1, 2006 8:06 PM by drsql
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog


Links to my other sites


Privacy Statement