3

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?

  • What is the DDL for the Children table? What is the PK defined as? – SQLRockstar Apr 2 '12 at 15:52
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'
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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