Beste Möglichkeit, den gruppierten Index (PK) in SQL 2005 zu ändern

Question

Ich habe eine Tabelle, die einen gruppierten Index auf zwei Spalten hat - den Primärschlüssel für die Tabelle. Es ist wie folgt definiert:

ALTER TABLE Table ADD CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED
(
  [ColA] ASC,
  [ColB] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Ich möchte diesen gruppierten Index PK entfernen und einen gruppierten Index wie folgt hinzufügen und eine Primärschlüssel-Beschränkung unter Verwendung eines nicht gruppierten Indexes hinzufügen, wie unten gezeigt.

CREATE CLUSTERED INDEX [IX_Clustered] ON [Table]
(
  [ColC] ASC,
  [ColA] ASC,
  [ColD] ASC,
  [ColE] ASC,
  [ColF] ASC,
  [ColG] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,     DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]

ALTER TABLE Table ADD CONSTRAINT
  PK_Table PRIMARY KEY NONCLUSTERED
  (
    ColA,
    ColB
  ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Ich wollte einfach den PK gruppierten Index löschen, dann den neuen gruppierten Index hinzufügen und dann den nicht gruppierten Primärschlüsselindex hinzufügen, aber ich habe erfahren, dass das Löschen des vorhandenen gruppierten Indexes dazu führen würde, dass die Tabellendaten neu geordnet werden (siehe Antwort hier Was passiert, wenn ich einen gruppierten Primärschlüssel in SQL 2005 lösche), was meiner Meinung nach nicht notwendig sein sollte. Die Tabelle ist 1 TB groß, daher möchte ich wirklich jede unnötige Neuordnung vermeiden.

Meine Frage ist, wie man am besten von der vorhandenen Struktur zur gewünschten Struktur gelangt?

EDIT: Möchte nur klarstellen. Die Tabelle ist 1 TB groß und ich habe leider keinen Platz, um eine temporäre Tabelle zu erstellen. Falls es einen Weg gibt, dies ohne Erstellen einer Temp-Tabelle zu tun, lassen Sie es mich bitte wissen.

Answer 1

Dies ist keine vollständige Antwort auf Ihre Frage, aber stellen Sie sicher, dass Sie zuerst alle anderen Indizes in der Tabelle löschen, wenn Sie diese haben. Andernfalls muss SQL Server sie alle neu erstellen, wenn Sie den gruppierten Index entfernen, und sie dann erneut neu erstellen, wenn Sie einen neuen gruppierten Index hinzufügen. Die üblichen Schritte sind:

1. Alle nicht gruppierten Indizes entfernen
2. Gruppierten Index entfernen
3. Neuen gruppierten Index hinzufügen
4. Alle nicht gruppierten Indizes wieder hinzufügen

Answer 2

Wenn Ihre Tabelle bis zu 1 TB groß wird und wahrscheinlich VIELE Zeilen enthält, würde ich dringend davon abraten, den gruppierten Index so viel breiter zu machen!

Zunächst einmal wird das Löschen und Neu Erstellen des gruppierten Index alle Ihre Daten mindestens einmal durcheinander bringen - das allein wird ewig dauern.

Zweitens wird der große zusammengesetzte gruppierte Index, den Sie erstellen möchten, die Größe aller Ihrer nicht gruppierten Indizes erheblich erhöhen (da sie den gesamten gruppierten Indexwert auf jedem Blattknoten enthalten, für die Lesevorgänge mit Lesezeichen).

Die Frage lautet eher: Warum versuchen Sie das zu tun?? Könnten Sie nicht einfach einen weiteren nicht gruppierten Index mit diesen Spalten hinzufügen, um potenziell Ihre Abfragen abzudecken? Warum muss das der gruppierte Index sein?? Ich sehe keinen Vorteil darin....

Weitere Informationen zu Indizierung und insbesondere zur Diskussion über den gruppierten Index finden Sie auf Kimberly Tripps Blog über SQL Server-Indizes - sehr hilfreich!

Marc

Answer 3

1. Eine neue Tabelle erstellen:

   CREATE TABLE newtable (colA INT, colB INT)

   • Alle Werte aus der alten Tabelle in die neue Tabelle einfügen:

     INSERT INTO newtable SELECT * FROM table

   • Die alte Tabelle löschen:

     DROP TABLE table

   • Die neue Tabelle in die alte Tabelle umbenennen

     EXEC sp_rename 'newtable', 'table'

   • Die Indexe erstellen:

     ALTER TABLE Table ADD CONSTRAINT PK_Table PRIMARY KEY NONCLUSTERED ( ColA, ColB ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Answer 4

Gruppierte Indizes ändern tatsächlich nicht die physische Reihenfolge der im Tabellenspeicher selbst gespeicherten Daten. Das ist seit SQL 6.5 nicht mehr so.

Die Daten auf den Seiten werden in der richtigen Reihenfolge gespeichert. Die Seiten können auf der Festplatte in beliebiger physischer Reihenfolge gespeichert werden.