Teilen über

CREATE COLUMNSTORE INDEX (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Konvertieren Sie eine Rowstore-Tabelle in einen gruppierten Columnstore-Index, oder erstellen Sie einen nicht gruppierten Columnstore-Index. Verwenden Sie einen Columnstore-Index, um eine operative Echtzeitanalyse für eine OLTP-Workload effizient auszuführen oder um die Datenkomprimierung und Abfrageleistung für Data Warehouse-Workloads zu verbessern.

Folgen Sie den Neuerungen in Columnstore-Indizes, um bei den neuesten Verbesserungen dieses Features auf dem Laufenden zu bleiben.

  • Gruppierte Columnstore-Indizes wurden in SQL Server 2022 (16.x) eingeführt. Weitere Informationen finden Sie unter CREATE COLUMNSTORE INDEX. Die Verfügbarkeit des sortierten Columnstore-Index finden Sie unter Columnstore-Indizes: Übersicht.

  • Ab SQL Server 2016 (13.x) können Sie die Tabelle als gruppierten Columnstore-Index erstellen. Es ist nun nicht mehr erforderlich, zuerst eine Rowstore-Tabelle zu erstellen und diese dann in einen gruppierten Columnstore-Index zu konvertieren.

  • Informationen zu Entwurfsrichtlinien für Columnstore-Indizes finden Sie unter Columnstore-Indizes – Entwurfsleitfaden.

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für SQL Server und Azure SQL-Datenbank:

-- Create a clustered columnstore index on disk-based table.
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER (column [ , ...n ] ) ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

-- Create a nonclustered columnstore index on a disk-based table.
CREATE [ NONCLUSTERED ]  COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
        ( column  [ , ...n ] )
    [ ORDER (column [ , ...n ] ) ]
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

<with_option> ::=
      DROP_EXISTING = { ON | OFF } -- default is OFF
    | MAXDOP = max_degree_of_parallelism
    | ONLINE = { ON | OFF }
    | COMPRESSION_DELAY  = { 0 | delay [ MINUTES ] }
    | DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { partition_number_expression | range } [ , ...n ] ) ]

<on_option>::=
      partition_scheme_name ( column_name )
    | filegroup_name
    | "default"

<filter_expression> ::=
      column_name IN ( constant [ , ...n ]
    | column_name { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< } constant )

Syntax für Azure Synapse Analytics, Parallel Data Warehouse, SQL Server 2022 (16.x) und höher:

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER ( column [ , ...n ] ) ]
    [ WITH ( DROP_EXISTING = { ON | OFF } ) ] -- default is OFF
[;]

Argumente

Einige Optionen sind nicht in allen Datenbank-Engine-Versionen verfügbar. Die folgende Tabelle enthält die Versionen, in denen die Optionen in GRUPPIERTEN COLUMNSTORE- und NICHT GRUPPIERTEN COLUMNSTORE-INDIZES eingeführt wurden:

Option CLUSTERED NONCLUSTERED
COMPRESSION_DELAY SQL Server 2016 (13.x) SQL Server 2016 (13.x)
DATA_COMPRESSION SQL Server 2016 (13.x) SQL Server 2016 (13.x)
ONLINE SQL Server 2019 (15.x) SQL Server 2017 (14.x)
WHERE-Klausel – SQL Server 2016 (13.x)

Alle Optionen sind in Azure SQL-Datenbank verfügbar.

CREATE CLUSTERED COLUMNSTORE INDEX

Erstellt einen gruppierten Columnstore-Index, in dem alle Daten komprimiert und nach Spalten gespeichert werden. Der Index beinhaltet alle Spalten der Tabelle und speichert die gesamte Tabelle. Wenn es sich bei der vorhandenen Tabelle um einen Heap oder gruppierten Index handelt, wird sie in einen gruppierten Spaltenspeicherindex konvertiert. Wenn die Tabelle bereits als gruppierter Columnstore-Index gespeichert ist, wird der vorhandene Index gelöscht und neu erstellt.

index_name

Gibt den Namen für den neuen Index an.

Wenn die Tabelle bereits einen gruppierten Columnstore-Index enthält, können Sie denselben Namen wie für den vorhandenen Index angeben oder die DROP EXISTING-Option zum Angeben eines neuen Namens verwenden.

ON [ database_name. [ schema_name ] . | schema_name . ] table_name

Gibt den ein-, zwei- oder dreiteiligen Namen der Tabelle an, die als gruppierter Columnstore-Index gespeichert werden soll. Wenn die Tabelle ein Heap ist oder über einen gruppierten Index verfügt, wird sie von Rowstore in Columnstore konvertiert. Wenn die Tabelle bereits ein Columnstore ist, wird mit dieser Anweisung der gruppierte Columnstore-Index neu erstellt.

ORDER für gruppierten Spaltenspeicher

Verwenden Sie die column_store_order_ordinal Spalte in sys.index_columns , um die Reihenfolge der Spalten für einen gruppierten Spaltenspeicherindex zu bestimmen. Spaltenspeicher-Sortierungshilfen bei der Segmententfernung, insbesondere bei Zeichenfolgendaten. Weitere Informationen finden Sie unter Leistungsoptimierung mit sortierten gruppierten Columnstore-Indizes und Columnstore-Indizes – Entwurfsanleitungen.

Um in einen geordneten gruppierten Columnstore-Index zu konvertieren, muss der vorhandene Index ein gruppierter Spaltenspeicherindex sein. Verwenden Sie die DROP_EXISTING-Option.

LOB-Datentypen (Datentypen mit (max) Länge) können nicht der Schlüssel eines geordneten gruppierten Columnstore-Index sein.

Verwenden Sie beim Erstellen eines gruppierten Columnstore-Indexes OPTION(MAXDOP = 1) für die höchste Qualität der Sortierung im Austausch für eine wesentlich längere Dauer der CREATE INDEX Anweisung. Um den Index so schnell wie möglich zu erstellen, beschränken Sie MAXDOP nicht. Die höchste Qualität bei Komprimierung und Sortierung kann für Abfragen des Columnstore-Index nützlich sein.

Die Verfügbarkeit des sortierten Columnstore-Index finden Sie unter Columnstore-Indizes: Übersicht.

WITH-Optionen

DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON gibt an, dass der vorhandene Index gelöscht und ein neuer Columnstore-Index erstellt werden soll.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (DROP_EXISTING = ON);

Bei Verwendung der Standardeinstellung DROP_EXISTING = OFF wird erwartet, dass der Indexname mit dem vorhandenen Namen übereinstimmt. Wenn der angegebene Indexname bereits vorhanden ist, tritt ein Fehler auf.

