Partitioning a table with primary key

• SQL Guy 1

  SSCoach

  Points: 15974

  More actions

  July 9, 2013 at 12:20 pm

  #275067

  We have a table that we are going to recreate on partition scheme. Its clustered index is a primary key.

  1. Can I just drop index without dropping the primary key ?

  2. In case if the above is impossible, what the best way to overcome the problem with foreign keys created on other tables from our primary key ?

  Should I drop them and re-create ? I tried to disable, but it does not work.

  In a short, what is the best practice and sequence of steps in partitioning a table ?

  Note: we already have partition function, scheme, filegroups. And some other tables are also partitioned.

  Thanks

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  July 10, 2013 at 3:40 am

  #1631131

  Good news! there is an easy way out:

  CREATE UNIQUE CLUSTERED INDEX pk_table(pkcol)

  WITH (DROP_EXISTING = ON) ON partition_scheme(pkcol)

  The key is the DROP_EXISTING = ON clause.

  Now, if you plan to do partition switching in the future, that will not be equally painless.

  [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]