Where Did the Index Go???

Question

Post reply

Where Did the Index Go???

jpipes

SSChampion

Points: 12230
More actions
January 16, 2003 at 6:26 am

#80613

--- Anyone ever seen this??? ---
I created an index with a pound sign as the first character, thinking to designate as a temporary index. I was only using this index for some perfomance testing. Anyway, when I was done with the index, I tried dropping it, using the index name. Didn't work. Said unrecognized object name... Tried putting square brackets around the name. Same thing... Tried double quotes. Same thing... I looked in Enterprise Manager, and sure enough, there was my new (now not so temporary) index tied to my table. Tried dropping it through Enterprise Manager. Same error in a dialog box. I even went to the system tables and found the object, but it refused to let me delete the index, even after updating options to allow editing systables...
What happened!!!???

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 1

I couldn't re-produce this. The statement

drop index

.[£indexname] executed successfully.

Regards,

Andy Jones

.

jpipes SSChampion Points: 12230 More actions · Answer 2

quote:
I couldn't re-produce this. The statement
drop index
.[£indexname] executed successfully.
Regards,
Andy Jones

Sorry, my british friend. I should have specified, the American pound sign (#). 🙂 Next pint's on me...

Cheers.

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 3

Sorry for the error in translation!!

Same thing with the statement: -

drop index

.[#indexname] - this works.

This is on SQL2K what version are you using?

P.S. I'll buy the double cheeseburgers to go with your pint!

Regards,

Andy Jones

.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

This script ran fine on my SQL 2K machine:

create table c (c int)

go

CREATE INDEX #c

ON test.dbo.c

(c)

GO

sp_helpindex c

go

drop index c.#c

go

sp_helpindex c

go

drop table c

[/quote]

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

jpipes SSChampion Points: 12230 More actions · Answer 5

jpipes

SSChampion

Points: 12230

January 16, 2003 at 7:05 am

#446493

I'm on SQL7. Perhaps that's the issue?

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Tried it in SQL 7 and does seem to be an issue there when using # for the name of an index. It looks for it in the table as is treating table.index as owner.temptable instead. However adjusting for that does not fix the problem. In addition found that Dropping the table does release the index and that _ will work without issue.