MAXDOP = max_degree_of_parallelism

Diese Option kann die Serverkonfiguration für den maximalen Grad an Parallelität während des Indexvorgangs überschreiben. Sie können mit MAXDOP die Anzahl der Prozessoren begrenzen, die bei der Ausführung paralleler Pläne verwendet werden. Maximal sind 64 Prozessoren zulässig.

max_degree_of_parallelism kann folgende Werte haben:

  • 1: Das Generieren paralleler Pläne wird unterdrückt.
  • >1: Die maximale Anzahl der Prozessoren, die bei einem parallelen Indexvorgang verwendet werden, wird je nach aktueller Systemauslastung auf die angegebene Zahl oder einen niedrigeren Wert beschränkt. Beispiel: Wenn MAXDOP = 4, beträgt die Anzahl der verwendeten Prozessoren 4 oder weniger.
  • 0 (Standardwert): Je nach aktueller Systemauslastung wird die tatsächliche Anzahl von Prozessoren oder weniger verwendet.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (MAXDOP = 2);

Weitere Informationen finden Sie unter Serverkonfiguration: max. Grad der Parallelität und Konfigurieren von Parallelindexvorgängen.

COMPRESSION_DELAY = 0 | Verzögerung [Minuten]

Bei einer datenträgerbasierten Tabelle wird mit Verzögerung die Mindestanzahl von Minuten angegeben, die eine Deltazeilengruppe im Status „Geschlossen“ in der Deltazeilengruppe beibehalten werden muss. Anschließend kann sie von SQL Server in die komprimierte Zeilengruppe komprimiert werden. Da Einfüge- und Aktualisierungszeiten in datenträgerbasierten Tabellen nicht für einzelne Zeilen nachverfolgt werden, wird die Verzögerung in SQL Server auf Deltazeilengruppen im Status „Geschlossen“ angewendet.

Die Standardeinstellung beträgt 0 Minuten.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( COMPRESSION_DELAY = 10 MINUTES );

Empfehlungen zur Verwendung von COMPRESSION_DELAY finden Sie unter Erste Schritte mit Columnstore für die operative Echtzeitanalyse.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

Gibt die Datenkomprimierungsoption für die angegebene Tabelle, die Partitionsnummer oder den Bereich von Partitionen an. Die folgenden Optionen sind verfügbar:

  • COLUMNSTORE ist die Standardeinstellung und gibt an, dass die leistungsstärkste Columnstore-Komprimierung ausgeführt werden soll. Diese Option ist die gängige Methode.
  • Durch COLUMNSTORE_ARCHIVE wird die Tabelle oder Partition weiter in eine geringere Größe komprimiert. Verwenden Sie diese Option für Situationen, bei denen es auf eine geringere Speichergröße und nicht auf den zusätzlichen Zeitaufwand für das Speichern und Abrufen ankommt, wie etwa die Archivierung.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

Weitere Informationen zur Komprimierung finden Sie unter Datenkomprimierung.

ONLINE = [ON | OFF]
  • ON gibt an, dass der Columnstore-Index online und verfügbar bleibt, während die neue Kopie des Index erstellt wird.
  • OFF gibt an, dass der Index nicht verfügbar ist, während die neue Kopie erstellt wird.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( ONLINE = ON );

ON-Optionen

Mit diesen Optionen können Sie Optionen für die Datenspeicherung angeben, wie z. B. ein Partitionsschema, eine bestimmte Dateigruppe oder die Standarddateigruppe. Wird die ON-Option nicht angegeben, verwendet der Index die Einstellungspartition oder Dateigruppeneinstellungen der vorhandenen Tabelle.

partition_scheme_name (column_name) gibt das Partitionsschema für die Tabelle an. Das Partitionsschema muss in der Datenbank bereits vorhanden sein. Informationen zum Erstellen des Partitionsschemas finden Sie unter CREATE PARTITION SCHEME (Transact-SQL).

column_name gibt die Spalte an, auf deren Grundlage ein partitionierter Index partitioniert wird. Diese Spalte muss mit dem Datentyp, der Länge und der Genauigkeit des Arguments der Partitionsfunktion übereinstimmen, die partition_scheme_name verwendet.

filegroup_name gibt die Dateigruppe zum Speichern des gruppierten Columnstore-Index an. Wird kein Speicherort angegeben und ist die Tabelle nicht partitioniert, verwendet der Index die gleiche Dateigruppe wie die zugrunde liegende Tabelle oder Sicht. Die Dateigruppe muss bereits vorhanden sein.

Um den Index für die Standarddateigruppe zu erstellen, verwenden "default" Oder [default]. Wenn Sie angeben "default", muss die QUOTED_IDENTIFIER Option für die aktuelle Sitzung sein ON . QUOTED_IDENTIFIER ist standardmäßig ON. Weitere Informationen finden Sie unter SET QUOTED_IDENTIFIER (Transact-SQL).

CREATE [NONCLUSTERED] COLUMNSTORE INDEX

Erstellen Sie einen nicht gruppierten Columnstore-Index in einer als Heap oder gruppierter Index gespeicherten Rowstore-Tabelle. Der Index kann gefiltert sein und muss nicht alle Spalten der zugrunde liegenden Tabelle enthalten. Der Columnstore-Index benötigt genügend Platz zum Speichern einer Kopie der Daten. Sie können den Index aktualisieren, und er wird bei einer Änderung der zugrunde liegenden Tabelle aktualisiert. Der nicht gruppierte Columnstore-Index in einem gruppierten Index ermöglicht eine Echtzeitanalyse.

index_name

Gibt den Namen des Indexes an. index_name muss innerhalb der Tabelle eindeutig sein, kann aber innerhalb der Datenbank mehrfach vorkommen. Indexnamen müssen den Regeln für Bezeichner entsprechen.

(column [ ,...n ])

Gibt die zu speichernden Spalten an. Ein nicht gruppierter Spaltenspeicherindex ist auf 1.024 Spalten beschränkt.

Jede Spalte muss ein unterstützter Datentyp für columnstore-Indizes sein. Eine Liste der unterstützten Datentypen finden Sie unter Einschränkungen.

ON [ database_name. [ schema_name ] . | schema_name . ] table_name

Gibt den ein-, zwei- oder dreiteiligen Name der Tabelle an, die den Index enthält.

ORDER für nicht gruppierten Columnstore

Die in der ORDER Klausel für einen nicht gruppierten Columnstore-Index angegebenen Spalten müssen eine Teilmenge der Schlüsselspalten für den Index sein.

