Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to create an index on table [Children] in SQL Azure but get the following error:

The operation failed because an index or statistics with name 'IX_Children_ParentID' already exists on table 'Children'.

As far as I can see there's no index or statistics with this name. The following command returned zero results so there shouldn't be any indexes or statistics. Is that correct?

SELECT object_id, name FROM sys.stats WHERE name = 'IX_Children_ParentID'
UNION ALL
SELECT object_id, name FROM sys.indexes WHERE name = 'IX_Children_ParentID'

UPDATE: Changed the query in response to @Shark's anwer.

My SQL command for creating the index:

CREATE INDEX IX_Children_ParentID
ON [myschema].[Children]
(
    [ParentID] DESC
)

Before this error occurred I had problems with timeouts while creating the index. Could this be caused by some remnants?

share|improve this question
    
What is the DDL for the Children table? What is the PK defined as? –  SQLRockstar Apr 2 '12 at 15:52

1 Answer 1

The catalog view sys.all_objects won't show nonclustered indexes. Here's the query you should be using:

select
    object_name(object_id) as table_name, 
    name as index_name
from sys.indexes
where name = 'IX_Children_ParentID'

Or to get all of the indexes for that table:

select
    object_name(object_id) as table_name, 
    name as index_name
from sys.indexes
where object_name(object_id) = 'Children'
share|improve this answer
    
Thanks. Still there's no indexes in the result (I've queried sys.stats as well). The SQL Server Management Studio isn't showing any index or statistics in the GUI either. –  Jonas Stensved Apr 2 '12 at 13:48
    
Check for unique constraints or PK's named like that (IX_Children_ParentID). –  Marian Apr 2 '12 at 14:01
    
@Marian, yes of course. I posted this question just because I can't find the cause of it. –  Jonas Stensved Apr 2 '12 at 14:06
    
@JonasStensved What was the result set of my second query that retrieves all indexes for that table? –  Thomas Stringer Apr 2 '12 at 14:20
    
@Shark, just the primary key; [PK_Children] –  Jonas Stensved Apr 2 '12 at 14:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.