1

In https://stackoverflow.com/a/26048363/143768, a clever way of eliminating the IDENTITY property from a table was provided using the magic of partition switching. This approach, however, does not work if your table is already partitioned. (And, in fact, this method is more applicable to very large multi-terabyte tables, which are likely are already partitioned.)

Since I am working in exactly this situation — trying to eliminate the IDENTITY attribute on some very large tables — does anyone know how to adapt this solution to already-partitioned tables?

EDIT: This is actually more complicated than I originally envisioned. There is at least one index that is not partitioned. When I try traditional partition switching:

ALTER TABLE [dbo].[era_adj] SWITCH PARTITION 1 TO [dbo].[tmp_ms_xx_era_adj] PARTITION 1;
. . .
ALTER TABLE [dbo].[era_adj] SWITCH PARTITION 57 TO [dbo].[tmp_ms_xx_era_adj] PARTITION 57;

I get the error:

Msg 7733, Level 16, State 4, Line 21
'ALTER TABLE SWITCH' statement failed. The table 'ERADev.dbo.era_adj' is partitioned while index 'PK_era_adj' is not partitioned.

So now I also need to figure out how to partition my indexes...

Community
  • 1
  • 1
David Mercer
  • 123
  • 1
  • 9
  • Is the identity column the partitioning key? If not, couldn't you create an identical table (without the identity property) on the same partition schema/function, and switch each partition in? (Maybe you need to use a dummy, non-partitioned, empty table in between, and move one partition at a time. I haven't tried it, just a wild theory, hence a comment and not an answer.) – Aaron Bertrand Oct 06 '14 at 18:16
  • No, the identity column is not the partitioning key. Your approach is what I would do absent any input from more experienced engineers. If I get no responses, I'll try it and post whether it worked or not. – David Mercer Oct 06 '14 at 20:13
  • @DavidMercer - https://duckduckgo.com/?q=%22aaron+bertrand%22 – Hannah Vernon Oct 06 '14 at 20:15
  • @DavidMercer You're right. A SQL Server MVP is not an experienced individual. – Kermit Oct 06 '14 at 20:16
  • Admittedly, I did say I haven't tried it, and I'm not sure that was what was meant anyway. :-) – Aaron Bertrand Oct 06 '14 at 20:17
  • Clearly I meant more experienced than me, not more experienced than Aaron. Sorry for the confusion. – David Mercer Oct 06 '14 at 21:03

0 Answers0