Verwenden Sie die column_store_order_ordinal Spalte in sys.index_columns , um die Reihenfolge der Spalten für einen nicht gruppierten Spaltenspeicherindex zu bestimmen. Spaltenspeicher-Sortierungshilfen bei der Segmententfernung, insbesondere bei Zeichenfolgendaten. Weitere Informationen finden Sie unter Leistungsoptimierung mit sortierten gruppierten Columnstore-Indizes und Columnstore-Indizes – Entwurfsanleitungen. Entwurfs- und Leistungsüberlegungen in diesen Artikeln gelten in der Regel sowohl für gruppierte als auch für nicht gruppierte Spaltenspeicherindizes.

Lob-Datentypen (die Datentypen der maximalen Länge) können nicht der Schlüssel eines sortierten nicht gruppierten Spaltenspeicherindex sein.

Verwenden Sie OPTION(MAXDOP = 1) beim Erstellen eines geordneten, nicht gruppierten Columnstore-Indexes für die höchste Qualität der Sortierung im Austausch für eine wesentlich längere Dauer der CREATE INDEX Anweisung. Um den Index so schnell wie möglich zu erstellen, beschränken Sie MAXDOP nicht. Die höchste Qualität bei Komprimierung und Sortierung kann für Abfragen des Columnstore-Index nützlich sein.

Informationen zur Verfügbarkeit des sortierten Spaltenspeicherindexes finden Sie unter Verfügbarkeit des geordneten Spaltenindexes.

WITH-Optionen

DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON Der vorhandene Index wird gelöscht und neu erstellt. Der angegebene Indexname muss mit dem Namen eines derzeit vorhandenen Index übereinstimmen. Die Indexdefinition kann jedoch geändert werden. Sie können z. B. andere Spalten oder Indexoptionen angeben.

DROP_EXISTING = OFF
Ist der angegebene Indexname bereits vorhanden, wird ein Fehler angezeigt. Der Indextyp kann nicht mithilfe von DROP_EXISTING geändert werden. In abwärtskompatibler Syntax ist WITH DROP_EXISTING gleichwertig mit WITH DROP_EXISTING = ON.

MAXDOP = max_degree_of_parallelism

Überschreibt die Serverkonfiguration: maximale Parallelitätskonfigurationsoption während des Indexvorgangs. Sie können mit MAXDOP die Anzahl der Prozessoren begrenzen, die bei der Ausführung paralleler Pläne verwendet werden. Maximal sind 64 Prozessoren zulässig.

max_degree_of_parallelism kann folgende Werte haben:

  • 1: Das Generieren paralleler Pläne wird unterdrückt.
  • >1: Die maximale Anzahl der Prozessoren, die bei einem parallelen Indexvorgang verwendet werden, wird je nach aktueller Systemauslastung auf die angegebene Zahl oder einen niedrigeren Wert beschränkt. Beispiel: Wenn MAXDOP = 4, beträgt die Anzahl der verwendeten Prozessoren 4 oder weniger.
  • 0 (Standardwert): Je nach aktueller Systemauslastung wird die tatsächliche Anzahl von Prozessoren oder weniger verwendet.

Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

Hinweis

Parallele Indexvorgänge sind nicht in jeder Edition von Microsoft SQL Server verfügbar. Eine Liste der Funktionen, die von den SQL Server-Editionen unterstützt werden, finden Sie unter Editionen und unterstützte Funktionen von SQL Server 2022.

ONLINE = [ON | OFF]
  • ON gibt an, dass der Columnstore-Index online und verfügbar bleibt, während die neue Kopie des Index erstellt wird.
  • OFF gibt an, dass der Index nicht verfügbar ist, während die neue Kopie erstellt wird. In einem nicht gruppierten Index bleibt die Basistabelle verfügbar. Nur der nicht gruppierte Columnstore-Index wird nicht für Abfragen verwendet, bis der neue Index erstellt ist.
CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate)
WITH ( ONLINE = ON );
COMPRESSION_DELAY = 0 | Verzögerung [Minuten]

Gibt eine Untergrenze für die Zeitdauer an, während der eine Zeile in der Deltazeilengruppe beibehalten wird, bevor sie in eine komprimierte Zeilengruppe migriert werden kann. So können Sie beispielsweise angeben, dass eine Zeile für die Komprimierung in das Spaltenspeicherformat freigegeben werden soll, wenn sie 120 Minuten lang nicht geändert wurde.

Bei einem Columnstore-Index für eine datenträgerbasierte Tabelle wird der Zeitpunkt des Einfüge- oder Aktualisierungsvorgangs einer Zeile nicht nachverfolgt. Stattdessen wird der Zeitpunkt, zu dem die Deltazeilengruppe geschlossen wurde, als Proxy für die Zeile verwendet. Die Standardeinstellung beträgt 0 Minuten. Eine Zeile wird nach 1 Millionen Zeilen in die Delta-Zeilengruppe migriert und als geschlossen markiert.

DATA_COMPRESSION

Gibt die Datenkomprimierungsoption für die angegebene Tabelle, die Partitionsnummer oder den Bereich von Partitionen an. Gilt nur für Columnstore-Indizes (gruppierte und nicht gruppierte). Die folgenden Optionen sind verfügbar:

  • COLUMNSTORE ist die Standardeinstellung und gibt an, dass die leistungsstärkste Columnstore-Komprimierung ausgeführt werden soll. Diese Option ist die gängige Methode.
  • Durch COLUMNSTORE_ARCHIVE wird die Tabelle oder Partition weiter in eine geringere Größe komprimiert. Verwenden Sie diese Option zur Archivierung und in anderen Situationen, in denen es auf eine geringere Speichergröße und nicht auf den zusätzlichen Zeitaufwand für das Speichern und Abrufen ankommt.

Weitere Informationen zur Komprimierung finden Sie unter Datenkomprimierung.

WHERE <Filterausdruck> [ AND <Filterausdruck> ]

Nach dem Aufruf eines Filterprädikats gibt diese Option an, welche Zeilen in den Index aufgenommen werden sollen. SQL Server erstellt gefilterte Statistikdaten für die Datenzeilen im gefilterten Index.

Für das Filterprädikat wird eine einfache Vergleichslogik verwendet. Vergleiche, die Literale verwenden NULL , sind mit den Vergleichsoperatoren nicht zulässig. Verwenden Sie stattdessen die Operatoren IS NULL und IS NOT NULL.

Es folgen einige Beispiele für Filterprädikate für die Production.BillOfMaterials-Tabelle:

  • WHERE StartDate > '20000101' AND EndDate <= '20000630'
  • WHERE ComponentID IN (533, 324, 753)
  • WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Informationen zu gefilterten Indizes finden Sie unter Erstellen gefilterter Indizes.

