SORT_IN_TEMPDB in PK Constraints

• sxg6023

  SSCrazy

  Points: 2918

  More actions

  November 13, 2003 at 11:31 am

  #76079

  Is the SORT_IN_TEMPDB option available when I create a Primary Key Clustered

  constraint? This option is available when I do the same (ie Clustered Unique)

  as an index but erros when I do through constraint..

  Here is the background, I must enforce the uniqueness thru the PK, since I have these

  tables as part of a partitioned view( and it requires the partitioned columns

  to be part of the PK). I am recreating the constraint since at the end of

  each quarter I drop the corresponding partition table and recreate the constraint/index

  so I have 100% contiguousness. I would like to use SORT_IN_TEMPDB

  since that makes them more contiguous than doing in the user database itself.

  Any ideas?

  thanks.

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  November 13, 2003 at 12:54 pm

  #481948

  After creating them, you can use this on PKs:

  
  
  CREATE UNIQUE CLUSTERED INDEX pk_Table ON Table(PKCol) WITH DROP_EXISTING, SORT_IN_TEMPDB

  --Jonathan

  
  
  --Jonathan

• sxg6023

  SSCrazy

  Points: 2918

  More actions

  November 13, 2003 at 3:04 pm

  #481974

  That works great. Thanks you.

  It is interesting I needed to run it a couple of times to get it to 0% fragmentation.

  I would prefer to get the old partitions as close to contiguous as possible, since no

  data is loaded into them after the quarter is past.

  Thanks Again.