5

I have a partition scheme in my SQL Server database that I am trying to drop. This scheme was used by 2 indexes, which I deleted.

I can't delete the scheme, since it appears to still be used by the indexes, even though they don't exist anymore.

Running the following query:

select object_name(i.object_id), i.name, i.index_id
FROM   sys.indexes i
JOIN   sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
WHERE  ps.name = 'CallScheme'

I get the names of the 2 tables that had the indexes, but the index names are NULL (and index id is 0).

How do I get rid of these indexes so that I can drop the partition scheme?

1 Answer 1

5

but the index names are NULL (and index id is 0)

That's because they are heaps. BOL Reference on sys.indexes

One way to get these off of that partition scheme would be to create a clustered index for those tables and specify a different filegroup or another partition scheme. Then once you have those tables off of that partition scheme you should be able to continue with removing it.

create clustered index IX_YourIndex
on dbo.YourTable (YourCiKeyCol)
on SomeOtherFilegroup;
3
  • Surely the on SomeOtherFileGroup is not important in this case? Just creating a clustered index on the same filegroup should still move the data, removing the dependency on the partition scheme, no? I could certainly be wrong - it wouldn't be the first time!
    – Hannah Vernon
    Jun 12, 2014 at 12:36
  • Correct, I was explicit just to solidify a point. Jun 12, 2014 at 12:38
  • Cool - I just wanted to make sure since for many databases, having a 2nd filegroup is not necessary.
    – Hannah Vernon
    Jun 12, 2014 at 12:40

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.