ON-Optionen

Die folgenden Optionen geben die Dateigruppen an, für die der Index erstellt wird.

partition_scheme_name ( column_name )

Gibt das Partitionsschema an, das die Dateigruppen definiert, denen die Partitionen eines partitionierten Index zugeordnet werden. Das Partitionsschema muss bereits durch Ausführen von CREATE PARTITION SCHEME in der Datenbank vorhanden sein.

column_name gibt die Spalte an, auf deren Grundlage ein partitionierter Index partitioniert wird. Diese Spalte muss mit dem Datentyp, der Länge und der Genauigkeit des Arguments der Partitionsfunktion übereinstimmen, die partition_scheme_name verwendet. column_name ist nicht auf die Spalten in der Indexdefinition beschränkt. Bei der Partitionierung eines Columnstore-Index fügt die Datenbank-Engine die Partitionierungsspalte als Spalte des Index hinzu, wenn sie nicht bereits angegeben ist.

Sind bei einer partitionierten Tabelle partition_scheme_name oder filegroup nicht angegeben, wird der Index im gleichen Partitionsschema platziert und verwendet die gleiche Partitionierungsspalte wie die zugrunde liegende Tabelle.

Ein columnstore-Index einer partitionierten Tabelle muss über eine Partitionsausrichtung verfügen. Weitere Informationen zur Partitionierung von Indizes finden Sie unter Partitionierte Tabellen und Indizes.

filegroup_name

Gibt den Namen einer Dateigruppe an, für die der Index erstellt werden soll. Ist filegroup_name nicht angegeben und die Tabelle nicht partitioniert, verwendet der Index die gleiche Dateigruppe wie die zugrunde liegende Tabelle. Die Dateigruppe muss bereits vorhanden sein.

"default"

Erstellt den angegebenen Index für die Standarddateigruppe.

Die Benennung „default“ ist in diesem Kontext kein Schlüsselwort. Dabei handelt es sich um einen Bezeichner für die Standarddateigruppe, der wie in ON "default" oder ON [default] abgegrenzt sein muss. Wenn "default" angegeben wird, muss die Option QUOTED_IDENTIFIER für die aktuelle Sitzung auf ON festgelegt sein, was die Standardeinstellung ist. Weitere Informationen finden Sie unter SET QUOTED_IDENTIFIER.

Berechtigungen

Erfordert die ALTER-Berechtigung für die Tabelle.

Bemerkungen

Sie können einen Columnstore-Index für eine temporäre Tabelle erstellen. Wenn die Tabelle gelöscht oder die Sitzung beendet wird, wird der Index ebenfalls gelöscht.

Gefilterte Indizes

Ein gefilterter Index ist ein optimierter nicht gruppierter Index, der für Abfragen für einen kleinen Prozentsatz der Zeilen in einer Tabelle geeignet ist. Es wird ein Filterprädikat verwendet, um einen Teil der Daten in der Tabelle zu indizieren. Ein gut entworfener gefilterter Index kann die Abfrageleistung verbessern, den Speicheraufwand verringern und Wartungskosten reduzieren.

Erforderliche SET-Optionen für gefilterte Indizes

Die SET-Optionen in der Spalte „Erforderlicher Wert“ sind immer dann erforderlich, wenn eine der folgenden Bedingungen zutrifft:

  • Sie erstellen einen gefilterten Index.
  • Die Daten in einem gefilterten Index werden durch einen INSERT-, UPDATE-, DELETE- oder MERGE-Vorgang geändert.
  • Der Abfrageoptimierer verwendet den gefilterten Index zum Erstellen des Abfrageplans.
SET-Optionen Erforderlicher Wert Standardserverwert OLE DB- und ODBC-Standardwert DB-Library-Standardwert
ANSI_NULLS EIN EIN EIN OFF
ANSI_PADDING EIN EIN EIN OFF
ANSI_WARNINGS 1 EIN EIN EIN OFF
ARITHABORT EIN EIN OFF OFF
CONCAT_NULL_YIELDS_NULL EIN EIN EIN OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER EIN EIN EIN OFF

1 Durch Festlegen von ANSI_WARNINGS auf ON wird implizit ARITHABORT auf ON festgelegt, wenn der Datenbank-Kompatibilitätsgrad auf mindestens 90 festgelegt ist. Wird der Kompatibilitätsgrad der Datenbank auf 80 oder niedriger festgelegt, müssen Sie die ARITHABORT-Option explizit auf ON festlegen.

Wenn die SET-Optionen falsch sind, können die folgenden Bedingungen auftreten:

  • Der gefilterte Index wird nicht erstellt.

  • Die Datenbank-Engine generiert einen Fehler und führt ein Rollback aller INSERT-, UPDATE-, DELETE- oder MERGE-Anweisungen aus, die im Index gespeicherte Daten ändern.

  • Der Abfrageoptimierer berücksichtigt für Transact-SQL-Anweisungen nicht den Index im Ausführungsplan.

Weitere Informationen zu gefilterten Indizes finden Sie unter Erstellen gefilterter Indizes.

Einschränkungen

Jede Spalte in einem Columnstore-Index muss von einem der folgenden allgemeinen Geschäftsdatentypen sein:

  • datetimeoffset [ ( n ) ]
  • datetime2 [ ( n ) ]
  • datetime
  • smalldatetime
  • date
  • time [ ( n ) ]
  • float [ ( n ) ]
  • real [ ( n ) ]
  • decimal [ ( precision [ , scale ] ) ]
  • numeric [ ( precision [ , scale ] ) ]
  • money
  • smallmoney
  • bigint
  • int
  • smallint
  • tinyint
  • bit
  • nvarchar [ ( n ) ]
  • nvarchar(max) 1
  • nchar [ ( n ) ]
  • varchar [ ( n ) ]
  • varchar(max) 1
  • char [ ( n ) ]
  • varbinary [ ( n ) ]
  • varbinary(max) 1
  • binary [ ( n ) ]
  • Eindeutiger Bezeichner 2

1 Gilt für SQL Server 2017 (14.x) und Azure SQL-Datenbank im Premium- und Standard-Tarif (S3 und höher) sowie allen Tarifen der vCore-Angebote, nur in gruppierten Columnstore-Indizes

2 Gilt für SQL Server 2014 (12.x) und höhere Versionen

Enthält die zugrunde liegende Tabelle eine Spalte, die einen Datentyp aufweist, der für Columnstore-Indizes nicht unterstützt wird, müssen Sie diese Spalte aus dem nicht gruppierten Columnstore-Index ausschließen.

