THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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')

name
-------------------------- ----------------------------
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 http://drsql.spaces.live.com

Published Sunday, October 01, 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

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement