Problem with not partition aligned nonclustered indizes

Nov 21, 2014 at 1:26 PM
Hello,

if I run the utility (ClearPartition) on a table which has a not partitioned aligned nonclustered index (unique constraint), I get a NullReference Exception in PartitionManagement.cs Row 384 (stgIndex.FileGroup = db.PartitionSchemes[i.PartitionScheme].FileGroups[partitionNumber - 1];). Obviously, the non partitioned index cannot have a PartitionScheme. Do I oversee something or is it supposed that I take care of such indexes myself (by dropping them beforehand)?

Thanks,

Andrej
Coordinator
Dec 1, 2014 at 9:27 PM
Hi Andrej -- You will need to drop any non-aligned indexes yourself beforehand, and rebuild after SWITCH operations take place.
  • Stuart
Dec 9, 2014 at 1:51 PM
Hello Stuart,

thanks for answering. I've opted for a T-SQL way, because I needed a little bit more custom handling, but your Partition Management tool actually covers a lot of ground.
Two possible improvements:
  1. You don't handle ANSI_PADDING OFF setting (a bad idea anyway but some kind of warning would be great)
  2. If I only want to switch out some partitions and drop the switch targets, I don't need to create default and check constraint (fk constraints probably too). It's just lost time.
Thanks

Andrej