LOB-Daten (Large Object), die größer als 8 KB sind, werden zeilenextern im Spaltensegment in einem LOB-Speicher mit nur einem Zeiger auf den physischen Speicherort gespeichert. Die Größe der gespeicherten Daten wird nicht in sys.column_store_segments, sys.column_store_dictionaries oder sys.dm_db_column_store_row_group_physical_stats gemeldet.

Spalten, die einen der folgenden Datentypen aufweisen, dürfen nicht in einem Columnstore-Index enthalten sein:

  • ntext, text und image
  • nvarchar(max), varchar(max), und varbinary(max) 1
  • rowversion (und timestamp)
  • sql_variant
  • CLR-Typen (hierarchyid- und räumliche Typen)
  • xml
  • Eindeutiger Bezeichner 2

1 Gilt für SQL Server 2016 (13.x) und frühere Versionen sowie nicht gruppierte Columnstore-Indizes

2 Gilt für SQL Server 2012 (11.x)

Nicht gruppierte Columnstore-Indizes:

  • Darf nicht mehr als 1.024 Spalten enthalten.
  • Können nicht als einschränkungsbasierte Indizes erstellt werden. Es ist möglich, dass Tabellen mit einem Columnstore-Index UNIQUE-, PRIMARY KEY- oder FOREIGN KEY-Einschränkungen enthalten. Einschränkungen werden immer mit einem Zeilenspeicherindex durchgesetzt. Sie können nicht mit einem (gruppierten oder nicht gruppierten) Columnstore-Index erzwungen werden.
  • Können keine Sparsespalte enthalten.
  • Können nicht mithilfe der ALTER INDEX-Anweisung geändert werden. Um den nicht gruppierten Index zu ändern, müssen Sie stattdessen den columnstore-Index löschen und neu erstellen. Sie können einen Columnstore-Index mithilfe von ALTER INDEX deaktivieren und neu erstellen.
  • Können nicht mit dem Schlüsselwort INCLUDE erstellt werden.
  • Können nicht die Schlüsselwörter ASC oder DESC zum Sortieren des Index enthalten. Columnstore-Indizes werden gemäß den Komprimierungsalgorithmen sortiert. Durch die Sortierung würden viele der Leistungsvorteile entfernt werden. In Azure Synapse Analytics und ab SQL Server 2022 (16.x) können Sie für die Spalten in einem Columnstore-Index eine Reihenfolge angeben. Weitere Informationen finden Sie unter Leistungsoptimierung mit geordneten gruppierten Columnstore-Indizes.
  • Lobspalten vom Typ "nvarchar(max)", "varchar(max)" und "varbinary(max)" können nicht in nicht gruppierte Columnstore-Indizes eingeschlossen werden. Nur gruppierte Columnstore-Indizes unterstützen branchenspezifische Typen, beginnend mit der SQL Server 2017 (14.x)-Version, Azure SQL-Datenbank (konfiguriert auf Premium-Ebene, Standardebene (S3 und höher) und allen vCore-Angebotsebenen). In früheren Versionen werden branchenspezifische Typen in gruppierten und nicht gruppierten Spaltenspeicherindizes nicht unterstützt.
  • Ab SQL Server 2016 (13.x) können Sie einen nicht gruppierten Columnstore-Index für eine indizierte Sicht erstellen.

Columnstore-Indizes können nicht mit den folgenden Features kombiniert werden:

  • Berechnete Spalten. Ab SQL Server 2017 (14.x) kann ein gruppierter Spaltenspeicherindex eine nicht persistente berechnete Spalte enthalten. In SQL Server 2017 (14.x) können gruppierte Spaltenspeicherindizes jedoch keine gespeicherten berechneten Spalten enthalten, und Sie können keine nicht gruppierten Indizes für berechnete Spalten erstellen.
  • Seiten- und Zeilenkomprimierung und das Vardecimal-Speicherformat . (Ein Columnstore-Index ist bereits in einem anderen Format komprimiert.)
  • Replikation mit gruppierten Columnstore-Indizes. Nicht gruppierte Spaltenspeicherindizes werden unterstützt. Weitere Informationen finden Sie unter sp_addarticle.
  • Filestream

In einer Tabelle mit einem gruppierten Columnstore-Index können keine Cursor oder Trigger verwendet werden. Diese Einschränkung gilt nicht für nicht gruppierte Columnstore-Indizes. In Tabellen mit einem nicht gruppierten Columnstore-Index können Cursor und Trigger verwendet werden.

Spezifische Einschränkungen für SQL Server 2014 (12.x):

Die folgenden Einschränkungen gelten nur für SQL Server 2014 (12.x). Seit diesem Release können Sie aktualisierbare gruppierte Columnstore-Indizes verwenden. Nicht gruppierte Columnstore-Indizes sind weiter schreibgeschützt.

  • Änderungsnachverfolgung. Für Columnstore-Indizes kann keine Änderungsnachverfolgung verwendet werden.
  • Change Data Capture. Dieses Feature kann nicht für Tabellen mit einem gruppierten Columnstore-Index aktiviert werden. Ab SQL Server 2016 (13.x) kann die Datenerfassung für Tabellen mit einem nicht gruppierten Spaltenspeicherindex aktiviert werden.
  • Lesbares sekundäres Replikat. Auf einen gruppierten Columnstore-Index (Clustered Columnstore Index, CCI) kann nicht über ein lesbares sekundäres Replikat einer lesbaren Always On-Verfügbarkeitsgruppe zugegriffen werden. Auf einen nicht gruppierten Columnstore-Index (NCCI) kann über ein lesbares sekundäres Replikat zugegriffen werden.
  • Mehrere aktive Resultsets (MARS). SQL Server 2014 (12.x) verwendet dieses Feature für schreibgeschützte Verbindungen mit Tabellen mit einem Columnstore-Index. SQL Server 2014 (12.x) unterstützt dieses Feature jedoch nicht für gleichzeitige DML-Vorgänge (Datenbearbeitungssprache) für eine Tabelle mit einem Columnstore-Index. Sollten Sie dennoch versuchen, das Feature für diesen Zweck zu verwenden, beendet SQL Server die Verbindungen und bricht die Transaktionen ab.
  • Nicht gruppierte Columnstore-Indizes können nicht für eine Sicht oder eine indizierte Sicht erstellt werden.

Informationen zu den Leistungsvorteilen und Einschränkungen von Spaltenspeicherindizes finden Sie unter Columnstore-Indizes: Übersicht.

Metadaten

Alle Spalten in einem Columnstore-Index werden in den Metadaten als eingeschlossene Spalten gespeichert. Der Columnstore-Index hat keine Schlüsselspalten. Die folgenden Systemsichten enthalten Informationen zu Columnstore-Indizes:

Beispiele: Konvertieren einer Tabelle von Rowstore in Columnstore

A. Konvertieren eines Heaps in einen gruppierten Columnstore-Index

In diesem Beispiel wird eine Tabelle als Heap erstellt und anschließend in einen gruppierten Columnstore-Index mit dem Namen cci_Simple konvertiert. Die Erstellung des gruppierten Columnstore-Index ändert den Speicher für die gesamte Tabelle von Rowstore in Columnstore.

CREATE TABLE dbo.SimpleTable(
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON dbo.SimpleTable;
GO

B. Konvertieren eines gruppierten Index in einen gruppierten Columnstore-Index mit demselben Namen

In diesem Beispiel wird eine Tabelle mit einem gruppierten Index erstellt. Anschließend wird die Syntax zum Konvertieren des gruppierten Indexes in einen gruppierten Columnstore-Index veranschaulicht. Die Erstellung des gruppierten Columnstore-Index ändert den Speicher für die gesamte Tabelle von Rowstore in Columnstore.

CREATE TABLE dbo.SimpleTable2 (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable2 (ProductKey);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON dbo.SimpleTable2
WITH (DROP_EXISTING = ON);
GO

C. Verarbeiten nicht gruppierter Indizes beim Konvertieren einer Rowstore-Tabelle in einen Columnstore-Index

In diesem Beispiel wird gezeigt, wie Sie nicht gruppierte Indizes beim Konvertieren einer Rowstore-Tabelle in einen Columnstore-Index verarbeiten. Ab SQL Server 2016 (13.x) ist dafür keine bestimmte Aktion erforderlich. SQL Server definiert und erstellt die nicht gruppierten Indizes automatisch für den neuen gruppierten Columnstore-Index.

Wenn Sie die nicht gruppierten Indizes ablegen möchten, verwenden Sie die DROP INDEX-Anweisung, bevor Sie den Columnstore-Index erstellen. Mit der DROP EXISTING-Option wird nur der konvertierte gruppierte Index gelöscht. Die nicht gruppierten Indizes werden nicht gelöscht.

In SQL Server 2012 (11.x) und SQL Server 2014 (12.x) kann für einen Columnstore-Index kein nicht gruppierter Index erstellt werden.

--Create the table for use with this example.
CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
  
--Create two nonclustered indexes for use with this example
CREATE INDEX nc1_simple ON dbo.SimpleTable (OrderDateKey);
CREATE INDEX nc2_simple ON dbo.SimpleTable (DueDateKey);
GO

Zum Erstellen des Columnstore-Index müssen die nicht gruppierten Indizes nur für SQL Server 2012 (11.x) und SQL Server 2014 (12.x) gelöscht werden.

DROP INDEX dbo.SimpleTable.nc1_simple;
DROP INDEX dbo.SimpleTable.nc2_simple;
  
--Convert the rowstore table to a columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON dbo.SimpleTable;
GO

D: Konvertieren einer großen Faktentabelle von einem Rowstore in einen Columnstore

In diesem Beispiel wird erläutert, wie eine große Faktentabelle von einer Rowstore-Tabelle in eine Columnstore-Tabelle konvertiert wird.

  1. Erstellen Sie eine kleine Tabelle, die Sie für dieses Beispiel verwenden können.

    --Create a rowstore table with a clustered index and a nonclustered index.
CREATE TABLE dbo.MyFactTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL
INDEX IDX_CL_MyFactTable CLUSTERED  ( ProductKey )
);

--Add a nonclustered index.
CREATE INDEX my_index ON dbo.MyFactTable ( ProductKey, OrderDateKey );

  2. Löschen Sie alle nicht gruppierten Indizes aus der Rowstore-Tabelle. Sie können ein Skript für die Indizes erstellen, um sie später neu erstellen zu können.

    --Drop all nonclustered indexes
DROP INDEX my_index ON dbo.MyFactTable;

  3. Konvertieren Sie die Rowstore-Tabelle in eine Columnstore-Tabelle mit einem gruppierten Columnstore-Index.

    Suchen Sie zunächst nach dem Namen des vorhandenen gruppierten Rowstore-Index. Legen Sie in Schritt 1 den Namen des Index auf IDX_CL_MyFactTable fest. Wurde kein Indexname angegeben, wird ein automatisch generierter eindeutiger Indexname erstellt. Sie können den automatisch generierten Namen mit der folgenden Beispielabfrage abrufen:

    SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'MyFactTable';

    Option 1: Löschen Sie den vorhandenen gruppierten Index IDX_CL_MyFactTable, und konvertieren Sie MyFactTable in einen Columnstore-Index. Ändern Sie den Namen des neuen gruppierten Columnstore-Index.

    --Drop the clustered rowstore index.
DROP INDEX [IDX_CL_MyFactTable]
ON dbo.MyFactTable;
GO
--Create a new clustered columnstore index with the name MyCCI.
CREATE CLUSTERED COLUMNSTORE
INDEX IDX_CCL_MyFactTable ON dbo.MyFactTable;
GO

    Option 2: Konvertieren Sie in Columnstore, und verwenden Sie den vorhandenen Namen des gruppierten Rowstore-Index weiter.

    --Create the clustered columnstore index,
--replacing the existing rowstore clustered index of the same name
CREATE CLUSTERED COLUMNSTORE
INDEX [IDX_CL_MyFactTable]
ON dbo.MyFactTable
WITH (DROP_EXISTING = ON);

E. Konvertieren einer Columnstore-Tabelle in eine Rowstore-Tabelle mit einem gruppierten Index

Verwenden Sie die CREATE INDEX-Anweisung mit der DROP_EXISTING-Option, um eine Columnstore-Tabelle in eine Rowstore-Tabelle mit einem gruppierten Index zu konvertieren.

CREATE CLUSTERED INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable] ( ProductKey )
WITH ( DROP_EXISTING = ON );

F. Konvertieren einer Columnstore-Tabelle in einen Rowstore-Heap

Löschen Sie zum Konvertieren einer Columnstore-Tabelle in einen Rowstore-Heap den gruppierten Columnstore-Index. Diese Methode wird in der Regel nicht empfohlen, kann aber in einigen wenigen Fällen geeignet sein. Weitere Informationen zu Heaps finden Sie unter Heaps (Tabellen ohne gruppierte Indizes).

DROP INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable];

G. Defragmentieren durch Neuorganisieren des Columnstore-Indizes

Es gibt zwei Möglichkeiten, den gesamten gruppierten Columnstore-Index zu pflegen. Ab SQL Server 2016 (13.x) sollten Sie ALTER INDEX...REORGANIZE ansttat REBUILD verwenden. Weitere Informationen finden Sie unter Zeilengruppe des Columnstore-Indizes. In früheren Versionen von SQL Server können Sie CREATE CLUSTERED COLUMNSTORE INDEX mit DROP_EXISTING=ON oder ALTER INDEX (Transact-SQL) und die Option REBUILD verwenden. Mit beiden Methoden wurden die gleichen Ergebnisse erzielt.

Ermitteln Sie zunächst den Namen des gruppierten Columnstore-Indizes in MyFactTable.

SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t on i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED COLUMNSTORE'
AND t.name = 'MyFactTable';

Entfernen Sie die Fragmentierung, indem Sie REORGANIZE im Columnstore-Index ausführen.

--Rebuild the entire index by using ALTER INDEX and the REBUILD option.
ALTER INDEX IDX_CL_MyFactTable
ON dbo.[MyFactTable]
REORGANIZE;

Beispiele für nicht gruppierte Columnstore-Indizes

A. Erstellen eines Columnstore-Indexes als sekundärer Index für eine Rowstore-Tabelle

In diesem Beispiel wird ein nicht gruppierter Columnstore-Index für eine Rowstore-Tabelle erstellt. In diesem Fall kann nur ein Columnstore-Index erstellt werden. Der Columnstore-Index erfordert zusätzlichen Speicher, da er eine Kopie der Daten in der Rowstore-Tabelle enthält. In diesem Beispiel wird eine einfache Tabelle und ein gruppierter Rowstore-Index erstellt. Anschließend wird die Syntax zum Erstellen eines nicht gruppierten Columnstore-Indexes beschrieben.

CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO

CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable (ProductKey);
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON dbo.SimpleTable (OrderDateKey, DueDateKey, ShipDateKey);
GO

B. Erstellen eines einfachen nicht gruppierten Columnstore-Index mit allen Optionen

Das folgende Beispiel veranschaulicht die Syntax zum Erstellen eines nicht gruppierten Columnstore-Index für die Dateigruppe DEFAULT unter Angabe eines maximalen Grads an Parallelität (MAXDOP) von 2.

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable (OrderDateKey, DueDateKey, ShipDateKey)
WITH (DROP_EXISTING =  ON,
    MAXDOP = 2)
ON "DEFAULT";
GO

C. Erstellen eines nicht gruppierten Columnstore-Indexes mit einem gefilterten Prädikat

Im folgenden Beispiel wird ein gefilterter nicht gruppierter Columnstore-Index für die Tabelle Production.BillOfMaterials in der Beispieldatenbank AdventureWorks2022 erstellt. Das Filterprädikat kann Spalten enthalten, die keine Schlüsselspalten im gefilterten Index sind. Das Prädikat in diesem Beispiel wählt nur die Zeilen aus, in denen EndDate nicht NULL ist.

IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

D: Ändern der Daten in einem nicht gruppierten Columnstore-Index

Gilt für: SQL Server 2012 (11.x) bis SQL Server 2014 (12.x).

Nachdem Sie in SQL Server 2014 (12.x) und früheren Versionen einen nicht gruppierten Spaltenspeicherindex für eine Tabelle erstellt haben, können Sie die Daten in dieser Tabelle nicht direkt ändern. Eine Abfrage mit INSERT, UPDATE, MERGE oder DELETE schlägt fehl und gibt eine Fehlermeldung zurück. Hier sind Optionen, mit denen Sie die Daten in der Tabelle hinzufügen oder ändern können:

  • Deaktivieren oder löschen Sie den Columnstore-Index. Anschließend können Sie die Daten in der Tabelle aktualisieren. Wenn Sie den Columnstore-Index deaktivieren, können Sie den Columnstore-Index nach dem Aktualisieren der Daten neu erstellen. Beispiel:

    ALTER INDEX mycolumnstoreindex ON dbo.mytable DISABLE;
-- update the data in mytable as necessary
ALTER INDEX mycolumnstoreindex on dbo.mytable REBUILD;

  • Laden Sie Daten in eine Stagingtabelle ohne Columnstore-Index. Erstellen Sie einen Columnstore-Index für die Stagingtabelle. Wechseln Sie für die Stagingtabelle in eine leere Partition der Haupttabelle.

  • Wechseln Sie für eine Partition in der Tabelle mit dem Columnstore-Index in eine leere Stagingtabelle. Wenn die Stagingtabelle über einen Columnstore-Index verfügt, deaktivieren Sie den Columnstore-Index. Nehmen Sie die gewünschten Updates vor. Erstellen bzw. erstellen Sie den Columnstore-Index neu. Wechseln Sie für die Stagingtabelle zurück in die (nun leere) Partition der Haupttabelle.

Beispiele: Azure Synapse Analytics, Analytics-Plattformsystem (PDW)

A. Ändern eines gruppierten Indexes in einen gruppierten Columnstore-Index

Mit der CREATE CLUSTERED COLUMNSTORE INDEX-Anweisung und DROP_EXISTING = ON haben Sie folgende Möglichkeiten:

  • Ändern eines gruppierten Indexes in einen gruppierten Columnstore-Index

  • Neuerstellen eines gruppierten Columnstore-Indexes

In diesem Beispiel wird die Tabelle xDimProduct als Rowstore-Tabelle mit einem gruppierten Index erstellt. Anschließend wird mit CREATE CLUSTERED COLUMNSTORE INDEX die Rowstore-Tabelle in eine Columnstore-Tabelle geändert.

-- Uses AdventureWorks
  
IF EXISTS (SELECT name FROM sys.tables
    WHERE name = N'xDimProduct'
    AND object_id = OBJECT_ID (N'xDimProduct'))
DROP TABLE xDimProduct;
  
--Create a distributed table with a clustered index.
CREATE TABLE xDimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey)
WITH ( DISTRIBUTION = HASH(ProductKey),
    CLUSTERED INDEX (ProductKey) )
AS SELECT ProductKey, ProductAlternateKey, ProductSubcategoryKey FROM DimProduct;

Suchen Sie den Namen des gruppierten Indexes, der mit sys.indexes automatisch für die neue Tabelle in den Systemmetadaten erstellt wurde. Beispiel:

SELECT i.object_id, i.name, t.object_id, t.name, i.type_desc
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'xdimProduct';

Nun stehen folgende Optionen zur Auswahl:

  1. Löschen Sie den vorhandenen gruppierten Columnstore-Index mit einem automatisch erstellten Namen, und erstellen Sie dann einen neuen gruppierten Columnstore-Index mit einem benutzerdefinierten Namen.
  2. Löschen Sie den vorhandenen Index und ersetzen Sie ihn durch einen gruppierten Columnstore-Index, wobei Sie den vom System generierten Namen beibehalten, z. B. ClusteredIndex_1bd8af8797f7453182903cc68df48541.

Beispiel:

--1. DROP the existing clustered columnstore index with an automatically-created name, for example:
DROP INDEX ClusteredIndex_1bd8af8797f7453182903cc68df48541 on xdimProduct;
GO
CREATE CLUSTERED COLUMNSTORE INDEX [<new_index_name>]
ON xdimProduct;
GO

--Or,
--2. Change the existing clustered index to a clustered columnstore index with the same name.
CREATE CLUSTERED COLUMNSTORE INDEX [ClusteredIndex_1bd8af8797f7453182903cc68df48541]
ON xdimProduct
WITH ( DROP_EXISTING = ON );
GO

B. Neuerstellen eines gruppierten Columnstore-Indexes

Ausgehend vom vorherigen Beispiel wird in diesem Beispiel der vorhandene gruppierte Columnstore-Index mit dem Namen cci_xDimProduct mit CREATE CLUSTERED COLUMNSTORE INDEX neu erstellt.

--Rebuild the existing clustered columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = ON );

C. Ändern des Namens eines gruppierten Columnstore-Indexes

Wenn Sie den Namen eines gruppierten Columnstore-Index ändern möchten, löschen Sie den vorhandenen gruppierten Columnstore-Index, und erstellen Sie anschließend den Index mit einem neuen Namen neu.

Es wird empfohlen, diesen Vorgang nur auf eine kleine oder leere Tabelle anzuwenden. Einen umfangreichen gruppierten Columnstore-Index zu löschen und mit einem anderen Namen neu zu erstellen, nimmt viel Zeit in Anspruch.

In diesem Beispiel wird auf den gruppierten Columnstore-Index cci_xDimProduct aus dem vorherigen Beispiel verwiesen. Der gruppierte Columnstore-Index cci_xDimProduct wird gelöscht und anschließend der gruppierte Columnstore-Index mit dem Namen mycci_xDimProduct neu erstellt.

--For illustration purposes, drop the clustered columnstore index.
--The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xDimProduct;
  
--Create a clustered index with a new name, mycci_xDimProduct.
CREATE CLUSTERED COLUMNSTORE INDEX mycci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = OFF );

D: Konvertieren einer Columnstore-Tabelle in eine Rowstore-Tabelle mit einem gruppierten Index

Es kann vorkommen, dass Sie einen gruppierten Columnstore-Index löschen und anschließend einen gruppierten Index erstellen möchten. Beim Löschen eines gruppierten Columnstore-Index wird die Tabelle in das Rowstore-Format geändert. In diesem Beispiel wird eine Columnstore-Tabelle in eine Rowstore-Tabelle mit einem gruppierten Index mit demselben Namen konvertiert. Dabei gehen keine Daten verloren. Alle Daten werden zur Rowstore-Tabelle, und die aufgelisteten Spalten werden zu den Schlüsselspalten im gruppierten Index.

--Drop the clustered columnstore index and create a clustered rowstore index.
--All of the columns are stored in the rowstore clustered index.
--The columns listed are the included columns in the index.
CREATE CLUSTERED INDEX cci_xDimProduct
ON xdimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey, WeightUnitMeasureCode)
WITH ( DROP_EXISTING = ON);

E. Konvertieren einer Columnstore-Tabelle in einen Rowstore-Heap

Verwenden Sie DROP INDEX , um den Gruppierten Spaltenspeicherindex abzulegen und die Tabelle in einen Rowstore-Heap zu konvertieren. In diesem Beispiel wird die Tabelle „cci_xDimProduct“ in einen Rowstore-Heap konvertiert. Die Tabelle wird weiterhin verteilt, jedoch als Heap gespeichert.

--Drop the clustered columnstore index. The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xdimProduct;

F. Erstellen eines sortierten gruppierten Columnstore-Index für eine Tabelle ohne Index

Ein unsortierter Columnstore-Index deckt standardmäßig alle Spalten ab, ohne eine Spaltenliste angeben zu müssen. Mit einem sortierten Columnstore-Index können Sie die Reihenfolge der Spalten angeben. Die Liste muss nicht alle Spalten enthalten.

Weitere Informationen finden Sie unter Leistungsoptimierung mit geordneten gruppierten Columnstore-Indizes.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE);

G. Konvertieren eines gruppierten Columnstore-Index in einen sortierten gruppierten Columnstore-Index

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE)
WITH (DROP_EXISTING = ON);

H. Hinzufügen einer Spalte zur Reihenfolge eines sortierten gruppierten Columnstore-Index

Sie können eine Reihenfolge für die Spalten in einem Columnstore-Index angeben. Der ursprüngliche sortierte gruppierte Columnstore-Index wurde nur nach der Spalte SHIPDATE sortiert. Im folgenden Beispiel wird der Sortierung die Spalte „PRODUCTKEY“ hinzugefügt. Die Verfügbarkeit des sortierten Columnstore-Index finden Sie unter Columnstore-Indizes: Übersicht.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE, PRODUCTKEY)
WITH (DROP_EXISTING = ON);

I. Ändern der Ordnungszahl von sortierten Spalten

Der ursprüngliche sortierte gruppierte Columnstore-Index wurde nach SHIPDATE, PRODUCTKEY sortiert. Im folgenden Beispiel wird die Sortierung in PRODUCTKEY, SHIPDATE geändert. Die Verfügbarkeit des sortierten Columnstore-Index finden Sie unter Columnstore-Indizes: Übersicht.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (PRODUCTKEY,SHIPDATE)
WITH (DROP_EXISTING = ON);

J. Erstellen eines sortierten gruppierten Columnstore-Index

Sie können einen gruppierten Columnstore-Index mit Sortierschlüsseln erstellen. Beim Erstellen eines gruppierten Spaltenspeicherindexes sollten Sie den Abfragehinweis MAXDOP = 1 auf maximale Sortierqualität und kürzeste Dauer anwenden. Die Verfügbarkeit des sortierten Columnstore-Index finden Sie unter Columnstore-Indizes: Übersicht.

CREATE CLUSTERED COLUMNSTORE INDEX [OrderedCCI] ON dbo.FactResellerSalesPartCategoryFull
ORDER (EnglishProductSubcategoryName, EnglishProductName)
WITH (MAXDOP = 1, DROP_EXISTING = ON);

Zugehöriger Inhalt