CREATE INDEX (Transact-SQL)

2025-04-09

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Tworzy indeks relacyjny w tabeli lub widoku. Nazywany również indeksem magazynu wierszy, ponieważ jest to indeks klastrowany lub nieklastrowany drzewo B. Indeks magazynu wierszy można utworzyć przed utworzeniem danych w tabeli. Użyj indeksu magazynu wierszy, aby zwiększyć wydajność zapytań, zwłaszcza gdy zapytania wybierają określoną kolumnę lub wymagają sortowania wartości w określonej kolejności.

Nuta

W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach magazynu wierszy aparat bazy danych implementuje drzewo B+ . Nie dotyczy to indeksów magazynu kolumn ani indeksów w tabelach zoptymalizowanych pod kątem pamięci. Aby uzyskać więcej informacji, zobacz architektura usługi SQL Server i architektura indeksu usługi Azure SQL oraz przewodnik projektowania.

Usługa Azure Synapse Analytics i system platformy analizy (PDW) obecnie nie obsługują unikatowych ograniczeń. Wszystkie przykłady odwołujące się do unikatowych ograniczeń mają zastosowanie tylko do programu SQL Server, usługi Azure SQL Database i usługi Azure SQL Managed Instance.

Aby uzyskać informacje na temat wytycznych dotyczących projektowania indeksów, zapoznaj się z przewodnikiem projektowania indeksów programu SQL Server.

przykłady :

Tworzenie indeksu nieklastrowanego w tabeli lub widoku
```
CREATE INDEX index1 ON schema1.table1 (column1);
```
Tworzenie indeksu klastrowanego w tabeli i używanie 3-częściowej nazwy tabeli
```
CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
```

Utwórz indeks nieklastrowany z unikatowym ograniczeniem i określ kolejność sortowania

CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);

scenariusz klucza :

Począwszy od programu SQL Server 2016 (13.x), w usłudze Azure SQL Database i w usłudze Azure SQL Managed Instance, możesz użyć indeksu nieklastrowanego w indeksie magazynu kolumn, aby poprawić wydajność zapytań magazynowania danych. Aby uzyskać więcej informacji, zobacz Indeksy magazynu kolumn — magazyn danych.

Aby uzyskać dodatkowe typy indeksów, zobacz:

Transact-SQL konwencje składni

Składnia

Składnia dla programu SQL Server, usługi Azure SQL Database, wystąpienia zarządzanego Azure SQL

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<object> ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

<filter_predicate> ::=
    <conjunct> [ AND ] [ ...n ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Indeks relacyjny zgodny z poprzednimi wersjami

Ważny

Struktura składni indeksu relacyjnego zgodna z poprzednimi wersjami zostanie usunięta w przyszłej wersji programu SQL Server. Unikaj używania tej struktury składni w nowych pracach programistycznych i zaplanuj modyfikowanie aplikacji, które obecnie korzystają z tej funkcji. Zamiast tego użyj struktury składni określonej w <relational_index_option>.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ]
    table_or_view_name
}

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Składnia dla usług Azure Synapse Analytics i Parallel Data Warehouse


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


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

Argumenty

NIEPOWTARZALNY

Tworzy unikatowy indeks w tabeli lub widoku. Unikatowy indeks to jeden, w którym żadne dwa wiersze nie mogą mieć tej samej wartości klucza indeksu.

Aparat bazy danych nie zezwala na tworzenie unikatowego indeksu w kolumnach, które zawierają już zduplikowane wartości, niezależnie od tego, czy IGNORE_DUP_KEY ustawiono wartość ON. Jeśli ta próba zostanie podjęta, aparat bazy danych wyświetli komunikat o błędzie. Przed utworzeniem unikatowego indeksu w kolumnie lub kolumnach należy usunąć zduplikowane wartości.

Ograniczenie UNIQUE traktuje NULL jako wartość. Jeśli kolumna jest dopuszczana do wartości null, a UNIQUE ograniczenie istnieje w kolumnie, dozwolony jest co najwyżej jeden wiersz z wartością NULL .

KLASTROWANY

Tworzy indeks, w którym kolejność sortowania określona dla kolumn klucza indeksu określa kolejność stron w strukturze indeksu na dysku. Wiersze na stronach w dolnej części lub liściu na poziomie indeksu klastrowanego zawsze zawierają wszystkie kolumny tabeli. Wiersze na stronach na wyższych poziomach indeksu zawierają tylko kolumny klucza.

Tabela może zawierać tylko jeden indeks klastrowany. Jeśli indeks klastrowany istnieje w tabeli, zawiera wszystkie dane w tabeli. Tabela bez indeksu klastrowanego jest nazywana stertą.

Widok z unikatowym indeksem klastrowanym jest nazywany widokiem indeksowanym. Widok indeksowany może mieć tylko jeden indeks klastrowany. Utworzenie unikatowego indeksu klastrowanego w widoku fizycznie zmaterializuje widok. Aby można było zdefiniować inne indeksy w tym samym widoku, należy utworzyć unikatowy indeks klastrowany. Aby uzyskać więcej informacji, zobacz Tworzenie indeksowanych widoków.

Utwórz indeks klastrowany przed utworzeniem wszystkich indeksów nieklastrowanych. Istniejące indeksy nieklastrowane w tabelach są odbudowywane po utworzeniu indeksu klastrowanego, co jest operacją intensywnie obciążaną zasobami, jeśli tabela jest duża.

Jeśli CLUSTERED nie zostanie określony, zostanie utworzony indeks nieklastrowany.

Nuta

Ponieważ indeks klastrowany zawiera wszystkie dane w tabeli, utworzenie indeksu klastrowanego i efektywne użycie ON partition_scheme_name klauzuli or ON filegroup_name powoduje przeniesienie tabeli z grupy plików, w której tabela została utworzona do nowego schematu partycji lub grupy plików. Przed utworzeniem tabel lub indeksów w określonych grupach plików sprawdź, które grupy plików są dostępne i czy mają wystarczającą ilość pustego miejsca dla indeksu.

W niektórych przypadkach utworzenie indeksu klastrowanego może włączyć wcześniej wyłączone indeksy. Aby uzyskać więcej informacji, zobacz Włączanie indeksów i ograniczeń oraz Wyłączanie indeksów i ograniczeń.

NIEKLASTROWANE

Tworzy indeks, w którym kolejność sortowania określona dla kolumn klucza indeksu określa kolejność stron w strukturze indeksu na dysku. W przeciwieństwie do indeksu klastrowanego wiersze na stronach na poziomie liścia indeksu nieklastrowanego zawierają tylko kolumny klucza indeksu. Opcjonalnie można uwzględnić podzbiór kolumn innych niż klucz przy użyciu klauzuli INCLUDE .

Każda tabela może mieć maksymalnie 999 indeksów nieklastrowanych, niezależnie od sposobu tworzenia indeksów: niejawnie z PRIMARY KEY ograniczeniami i UNIQUE lub jawnie za CREATE INDEXpomocą polecenia .

W przypadku widoków indeksowanych indeksy nieklastrowane można tworzyć tylko w widoku, który ma już zdefiniowany unikatowy indeks klastrowany.

Jeśli nie określono inaczej, domyślny typ indeksu jest nieklastrowany.

index_name

Nazwa indeksu. Nazwy indeksów muszą być unikatowe w obrębie tabeli lub widoku, ale nie muszą być unikatowe w bazie danych. Nazwy indeksów muszą być zgodne z regułami identyfikatorów .

kolumny

Kolumna lub kolumny, na których opiera się indeks. Określ co najmniej dwie nazwy kolumn, aby utworzyć indeks złożony dla połączonych wartości w określonych kolumnach. Wyświetl listę kolumn, które mają być uwzględnione w indeksie złożonym w kolejności sortowania, wewnątrz nawiasów po table_or_view_name.

Maksymalnie 32 kolumny można połączyć w jeden klucz indeksu złożonego. Wszystkie kolumny w kluczu indeksu złożonego muszą znajdować się w tej samej tabeli lub widoku. Maksymalny dozwolony rozmiar połączonych wartości indeksu to 900 bajtów dla indeksu klastrowanego lub 1700 dla indeksu nieklastrowanego. Limity to 16 kolumn i 900 bajtów dla wersji przed usługami SQL Database i SQL Server 2016 (13.x).

Kolumny z dużych typów danych obiektów (LOB) ntext, tekst, varchar(max), nvarchar(max), varbinary(max), xmllub obrazu obrazu nie można określić jako kolumn kluczowych dla indeksu. Ponadto definicja indeksowanego widoku nie może zawierać kolumn tekstowych, tekstowych ani obrazów , nawet jeśli nie są przywoływane w instrukcji CREATE INDEX .

Indeksy można tworzyć w kolumnach typu zdefiniowanego przez użytkownika środowiska CLR, jeśli typ obsługuje kolejność binarną. Można również tworzyć indeksy dla obliczonych kolumn, które są zdefiniowane jako wywołania metod poza kolumną typu zdefiniowanego przez użytkownika, o ile metody są oznaczone deterministyczną i nie wykonują operacji dostępu do danych. Aby uzyskać więcej informacji na temat indeksowania kolumn typów zdefiniowanych przez użytkownika clR, zobacz CLR user-defined types (Typy zdefiniowane przez użytkownika środowiska CLR).

[ ASC | DESC ]

Określa kierunek sortowania rosnącego lub malejącego dla określonej kolumny indeksu. Wartość domyślna to ASC.

INCLUDE ( kolumna [ ,... n ] )

Określa kolumny inne niż klucz, które mają zostać dodane do poziomu liścia indeksu nieklastrowanego. Indeks nieklastrowany może być unikatowy lub nieukondycyjny.

Nazwy kolumn nie mogą być powtarzane na INCLUDE liście i nie można ich używać jednocześnie jako kolumn kluczy i innych niż klucz. Indeksy nieklastrowane zawsze niejawnie zawierają kolumny indeksu klastrowanego, jeśli indeks klastrowany jest zdefiniowany w tabeli. Aby uzyskać więcej informacji, zobacz Tworzenie indeksów z dołączonymi kolumnami.

Wszystkie typy danych są dozwolone z wyjątkiem tekstu, ntexti obrazu. Począwszy od programu SQL Server 2012 (11.x), w usłudze Azure SQL Database i w usłudze Azure SQL Managed Instance, jeśli którakolwiek z określonych kolumn innych niż klucz to varchar(max), nvarchar(max)lub varbinary(max), indeks można skompilować lub skompilować przy użyciu ONLINE opcji .

Kolumny obliczane, które są deterministyczne i precyzyjne lub nieprecyzyjne, mogą zawierać kolumny. Obliczone kolumny pochodzące z obrazu, ntextu, tekstu, varchar(max), nvarchar(max), varbinary(max)i typów danych XML mogą być uwzględniane tak długo, jak typ danych kolumny obliczeniowej jest dozwolony jako dołączona kolumna. Aby uzyskać więcej informacji, zobacz Indeksy na obliczanych kolumnach.

Aby uzyskać informacje na temat tworzenia indeksu XML, zobacz CREATE XML INDEX.

GDZIE <filter_predicate>

Tworzy filtrowany indeks, określając wiersze do uwzględnienia w indeksie. Indeks filtrowany musi być indeksem nieklastrowanym w tabeli. Tworzy przefiltrowane statystyki dla wierszy danych w filtrowanym indeksie.

Predykat filtru używa prostej logiki porównania i nie może odwoływać się do kolumny obliczeniowej, kolumny typu danych zdefiniowanych przez użytkownika (UDT), kolumny typu danych przestrzennych lub kolumny typu danych hierarchyid . Porównania z NULL literałami używającymi operatorów porównania są niedozwolone. Zamiast tego użyj operatorów IS NULL i IS NOT NULL.

Oto kilka przykładów predykatów filtrów dla tabeli Production.BillOfMaterials:

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Indeksy filtrowane nie mają zastosowania do indeksów XML i indeksów pełnotekstowych. W przypadku UNIQUE indeksów tylko wybrane wiersze muszą mieć unikatowe wartości indeksu. Filtrowane indeksy nie zezwalają na opcję IGNORE_DUP_KEY.

ON partition_scheme_name ( column_name )

Określa schemat partycji, który definiuje grupy plików, na których są mapowane partycje indeksu partycjonowanego. Schemat partycji musi istnieć w bazie danych, wykonując CREATE PARTITION SCHEME lub ALTER PARTITION SCHEME. column_name określa kolumnę partycjonowania dla indeksu. Ta kolumna musi być zgodna z typem danych, długością i dokładnością argumentu funkcji partycji, która partition_scheme_name jest używana. column_name nie jest ograniczona do kolumn w definicji indeksu. Każdą kolumnę w tabeli podstawowej można określić, z wyjątkiem partycjonowania unikatowego indeksu, column_name należy wybrać spośród tych, które są używane jako unikatowy klucz. To ograniczenie umożliwia aparatowi bazy danych weryfikowanie unikatowości wartości kluczy tylko w ramach jednej partycji.

Nuta

Podczas partycjonowania indeksu klastrowanego bez unikatowości aparat bazy danych domyślnie dodaje kolumnę partycjonowania do listy kluczy indeksu klastrowanego, jeśli nie została jeszcze określona. Podczas partycjonowania indeksu innego niż unikatowy, nieklastrowany aparat bazy danych dodaje kolumnę partycjonowania jako kolumnę inną niż klucz (dołączona) indeksu, jeśli nie została jeszcze określona.

Jeśli partition_scheme_name lub grupa plików nie zostanie określona, a tabela zostanie podzielona na partycje, indeks zostanie umieszczony w tym samym schemacie partycji, używając tej samej kolumny partycjonowania, co tabela bazowa.

Nuta

Nie można określić schematu partycjonowania w indeksie XML. Jeśli tabela podstawowa jest partycjonowana, indeks XML używa tego samego schematu partycji co tabela.

Aby uzyskać więcej informacji na temat partycjonowania indeksów, tabel partycjonowanych i indeksów.

ON filegroup_name

Tworzy określony indeks w określonej grupie plików. Jeśli nie określono żadnej lokalizacji, a tabela lub widok nie są partycjonowane, indeks używa tej samej grupy plików co podstawowa tabela lub widok. Grupa plików musi już istnieć.

WŁĄCZONE [ustawienie domyślne]

Tworzy określony indeks w tej samej grupie plików lub schemacie partycji co tabela lub widok.

defaultTermin , w tym kontekście, nie jest słowem kluczowym. Jest to identyfikator grupy plików lub schematu partycjonowanego tabeli lub widoku i musi być rozdzielany, tak jak w tabeli ON "default" lub ON [default]. Jeśli "default" jest określona QUOTED_IDENTIFIER , opcja musi być ON dla bieżącej sesji. Jest to ustawienie domyślne. Aby uzyskać więcej informacji, zobacz SET QUOTED_IDENTIFIER.

Nuta

W kontekście elementu i [default] nie wskazują domyślnej CREATE INDEX"default" grupy plików bazy danych. Wskazują one grupę plików lub schemat partycji używany przez tabelę podstawową lub widok. Różni się to od CREATE TABLE, gdzie "default" i [default] umieszcza tabelę w domyślnej grupie plików bazy danych.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

Określa umieszczanie danych FILESTREAM dla tabeli podczas tworzenia klastrowanego indeksu. Klauzula FILESTREAM_ON umożliwia przenoszenie danych FILESTREAM do innej grupy plików FILESTREAM lub schematu partycji.

Filestream_filegroup_name jest nazwą grupy plików FILESTREAM. Grupa plików musi mieć jeden plik zdefiniowany dla grupy plików przy użyciu instrukcji CREATE DATABASE lub ALTER DATABASE; w przeciwnym razie zostanie zgłoszony błąd.

Jeśli tabela jest partycjonowana, klauzula FILESTREAM_ON musi zostać dołączona i musi określić schemat partycji grup plików FILESTREAM, które używają tej samej funkcji partycji i kolumn partycji co schemat partycji dla tabeli. W przeciwnym razie zostanie zgłoszony błąd.

Jeśli tabela nie jest partycjonowana, kolumna FILESTREAM nie może być partycjonowana. Dane FILESTREAM dla tabeli muszą być przechowywane w jednej grupie plików określonej w klauzuli FILESTREAM_ON.

FILESTREAM_ON NULL można określić w instrukcji CREATE INDEX, jeśli tworzony jest indeks klastrowany, a tabela nie zawiera kolumny FILESTREAM.

Aby uzyskać więcej informacji, zobacz FILESTREAM (SQL Server).

<obiektu>::=

W pełni kwalifikowany lub nieuprzykwalifikowany obiekt do indeksowania.

database_name

Nazwa bazy danych.

schema_name

Nazwa schematu, do którego należy tabela lub widok.

table_or_view_name

Nazwa tabeli lub widoku do indeksowania.

Aby utworzyć indeks w widoku, należy zdefiniować widok za pomocą polecenia SCHEMABINDING. Przed utworzeniem indeksu nieklastrowanego należy utworzyć unikatowy indeks klastrowany w widoku. Aby uzyskać więcej informacji na temat indeksowanych widoków, zobacz Uwagi.

Począwszy od programu SQL Server 2016 (13.x), obiekt może być tabelą przechowywaną z klastrowanym indeksem magazynu kolumn.

Usługa Azure SQL Database obsługuje trzyczęściowy format nazwy <database_name>.<schema_name>.<object_name>, gdy <database_name> jest bieżącą nazwą bazy danych lub <database_name> jest tempdb i <object_name> rozpoczyna się od # lub ##. Jeśli nazwa schematu jest dbo, można pominąć <schema_name>.

<relational_index_option>::=

Określa opcje do użycia podczas tworzenia indeksu.

PAD_INDEX = { ON | WYŁ. }

Określa dopełnienie indeksu. Wartość domyślna to OFF.

NA

Procent wolnego miejsca określonego przez współczynnik wypełnienia jest stosowany do stron na poziomie pośrednim indeksu. Jeśli FILLFACTOR nie zostanie określona w tym samym czasie, PAD_INDEX zostanie ustawiona na ON, zostanie użyta wartość współczynnika wypełnienia w sys.indexes.
OD

Strony na poziomie pośrednim są wypełniane w pobliżu pojemności, pozostawiając wystarczającą ilość miejsca dla co najmniej jednego wiersza maksymalnego rozmiaru indeksu, biorąc pod uwagę zestaw kluczy na stronach pośrednich. Dzieje się tak również, jeśli PAD_INDEX jest ustawiona na ON, ale nie określono współczynnika wypełnienia.

Opcja jest przydatna PAD_INDEX tylko wtedy, gdy FILLFACTOR jest określona, ponieważ PAD_INDEX używa wartości procentowej określonej przez FILLFACTOR. Jeśli wartość procentowa określona dla FILLFACTOR parametru nie jest wystarczająco duża, aby zezwolić na jeden wiersz, aparat bazy danych wewnętrznie zastępuje wartość procentową, aby umożliwić minimalną wartość. Liczba wierszy na stronie indeksu pośredniego nigdy nie jest mniejsza niż dwie, niezależnie od tego, jak niska jest wartość FILLFACTOR.

W składni zgodnej z poprzednimi wersjami WITH PAD_INDEX jest równoważna WITH PAD_INDEX = ON.

FILLFACTOR = fillfactor

Określa wartość procentową wskazującą, jak pełny aparat bazy danych powinien ustawić poziom liścia każdej strony indeksu podczas tworzenia lub odbudowy indeksu. Wartość fillfactor musi być wartością całkowitą z zakresu od 1 do 100. Wartości współczynnika wypełnienia 0 i 100 są takie same we wszystkich aspektach. Jeśli fillfactor wynosi 100, aparat bazy danych tworzy indeksy ze stronami liści wypełnionymi pojemnością.

Ustawienie FILLFACTOR ma zastosowanie tylko podczas tworzenia lub odbudowy indeksu. Aparat bazy danych nie zachowuje dynamicznie określonego procentu pustego miejsca na stronach.

Aby wyświetlić ustawienie współczynnika wypełnienia, użyj fill_factor kolumny w widoku katalogu sys.indexes .

Ważny

Utworzenie indeksu z FILLFACTOR mniejszym niż 100 zwiększa ilość miejsca do magazynowania zajmowanego przez aparat bazy danych, ponieważ aparat bazy danych redystrybuuje dane zgodnie z współczynnikiem wypełnienia podczas tworzenia lub odbudowy indeksu.

Aby uzyskać więcej informacji, zobacz Określanie współczynnika wypełnienia dla indeksu.

SORT_IN_TEMPDB = { ON | WYŁ. }

Określa, czy mają być przechowywane tymczasowe wyniki sortowania w tempdb. Wartość domyślna to OFF z wyjątkiem warstwy Hiperskala usługi Azure SQL Database. W przypadku wszystkich operacji kompilacji indeksu w warstwie Hiperskala SORT_IN_TEMPDB jest zawsze ON, chyba że jest używana kompilacja indeksu z możliwością wznowienia. W przypadku wznawianych kompilacji indeksu SORT_IN_TEMPDB jest zawsze OFF.

NA

Wyniki sortowania pośredniego używane do kompilowania indeksu są przechowywane w tempdb. Może to skrócić czas wymagany do utworzenia indeksu. Zwiększa to jednak ilość miejsca na dysku używanego podczas kompilacji indeksu.
OD

Wyniki sortowania pośredniego są przechowywane w tej samej bazie danych co indeks.

Oprócz miejsca wymaganego w bazie danych użytkownika do utworzenia indeksu tempdb musi mieć mniej więcej taką samą ilość miejsca, aby przechowywać wyniki sortowania pośredniego. Aby uzyskać więcej informacji, zobacz SORT_IN_TEMPDB opcja indeksów.

W składni zgodnej z poprzednimi wersjami WITH SORT_IN_TEMPDB jest równoważna WITH SORT_IN_TEMPDB = ON.

IGNORE_DUP_KEY = { ON | WYŁ. }

Określa odpowiedź na błąd, gdy operacja wstawiania próbuje wstawić zduplikowane wartości klucza do unikatowego indeksu. Opcja IGNORE_DUP_KEY dotyczy tylko operacji wstawiania po utworzeniu lub ponownym utworzeniu indeksu. Opcja nie ma wpływu podczas wykonywania CREATE INDEX, ALTER INDEXlub UPDATE. Wartość domyślna to OFF.

NA

Komunikat ostrzegawczy występuje, gdy zduplikowane wartości klucza są wstawione do unikatowego indeksu. Tylko wiersze naruszające ograniczenie unikatowości nie są wstawione.
OD

Komunikat o błędzie występuje, gdy zduplikowane wartości klucza są wstawione do unikatowego indeksu. Cała INSERT instrukcja zostanie wycofana.

IGNORE_DUP_KEY nie można ustawić na ON dla indeksów utworzonych w widoku, indeksach innych niż unikatowe, indeksach XML, indeksach przestrzennych i filtrowanych indeksach.

Aby wyświetlić ustawienie IGNORE_DUP_KEY dla indeksu, użyj kolumny ignore_dup_key w widoku katalogu sys.indexes.

W składni zgodnej z poprzednimi wersjami WITH IGNORE_DUP_KEY jest równoważna WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | WYŁ.}

Określa, czy statystyki są ponownie obliczane. Wartość domyślna to OFF.

NA

Nieaktualne statystyki nie są automatycznie ponownie obliczane.
OD

Automatyczne aktualizowanie statystyk jest włączone.

Aby przywrócić automatyczne aktualizowanie statystyk, ustaw STATISTICS_NORECOMPUTE na WYŁ. lub wykonaj UPDATE STATISTICS bez klauzuli NORECOMPUTE.

Ostrzeżenie

Jeśli wyłączysz automatyczną ponowną kompilację statystyk przez ustawienie STATISTICS_NORECOMPUTE = ON, możesz uniemożliwić optymalizatorowi zapytań wybranie optymalnych planów wykonywania zapytań dotyczących tabeli.

Ustawienie STATISTICS_NORECOMPUTE na wartość ON nie uniemożliwia aktualizacji statystyk indeksu występujących podczas operacji ponownego kompilowania indeksu.

W składni zgodnej z poprzednimi wersjami WITH STATISTICS_NORECOMPUTE jest równoważna WITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = { ON | WYŁ. }

Dotyczy: SQL Server 2014 (12.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Po ONutworzeniu statystyk na partycję są tworzone statystyki. Gdy OFFdrzewo statystyk zostanie usunięte, a program SQL Server ponownie oblicza statystyki. Wartość domyślna to OFF.

Jeśli statystyki poszczególnych partycji nie są obsługiwane, opcja jest ignorowana i jest generowane ostrzeżenie. Statystyki przyrostowe nie są obsługiwane w następujących przypadkach:

Statystyki utworzone z indeksami, które nie są zgodne z tabelą podstawową.
Statystyki utworzone w przypadku pomocniczych baz danych z możliwością odczytu zawsze włączone.
Statystyki utworzone w bazach danych tylko do odczytu.
Statystyki utworzone na podstawie filtrowanych indeksów.
Statystyki utworzone w widokach.
Statystyki utworzone w tabelach wewnętrznych.
Statystyki utworzone za pomocą indeksów przestrzennych lub indeksów XML.

DROP_EXISTING = { ON | WYŁ. }

Jest opcją porzucania i ponownego kompilowania istniejącego indeksu klastrowanego lub nieklastrowanego ze zmodyfikowanymi specyfikacjami kolumn i zachowywania tej samej nazwy indeksu. Wartość domyślna to OFF.

NA

Określa, aby usunąć i ponownie skompilować istniejący indeks, który musi mieć taką samą nazwę jak parametr index_name.
OD

Określa, że nie należy usuwać i ponownie kompilować istniejący indeks. Program SQL Server wyświetla błąd, jeśli określona nazwa indeksu już istnieje.

Za pomocą DROP_EXISTINGmożna zmienić:

Indeks nieklastrowanego magazynu wierszy do klastrowanego indeksu magazynu wierszy.

Za pomocą DROP_EXISTINGnie można zmienić:

Indeks klastrowanego magazynu wierszy do nieklastrowanego indeksu magazynu wierszy.
Indeks klastrowanego magazynu kolumn do dowolnego typu indeksu magazynu wierszy.

W składni zgodnej z poprzednimi wersjami WITH DROP_EXISTING jest równoważna WITH DROP_EXISTING = ON.

ONLINE = { ON | WYŁ. }

Określa, czy tabele bazowe i skojarzone indeksy są dostępne dla zapytań i modyfikacji danych podczas operacji indeksu. Wartość domyślna to OFF.

Ważny

Operacje indeksowania online nie są dostępne w każdej wersji programu Microsoft SQL Server. Aby uzyskać listę funkcji obsługiwanych przez wersje programu SQL Server, zobacz Editions i obsługiwane funkcje programu SQL Server 2022.

NA

Długoterminowe blokady tabeli nie są przechowywane przez czas trwania operacji indeksu. W fazie głównej operacji indeksowania blokada intencji udostępnionej (IS) jest przechowywana w tabeli źródłowej. Umożliwia to kontynuowanie zapytań lub aktualizacji podstawowej tabeli i indeksów. Na początku operacji blokada współużytkowanego (S) jest przechowywana w obiekcie źródłowym przez krótki czas. Na końcu operacji przez krótki czas nabyty jest blokada współużytkowana (S) na obiekcie, jeśli tworzony jest indeks nieklastrowany. Modyfikacja schematu (Sch-M) jest uzyskiwana podczas tworzenia lub porzucania klastrowanego indeksu w trybie online i odbudowy indeksu klastrowanego lub nieklastrowanego. ONLINE nie można ustawić na ON podczas tworzenia indeksu w lokalnej tabeli tymczasowej.

Nuta

Możesz użyć opcji WAIT_AT_LOW_PRIORITY, aby zmniejszyć lub uniknąć blokowania podczas operacji indeksowania online. Aby uzyskać więcej informacji, zobacz WAIT_AT_LOW_PRIORITY z operacjami indeksowania online.
OD

Blokady tabeli są stosowane do czasu trwania operacji indeksu. Operacja indeksu w trybie offline, która tworzy, kompiluje lub usuwa indeks klastrowany, przestrzenny lub XML albo ponownie kompiluje lub usuwa indeks nieklastrowany, uzyskuje modyfikację schematu (Sch-M) blokady w tabeli. Zapobiega to dostępowi wszystkich użytkowników do tabeli bazowej przez czas trwania operacji. Operacja indeksu w trybie offline, która tworzy indeks nieklastrowany, początkowo uzyskuje blokadę współużytkowaną (S) w tabeli. Zapobiega to modyfikacjom podstawowej definicji tabeli, ale umożliwia odczytywanie i modyfikowanie danych w tabeli, gdy kompilacja indeksu jest w toku.

Aby uzyskać więcej informacji, zobacz Wykonywanie operacji indeksowania w trybie online i wytyczne dotyczące operacji indeksowania online.

Indeksy, w tym indeksy w globalnych tabelach tymczasowych, można tworzyć w trybie online, z wyjątkiem następujących przypadków:

Indeks XML
Indeksowanie w lokalnej tabeli tymczasowej
Początkowy unikatowy indeks klastrowany w widoku
Wyłączone indeksy klastrowane
Klastrowane indeksy magazynu kolumn w programie SQL Server 2017 (14.x)) i starszych wersjach
Nieklastrowane indeksy magazynu kolumn w programie SQL Server 2016 (13.x)) i starszych wersjach
Indeks klastrowany, jeśli tabela bazowa zawiera typy danych BIZNESOWYCH (obraz, ntext, tekst) i typy danych przestrzennych
varchar(max) i kolumny varbinary(max) nie mogą być częścią klucza indeksu. W programie SQL Server (począwszy od programu SQL Server 2012 (11.x)) w usłudze Azure SQL Database i w usłudze Azure SQL Managed Instance, gdy tabela zawiera kolumny varchar(max) lub varbinary(max), indeks klastrowany zawierający inne kolumny można skompilować lub odtworzyć przy użyciu ONLINE tej opcji.
Indeksy nieklastrowane w tabeli z klastrowanym indeksem magazynu kolumn

Aby uzyskać więcej informacji, zobacz Jak działają operacje indeksowania online.

WZNAWIANIE = { WŁ. | WYŁ. }

Dotyczy: SQL Server 2019 (15.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Określa, czy operacja indeksu online jest wznawiana. Aby uzyskać więcej informacji, zobacz Temat Operacje indeksowania możliwe do wznowienia i Zagadnienia dotyczące indeksu z możliwością wznowienia.

NA

Operacja indeksu jest wznawiana.
OD

Operacja indeksu nie jest wznawiana.

MAX_DURATION = czas [MINUT] używany z `RESUMABLE = ON` (wymaga `ONLINE = ON`)

Dotyczy: SQL Server 2019 (15.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Określa, jak długo w minutach jest wykonywana operacja indeksu możliwego do wznowienia przed wstrzymaniem.

ALLOW_ROW_LOCKS = { ON | WYŁ. }

Określa, czy blokady wierszy są dozwolone. Wartość domyślna to ON.

NA

Blokady wierszy są dozwolone podczas uzyskiwania dostępu do indeksu. Aparat bazy danych określa, kiedy są używane blokady wierszy.
OD

Blokady wierszy nie są używane.

ALLOW_PAGE_LOCKS = { ON | WYŁ. }

Określa, czy blokady strony są dozwolone. Wartość domyślna to ON.

NA

Blokady stron są dozwolone podczas uzyskiwania dostępu do indeksu. Aparat bazy danych określa, kiedy są używane blokady strony.
OD

Blokady stron nie są używane.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | WYŁ. }

Dotyczy: SQL Server 2019 (15.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Określa, czy należy zoptymalizować, aby uniknąć rywalizacji o wstawianie ostatniej strony. Wartość domyślna to OFF. Aby uzyskać więcej informacji, zobacz sekcję Sekwencyjne klucze .

MAXDOP = max_degree_of_parallelism

Zastępuje maksymalny stopień równoległości opcji konfiguracji dla operacji indeksowania. Aby uzyskać więcej informacji, zobacz Configure the max degree of parallelism Server Configuration Option. Użyj MAXDOP, aby ograniczyć stopień równoległości i wynikowe użycie zasobów dla operacji kompilacji indeksu.

max_degree_of_parallelism może to być:

1

Pomija generowanie planu równoległego.
>1

Ogranicza maksymalny stopień równoległości używany w operacji indeksowania równoległego do określonej liczby lub mniejszej na podstawie bieżącego obciążenia systemu.
0 (wartość domyślna)

Używa stopnia równoległości określonego na poziomie serwera, bazy danych lub grupy obciążenia, chyba że zostanie zmniejszona na podstawie bieżącego obciążenia systemu.

Aby uzyskać więcej informacji, zobacz Konfigurowanie operacji indeksowania równoległego.

Nuta

Operacje indeksowania równoległego nie są dostępne w każdej wersji programu Microsoft SQL Server. Aby uzyskać listę funkcji obsługiwanych przez wersje programu SQL Server, zobacz Editions i obsługiwane funkcje programu SQL Server 2022.

DATA_COMPRESSION

Określa opcję kompresji danych dla określonego indeksu, numeru partycji lub zakresu partycji. Opcje są następujące:

ŻADEN

Indeks lub określone partycje nie są kompresowane. Nie dotyczy to indeksów magazynu kolumn.
SZEREG

Indeks lub określone partycje są kompresowane przy użyciu kompresji wierszy. Nie dotyczy to indeksów magazynu kolumn.
STRONA

Indeks lub określone partycje są kompresowane przy użyciu kompresji strony. Nie dotyczy to indeksów magazynu kolumn.
MAGAZYN KOLUMN

Dotyczy: SQL Server 2014 (12.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Dotyczy tylko indeksów magazynu kolumn, w tym zarówno nieklastrowanych magazynów kolumn, jak i klastrowanych indeksów magazynu kolumn.
COLUMNSTORE_ARCHIVE

Dotyczy: SQL Server 2014 (12.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Dotyczy tylko indeksów magazynu kolumn, w tym zarówno nieklastrowanych magazynów kolumn, jak i klastrowanych indeksów magazynu kolumn. COLUMNSTORE_ARCHIVE dodatkowo kompresuje określoną partycję do mniejszego rozmiaru. Może to być używane do archiwizacji lub w innych sytuacjach, które wymagają mniejszego rozmiaru magazynu i mogą pozwolić na więcej czasu na przechowywanie i pobieranie.

Aby uzyskać więcej informacji na temat kompresji, zobacz Kompresja danych.

XML_COMPRESSION

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Określa opcję kompresji XML dla określonego indeksu, który zawiera co najmniej jedną kolumnę xml typu danych. Opcje są następujące:

NA

Indeks lub określone partycje są kompresowane przy użyciu kompresji XML.
OD

Indeks lub określone partycje nie są kompresowane przy użyciu kompresji XML.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

Określa partycje, do których mają zastosowanie ustawienia DATA_COMPRESSION lub XML_COMPRESSION. Jeśli indeks nie jest partycjonowany, argument ON PARTITIONS generuje błąd. Jeśli nie podano klauzuli ON PARTITIONS, opcja DATA_COMPRESSION lub XML_COMPRESSION dotyczy wszystkich partycji indeksu partycjonowanego.

<partition_number_expression> można określić w następujący sposób:

Podaj liczbę partycji, na przykład: ON PARTITIONS (2).
Podaj numery partycji dla kilku pojedynczych partycji rozdzielonych przecinkami, na przykład: ON PARTITIONS (1, 5).
Podaj zarówno zakresy, jak i poszczególne partycje, na przykład: ON PARTITIONS (2, 4, 6 TO 8).

<range> można określić jako numery partycji rozdzielone słowem kluczowym TO, na przykład: ON PARTITIONS (6 TO 8).

Aby ustawić różne typy kompresji danych dla różnych partycji, określ opcję DATA_COMPRESSION więcej niż raz, na przykład:

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

Można również określić opcję XML_COMPRESSION więcej niż raz, na przykład:

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

Uwagi

Podczas tworzenia planu zapytania dla CREATE INDEX instrukcji optymalizator zapytań może wybrać skanowanie innego indeksu zamiast skanowania tabeli. Operacja sortowania może zostać wyeliminowana w niektórych sytuacjach. Na komputerach wieloprocesorowych CREATE INDEX można używać równoległości operacji skanowania i sortowania skojarzonych z tworzeniem indeksu w taki sam sposób, jak inne zapytania. Aby uzyskać więcej informacji, zobacz Konfigurowanie operacji indeksowania równoległego.

Operacja CREATE INDEX może być minimalnie rejestrowana, jeśli model odzyskiwania bazy danych został ustawiony na zalogowany zbiorczo lub prosty.

Indeksy można tworzyć w tabeli tymczasowej. Gdy tabela zostanie porzucona lub znika z zakresu, indeksy są porzucane.

Indeks klastrowany jest oparty na zmiennej tabeli po dodaniu ograniczenia klucza podstawowego. Podobnie indeks nieklastrowany jest oparty na zmiennej tabeli po dodaniu unikatowego ograniczenia. Gdy zmienna tabeli wykracza poza zakres, indeksy są porzucane.

Indeksy obsługują właściwości rozszerzone.

CREATE INDEX nie jest obsługiwana w usłudze Microsoft Fabric.

Indeksy klastrowane

Utworzenie indeksu klastrowanego w tabeli (sterta) lub usunięcie i ponowne utworzenie istniejącego klastrowanego indeksu wymaga udostępnienia dodatkowego obszaru roboczego w bazie danych w celu uwzględnienia sortowania danych i tymczasowej kopii oryginalnej tabeli lub istniejących danych indeksu klastrowanego. Aby uzyskać więcej informacji na temat indeksów klastrowanych, zobacz Create clustered indexes and the SQL Server index architecture and design guide (Tworzenie indeksów klastrowanych oraz architektura indeksu programu SQL Server i przewodnik projektowania).

Indeksy nieklastrowane

Począwszy od programu SQL Server 2016 (13.x), w usłudze Azure SQL Database i w usłudze Azure SQL Managed Instance, można utworzyć indeks nieklastrowany w tabeli przechowywanej jako indeks klastrowanego magazynu kolumn. Jeśli najpierw utworzysz indeks nieklastrowany w tabeli przechowywanej jako stos lub indeks klastrowany, indeks będzie się powtarzać, jeśli później przekonwertujesz tabelę na indeks klastrowanego magazynu kolumn. Nie jest również konieczne usunięcie indeksu nieklastrowanego podczas ponownego kompilowania klastrowanego indeksu magazynu kolumn.

Opcja FILESTREAM_ON nie jest prawidłowa podczas tworzenia indeksu nieklastrowanego w tabeli przechowywanej jako indeks klastrowanego magazynu kolumn.

Indeksy unikatowe

Gdy istnieje unikatowy indeks, aparat bazy danych sprawdza zduplikowane wartości za każdym razem, gdy dane są dodawane lub modyfikowane. Operacje, które wygenerują zduplikowane wartości kluczy, zostaną wycofane, a aparat bazy danych zwróci komunikat o błędzie. Jest to prawdą, nawet jeśli operacja dodawania lub modyfikowania danych zmienia wiele wierszy, ale powoduje tylko jeden duplikat. Jeśli zostanie podjęta próba wstawienia wierszy, gdy istnieje unikatowy indeks z IGNORE_DUP_KEY ustawioną ONopcją na , wiersze naruszające unikatowy indeks są ignorowane.

Partycjonowane indeksy

Indeksy partycjonowane są tworzone i utrzymywane w podobny sposób do partycjonowanych tabel, ale podobnie jak zwykłe indeksy, są obsługiwane jako oddzielne obiekty bazy danych. Indeks partycjonowany może znajdować się w tabeli, która nie jest podzielona na partycje, a indeks niepartycyjny może znajdować się w tabeli podzielonej na partycje.

Jeśli tworzysz indeks w tabeli podzielonej na partycje i nie określasz grupy plików, w której ma być umieszczany indeks, indeks jest partycjonowany w taki sam sposób, jak tabela bazowa. Wynika to z faktu, że indeksy są domyślnie umieszczane w tych samych grupach plików co ich tabele bazowe, a w przypadku tabeli partycjonowanej w tym samym schemacie partycji, który używa tych samych kolumn partycjonowania. Gdy indeks używa tego samego schematu partycji i kolumny partycjonowania co tabela, indeks jest wyrównany z tabelą.

Ostrzeżenie

Tworzenie i ponowne kompilowanie indeksów nieprzyznanych w tabeli z ponad 1000 partycjami jest możliwe, ale nie jest obsługiwane. Może to spowodować obniżenie wydajności lub nadmierne zużycie pamięci podczas tych operacji. Zalecamy używanie indeksów wyrównanych tylko wtedy, gdy liczba partycji przekracza 1000.

Podczas partycjonowania indeksu klastrowanego, aparat bazy danych domyślnie dodaje wszystkie kolumny partycjonowania do listy kluczy indeksu klastrowanego, jeśli nie zostały jeszcze określone.

Widoki indeksowane można tworzyć w tabelach podzielonych na partycje w taki sam sposób, jak indeksy w tabelach. Aby uzyskać więcej informacji na temat indeksów partycjonowanych, zobacz Partycjonowane tabele i indeksy oraz architektura indeksu programu SQL Server i przewodnik projektowania.

Po utworzeniu lub ponownym utworzeniu indeksu zapytanie optymalizuje statystyki dotyczące indeksu. W przypadku indeksu partycjonowanego optymalizator zapytań używa domyślnego algorytmu próbkowania zamiast skanowania wszystkich wierszy w tabeli dla indeksu niepartycyjnego. Aby uzyskać statystyki dotyczące partycjonowanych indeksów, przeskanując wszystkie wiersze w tabeli, użyj CREATE STATISTICS lub UPDATE STATISTICS z klauzulą FULLSCAN.

Przefiltrowane indeksy

Filtrowany indeks jest zoptymalizowanym indeksem nieklastrowanym, dostosowanym do zapytań, które wybierają niewielki procent wierszy z tabeli. Używa predykatu filtru do indeksowania części danych w tabeli. Dobrze zaprojektowany filtrowany indeks może poprawić wydajność zapytań, zmniejszyć koszty magazynowania i zmniejszyć koszty konserwacji.

Wymagane opcje ZESTAWU dla filtrowanych indeksów

Opcje w kolumnie WymaganaSET wartość są wymagane za każdym razem, gdy wystąpi dowolny z następujących warunków:

Utworzysz filtrowany indeks.
Instrukcja INSERT, UPDATE, DELETElub MERGE modyfikuje dane w filtrowanym indeksie.

Filtrowany indeks jest używany przez optymalizator zapytań do utworzenia planu zapytania.

opcja `SET`	Wymagana wartość	Wartość domyślna serwera	Domyślna wartość OLE DB i ODBC	Wartość domyślna DB-Library
`ANSI_NULLS`	`ON`	`ON`	`ON`	`OFF`
`ANSI_PADDING`	`ON`	`ON`	`ON`	`OFF`
`ANSI_WARNINGS` ¹	`ON`	`ON`	`ON`	`OFF`
`ARITHABORT`	`ON`	`ON`	`OFF`	`OFF`
`CONCAT_NULL_YIELDS_NULL`	`ON`	`ON`	`ON`	`OFF`
`NUMERIC_ROUNDABORT`	`OFF`	`OFF`	`OFF`	`OFF`
`QUOTED_IDENTIFIER`	`ON`	`ON`	`ON`	`OFF`

¹ Ustawienie niejawnie ANSI_WARNINGS ustawia ARITHABORT wartość ONON , gdy poziom zgodności bazy danych jest ustawiony na 90 lub wyższy. Jeśli poziom zgodności bazy danych ma wartość 80 lub starszą, ARITHABORT opcja musi być jawnie ustawiona na ONwartość .

SET Jeśli opcje są nieprawidłowe, mogą wystąpić następujące warunki:

Tworzenie filtrowanego indeksu kończy się niepowodzeniem.
Aparat bazy danych generuje błąd i cofa INSERTinstrukcję , UPDATE, DELETElub MERGE , która zmienia dane w indeksie.
Optymalizator zapytań nie uwzględnia indeksu w planie wykonywania dla żadnych instrukcji Transact-SQL.

Aby uzyskać więcej informacji na temat filtrowanych indeksów, zobacz Create filtered indexes and the SQL Server index architecture and design guide (Tworzenie filtrowanych indeksów oraz architektura indeksu programu SQL Server i przewodnik projektowania).

Indeksy przestrzenne

Aby uzyskać informacje na temat indeksów przestrzennych, zobacz CREATE SPATIAL INDEX and Spatial indexes overview (Omówienie tworzenia indeksów przestrzennych i indeksów przestrzennych).

Indeksy XML

Aby uzyskać informacje o indeksach XML, zobacz create XML INDEX i XML Indexes (SQL Server).

Rozmiar klucza indeksu

Maksymalny rozmiar klucza indeksu to 900 bajtów dla indeksu klastrowanego i 1700 bajtów dla indeksu nieklastrowanego. (Przed usługą SQL Database i programem SQL Server 2016 (13.x) limit wynosił zawsze 900 bajtów). Indeksy w kolumnach varchar , które przekraczają limit bajtów, można utworzyć, jeśli istniejące dane w kolumnach nie przekraczają limitu w momencie utworzenia indeksu; jednak kolejne operacje wstawiania lub aktualizowania kolumn, które powodują, że całkowity rozmiar będzie większy niż limit. Klucz indeksu klastrowanego nie może zawierać kolumn varchar, które mają istniejące dane w jednostce alokacji ROW_OVERFLOW_DATA. Jeśli indeks klastrowany jest tworzony w kolumnie varchar , a istniejące dane są w IN_ROW_DATA jednostce alokacji, kolejne operacje wstawiania lub aktualizacji w kolumnie, które wypychają dane poza wierszem, kończą się niepowodzeniem.

Indeksy nieklastrowane mogą zawierać kolumny inne niż klucz (dołączone) na poziomie liścia indeksu. Te kolumny nie są brane pod uwagę przez aparat bazy danych podczas obliczania rozmiaru klucza indeksu. Aby uzyskać więcej informacji, zobacz Tworzenie indeksów z dołączonymi kolumnami i przewodnikiem projektowania i architektury indeksu programu SQL Server.

Nuta

Jeśli tabele są partycjonowane, jeśli kolumny klucza partycjonowania nie są jeszcze obecne w indeksie klastrowanym, są one dodawane do indeksu przez aparat bazy danych. Łączny rozmiar indeksowanych kolumn (bez zliczania dołączonych kolumn) oraz żadne dodane kolumny partycjonowania nie mogą przekraczać 1800 bajtów w indeksie klastrowanym, który nie jest unikatowy.

Obliczone kolumny

Indeksy można tworzyć w kolumnach obliczeniowych. Ponadto obliczone kolumny mogą mieć właściwość PERSISTED. Oznacza to, że aparat bazy danych przechowuje obliczone wartości w tabeli i aktualizuje je, gdy zostaną zaktualizowane inne kolumny, od których zależy obliczona kolumna. Aparat bazy danych używa tych utrwalonej wartości podczas tworzenia indeksu w kolumnie i gdy indeks jest przywoływane w zapytaniu.

Aby indeksować obliczoną kolumnę, obliczona kolumna musi być deterministyczna i precyzyjna. Jednak użycie PERSISTED właściwości rozszerza typ kolumn obliczanych z możliwością indeksowania w celu uwzględnienia:

Obliczone kolumny oparte na funkcjach Transact-SQL i CLR oraz metodach typów zdefiniowanych przez użytkownika CLR, które są oznaczone deterministyczną przez użytkownika.
Obliczone kolumny na podstawie wyrażeń, które są deterministyczne zgodnie z definicją aparatu bazy danych, ale nie są zwięzłe.

Utrwalone obliczone kolumny wymagają ustawienia następujących SET opcji, jak pokazano w poprzedniej sekcji Wymagane opcje ZESTAWU dla filtrowanych indeksów.

Ograniczenie UNIQUE lub PRIMARY KEY może zawierać obliczoną kolumnę, o ile spełnia wszystkie warunki indeksowania. W szczególności obliczona kolumna musi być deterministyczna i dokładna lub deterministyczna i utrwalone. Aby uzyskać więcej informacji na temat determinizmu, zobacz Deterministyczne i Niedeterministyczne funkcje.

Obliczone kolumny pochodzące zobrazu , ntext, tekst, varchar(max), nvarchar(max), varbinary(max)i typy danych xml można indeksować jako klucz lub dołączyć kolumnę inną niż klucz, o ile typ danych obliczonej kolumny jest dozwolony jako kolumna klucza indeksu lub kolumna nieklucza. Na przykład nie można utworzyć podstawowego indeksu XML w obliczonej kolumnie xml. Jeśli rozmiar klucza indeksu przekracza 900 bajtów, zostanie wyświetlony komunikat ostrzegawczy.

Utworzenie indeksu w obliczonej kolumnie może spowodować niepowodzenie operacji wstawiania lub aktualizacji, która wcześniej działała. Taka awaria może wystąpić, gdy obliczona kolumna powoduje błąd arytmetyczny.

Na przykład w poniższej tabeli, chociaż wyrażenie obliczonej kolumny c wydaje się powodować błąd arytmetyczny po wstawieniu wiersza, INSERT instrukcja działa.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Jeśli jednak utworzysz indeks dla obliczonej kolumny c, ta sama INSERT instrukcja zakończy się niepowodzeniem.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Aby uzyskać więcej informacji, zobacz Indeksy na obliczanych kolumnach.

Uwzględnione kolumny w indeksach

Kolumny inne niż kluczowe, nazywane dołączonymi kolumnami, można dodać do poziomu liści indeksu nieklastrowanego w celu zwiększenia wydajności zapytań przez pokrycie zapytania. Oznacza to, że wszystkie kolumny, do których odwołuje się zapytanie, znajdują się w indeksie jako kolumny klucza lub innego niż klucz. Dzięki temu optymalizator zapytań może uzyskać wszystkie wymagane informacje z nieklastrowanego skanowania indeksu lub wyszukiwania; dostęp do danych tabeli lub indeksu klastrowanego nie jest dostępny. Aby uzyskać więcej informacji, zobacz Tworzenie indeksów z dołączonymi kolumnami i przewodnikiem projektowania i architektury indeksu programu SQL Server.

Określanie opcji indeksu

Program SQL Server 2005 (9.x) wprowadził nowe opcje indeksu, a także zmodyfikował sposób, w jaki opcje są określone. W składni WITH option_name zgodnej z poprzednimi wersjami jest odpowiednikiem WITH (option_name = ON). Po ustawieniu opcji indeksu obowiązują następujące reguły:

Nowe opcje indeksu można określić tylko przy użyciu WITH (<option_name> = <ON | OFF>).
Nie można określić opcji przy użyciu zarówno zgodnej z poprzednimi wersjami, jak i nowej składni w tej samej instrukcji. Na przykład określenie WITH (DROP_EXISTING, ONLINE = ON) powoduje niepowodzenie instrukcji .
Podczas tworzenia indeksu XML opcje muszą być określone przy użyciu WITH (<option_name> = <ON | OFF>).

DROP_EXISTING, klauzula

Możesz użyć klauzuli DROP_EXISTING, aby ponownie skompilować indeks, dodać lub usunąć kolumny, zmodyfikować opcje, zmodyfikować kolejność sortowania kolumn lub zmienić schemat partycji lub grupę plików.

Jeśli indeks wymusza PRIMARY KEY ograniczenie lub UNIQUE i definicja indeksu nie zostanie w żaden sposób zmieniona, indeks zostanie porzucony i utworzony ponownie zachowując istniejące ograniczenie. Jeśli jednak definicja indeksu zostanie zmieniona, instrukcja nie powiedzie się. Aby zmienić definicję PRIMARY KEY ograniczenia lub UNIQUE , usuń ograniczenie i dodaj ograniczenie z nową definicją.

DROP_EXISTING zwiększa wydajność podczas ponownego tworzenia indeksu klastrowanego z tym samym lub innym zestawem kluczy w tabeli, która ma również indeksy nieklastrowane. DROP_EXISTING zastępuje wykonywanie instrukcji DROP INDEX w starym indeksie klastrowanym, a następnie wykonanie instrukcji CREATE INDEX dla nowego klastrowanego indeksu. Indeksy nieklastrowane są odbudowywane raz, a następnie tylko wtedy, gdy definicja indeksu uległa zmianie. Klauzula DROP_EXISTING nie kompiluje indeksów nieklastrowanych, gdy definicja indeksu ma taką samą nazwę indeksu, kolumny klucza i partycji, atrybut unikatowości i kolejność sortowania, jak oryginalny indeks.

Niezależnie od tego, czy indeksy nieklastrowane są odbudowywane, czy nie, zawsze pozostają w oryginalnych grupach plików lub schematach partycji i używają oryginalnych funkcji partycji. Jeśli indeks klastrowany zostanie ponownie skompilowany w innej grupie plików lub schemacie partycji, indeksy nieklastrowane nie zostaną przeniesione, aby zbiegły się z nową lokalizacją indeksu klastrowanego. W związku z tym nawet jeśli indeksy nieklastrowane były wcześniej wyrównane do indeksu klastrowanego, mogą już nie być wyrównane do niego. Aby uzyskać więcej informacji na temat wyrównania indeksu partycjonowanego, zobacz Partycjonowane tabele i indeksy.

Klauzula DROP_EXISTING nie sortuje ponownie danych, jeśli te same kolumny klucza indeksu są używane w tej samej kolejności i z tą samą kolejnością rosnącą lub malejącą, chyba że instrukcja indeksu określa indeks nieklastrowany, a ONLINE opcja jest ustawiona na OFFwartość . Jeśli indeks klastrowany jest wyłączony, należy wykonać operację CREATE INDEX WITH DROP_EXISTING z ustawioną wartością ONLINEOFF. Jeśli indeks nieklastrowany jest wyłączony i nie jest skojarzony z wyłączonym indeksem klastrowanym, CREATE INDEX WITH DROP_EXISTING można wykonać operację z ustawioną wartością OFFONLINE lub ON.

Nuta

Gdy indeksy z co najmniej 128 zakresami zostaną porzucone lub ponownie skompilowane, aparat bazy danych odchyli rzeczywiste przydziały strony i skojarzone z nimi blokady do momentu zatwierdzenia transakcji. Aby uzyskać więcej informacji, zobacz odroczonej alokacji transakcji.

Opcja ONLINE

Poniższe wytyczne dotyczą wykonywania operacji indeksowania w trybie online:

Nie można zmienić, obcięć ani porzucić tabeli bazowej, gdy operacja indeksu online jest w toku.
Podczas operacji indeksowania jest wymagane dodatkowe tymczasowe miejsce na dysku.
Operacje online można wykonywać na partycjonowanych indeksach i indeksach zawierających utrwalone kolumny obliczeniowe lub dołączone kolumny.
Opcja WAIT_AT_LOW_PRIORITY argumentu pozwala zdecydować, jak operacja indeksu jest kontynuowana, gdy czeka na blokadę Sch-M . Aby uzyskać więcej informacji, zobacz WAIT_AT_LOW_PRIORITY

Aby uzyskać więcej informacji, zobacz Wykonywanie operacji indeksowania w trybie online.

Operacje indeksu z możliwością wznowienia

Dotyczy: SQL Server 2019 (15.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Możesz utworzyć indeks online, aby można było wznowić operację tworzenia. Oznacza to, że kompilacja indeksu może zostać zatrzymana, a następnie ponownie uruchomiona od momentu zatrzymania. Aby uruchomić kompilację indeksu jako możliwe do wznowienia, określ RESUMABLE = ON opcję.

Poniższe wskazówki dotyczą operacji indeksowania z możliwością wznowienia:

Aby użyć opcji RESUMABLE, należy również użyć opcji ONLINE.
Opcja RESUMABLE nie jest utrwalana w metadanych dla danego indeksu i ma zastosowanie tylko do czasu trwania bieżącej instrukcji DDL. Dlatego należy jawnie określić klauzulę RESUMABLE = ON, aby umożliwić wznawianie.
Opcję MAX_DURATION można określić w dwóch kontekstach:
- MAX_DURATION RESUMABLE dla opcji określa interwał czasu odbudowy indeksu. Po upływie tego czasu i jeśli ponowne kompilowanie indeksu jest nadal uruchomione, zostanie wstrzymane. Decydujesz, kiedy można wznowić ponowną kompilację wstrzymanego indeksu. Czas w minutach dla MAX_DURATION musi być dłuższy niż 0 minut i krótszy niż lub równy jeden tydzień (7 * 24 * 60 = 10080 minut). Długa przerwa w operacji indeksu może znacząco wpłynąć na wydajność DML w określonej tabeli, a także pojemność dysku bazy danych, ponieważ zarówno oryginalny indeks, jak i nowo utworzony indeks wymagają miejsca na dysku i muszą zostać zaktualizowane przez operacje DML. Jeśli opcja MAX_DURATION zostanie pominięta, operacja indeksu będzie kontynuowana do momentu ukończenia lub do momentu wystąpienia błędu.
- MAX_DURATION dla opcji WAIT_AT_LOW_PRIORITY określa czas oczekiwania przy użyciu blokad o niskim priorytekcie, jeśli operacja indeksu jest zablokowana, przed podjęciem akcji. Aby uzyskać więcej informacji, zobacz WAIT_AT_LOW_PRIORITY z operacjami indeksowania online.
Aby natychmiast wstrzymać operację indeksu, możesz wykonać polecenie ALTER INDEX PAUSE lub wykonać polecenie KILL <session_id>.
Ponowne wykonanie oryginalnej CREATE INDEX instrukcji z tymi samymi parametrami wznawia wstrzymaną operację kompilacji indeksu. Możesz również wznowić wstrzymaną operację kompilacji indeksu, wykonując instrukcję ALTER INDEX RESUME .
Polecenie ABORT zabija sesję uruchamiającą kompilację indeksu i anuluje operację indeksu. Nie można wznowić operacji indeksowania, która została przerwana.

Operacja indeksu z możliwością wznowienia jest uruchamiana do momentu ukończenia, wstrzymania lub niepowodzenia. Jeśli operacja zostanie wstrzymana, zostanie wyświetlony błąd wskazujący, że operacja została wstrzymana i że tworzenie indeksu nie zostało ukończone. Jeśli operacja zakończy się niepowodzeniem, zostanie również wyświetlony błąd.

Aby sprawdzić, czy operacja indeksu jest wykonywana jako operacja z możliwością wznowienia i sprawdzić jego bieżący stan wykonania, użyj widoku wykazu sys.index_resumable_operations.

Zasoby

Następujące zasoby są wymagane do wznowienia operacji indeksowania:

Dodatkowe miejsce wymagane do zachowania kompilowania indeksu, w tym czas wstrzymania kompilacji.
Dodatkowa przepływność dziennika w fazie sortowania. Ogólne użycie miejsca w dzienniku dla indeksu z możliwością wznowienia jest mniejsze w porównaniu do zwykłego tworzenia indeksu online i umożliwia obcinanie dzienników podczas tej operacji.
Instrukcje DDL próbujące zmodyfikować tabelę skojarzą z tworzonym indeksem, gdy operacja indeksu jest wstrzymana, nie są dozwolone.
Czyszczenie duchów jest blokowane w indeksie kompilacji przez czas trwania operacji zarówno podczas wstrzymania, jak i podczas wykonywania operacji.
Jeśli tabela zawiera kolumny LOB, wznawiana kompilacja indeksu klastrowanego wymaga modyfikacji schematu (Sch-M) blokady na początku operacji.

Bieżące ograniczenia funkcjonalności

Operacje tworzenia indeksu z możliwością wznowienia mają następujące ograniczenia:

Po wstrzymaniu operacji tworzenia indeksu online z możliwością wznowienia nie można zmienić początkowej MAXDOP wartości.
Opcja SORT_IN_TEMPDB = ON nie jest obsługiwana w przypadku operacji indeksowania z możliwością wznowienia.
Nie można wykonać polecenia DDL z RESUMABLE = ON wewnątrz jawnej transakcji.
Nie można utworzyć indeksu, który zawiera:
- Kolumny obliczane lub timestamp (rowversion) jako kolumny klucza.
- Kolumna LOB jako dołączona kolumna.
Operacje indeksu z możliwością wznowienia nie są obsługiwane w następujących celach:
- Polecenie ALTER INDEX REBUILD ALL
- Polecenie ALTER TABLE REBUILD
- Indeksy kolumnowe
- Przefiltrowane indeksy
- Wyłączone indeksy

WAIT_AT_LOW_PRIORITY z operacjami indeksowania online

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Jeśli nie używasz WAIT_AT_LOW_PRIORITY tej opcji, wszystkie aktywne transakcje blokujące przechowujące blokady w tabeli lub indeksie muszą zostać ukończone, aby operacja tworzenia indeksu została uruchomiona i zakończona. Po rozpoczęciu i zakończeniu operacji indeksu online należy uzyskać udostępniony (S) lub modyfikację schematu (Sch-M) zablokować tabelę i przechowywać ją przez krótki czas. Mimo że blokada jest przechowywana tylko przez krótki czas, może ona znacząco wpłynąć na przepływność obciążenia, zwiększyć opóźnienie zapytań lub spowodować przekroczenie limitu czasu wykonywania.

Aby uniknąć tych problemów, opcja WAIT_AT_LOW_PRIORITY umożliwia zarządzanie zachowaniem S lub Sch-M blokad wymaganych do rozpoczęcia i zakończenia operacji indeksowania online, wybierając spośród trzech opcji. We wszystkich przypadkach, jeśli w czasie oczekiwania określonym przez MAX_DURATION = n [minutes] nie ma blokady, która obejmuje operację indeksu, operacja indeksu jest wykonywana natychmiast.

WAIT_AT_LOW_PRIORITY sprawia, że operacja indeksu online czeka przy użyciu blokad o niskim priorytekcie, umożliwiając innym operacjom korzystanie z normalnych blokad priorytetu w międzyczasie. Pominięcie opcji WAIT_AT_LOW_PRIORITY jest równoważne WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = czas [MINUTES]

Czas oczekiwania (wartość całkowita określona w minutach) oczekiwania operacji indeksu online przy użyciu blokad o niskim priorytekcie. Jeśli operacja zostanie zablokowana przez MAX_DURATION czasu, zostanie wykonana określona akcja ABORT_AFTER_WAIT. MAX_DURATION czas jest zawsze w minutach, a słowo MINUTES można pominąć.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

NONE: Kontynuuj oczekiwanie na blokadę z normalnym priorytetem.
SELF: Zakończ obecnie wykonywaną operację indeksu online bez podejmowania żadnych akcji. Nie można użyć opcji SELF, gdy MAX_DURATION wynosi 0.
BLOCKERS: zabij wszystkie transakcje użytkownika, które blokują operację indeksu online, aby operacja mogła kontynuować. Opcja BLOCKERS wymaga, aby podmiot zabezpieczeń wykonujący instrukcję CREATE INDEX lub ALTER INDEX miał uprawnienie ALTER ANY CONNECTION.

Za pomocą następujących zdarzeń rozszerzonych można monitorować operacje indeksowania oczekujące na blokady o niskim priorytekcie:

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

Opcje blokad wierszy i stron

Gdy ALLOW_ROW_LOCKS = ON i ALLOW_PAGE_LOCK = ON, blokady na poziomie wiersza, strony i tabeli są dozwolone podczas uzyskiwania dostępu do indeksu. Aparat bazy danych wybiera odpowiednią blokadę i może eskalować blokadę z wiersza lub blokady strony do blokady tabeli.

W przypadku ALLOW_ROW_LOCKS = OFF i ALLOW_PAGE_LOCK = OFFtylko blokada na poziomie tabeli jest dozwolona podczas uzyskiwania dostępu do indeksu.

Ostrzeżenie

Nie zaleca się wyłączania blokad wierszy lub stron w indeksie. Mogą wystąpić problemy związane ze współbieżnością, a niektóre funkcje mogą być niedostępne. Na przykład nie można zreorganizować indeksu, gdy ALLOW_PAGE_LOCKS jest ustawiona na OFF.

Sekwencyjne klucze

Dotyczy: SQL Server 2019 (15.x) i nowsze wersje, w usłudze Azure SQL Database i w usłudze Azure SQL Managed Instance.

Rywalizacja o wstawienie ostatniej strony to typowy problem z wydajnością występujący, gdy duża liczba współbieżnych wątków próbuje wstawić wiersze do indeksu przy użyciu klucza sekwencyjnego. Indeks jest uznawany za sekwencyjny, gdy kolumna klucza wiodącego zawiera wartości, które są zawsze rosnące (lub malejące), takie jak kolumna tożsamości lub data domyślna dla bieżącej daty/godziny. Ponieważ wstawione klucze są sekwencyjne, wszystkie nowe wiersze są wstawiane na końcu struktury indeksu — innymi słowy, na tej samej stronie. Prowadzi to do rywalizacji o stronę w pamięci, która może być obserwowana jako kilka wątków oczekujących na uzyskanie zatrzasku dla danej strony. Odpowiedni typ oczekiwania to PAGELATCH_EX.

Włączenie opcji indeksu OPTIMIZE_FOR_SEQUENTIAL_KEY umożliwia optymalizację aparatu bazy danych, która pomaga zwiększyć przepływność wstawiania wysokiej współbieżności do indeksu. Jest przeznaczony dla indeksów, które mają klucz sekwencyjny i dlatego są podatne na rywalizację o ostatnią stronę, ale może również pomóc w indeksach, które mają punkty aktywne w innych obszarach struktury indeksu B-Tree.

Nuta

Kompresja danych

Aby uzyskać więcej informacji na temat kompresji danych, zobacz Kompresja danych.

Poniżej przedstawiono kluczowe kwestie, które należy wziąć pod uwagę w kontekście operacji kompilacji indeksu, gdy jest używana kompresja danych:

Kompresja może zezwalać na przechowywanie większej liczby wierszy na stronie, ale nie zmienia maksymalnego rozmiaru wiersza.
Strony inne niż liścia indeksu nie są kompresowane na stronie, ale mogą być kompresowane wierszami.
Każdy indeks nieklastrowany ma indywidualne ustawienie kompresji i nie dziedziczy ustawienia kompresji bazowej tabeli.
Po utworzeniu klastrowanego indeksu na stercie indeks klastrowany dziedziczy stan kompresji sterta, chyba że określono alternatywny stan kompresji.

Aby ocenić, jak zmiana stanu kompresji wpływa na użycie miejsca przez tabelę, indeks lub partycję, użyj procedury składowanej sp_estimate_data_compression_savings .

Kompresja XML

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance.

Wiele zagadnień dotyczących kompresji danych ma zastosowanie do kompresji XML. Należy również pamiętać o następujących kwestiach:

Po określeniu listy partycji można włączyć kompresję XML na poszczególnych partycjach. Jeśli lista partycji nie jest określona, wszystkie partycje są ustawione tak, aby korzystały z kompresji XML. Po utworzeniu tabeli lub indeksu kompresja danych XML jest wyłączona, chyba że określono inaczej. Po zmodyfikowaniu tabeli istniejąca kompresja jest zachowywana, chyba że określono inaczej.
Jeśli określisz listę partycji lub partycję, która jest poza zakresem, zostanie wygenerowany błąd.
Po utworzeniu klastrowanego indeksu na stercie indeks klastrowany dziedziczy stan kompresji XML sterta, chyba że zostanie określona alternatywna opcja kompresji.
Zmiana ustawienia kompresji XML sterta wymaga ponownego skompilowania wszystkich indeksów nieklastrowanych w tabeli, tak aby miały wskaźniki do nowych lokalizacji wierszy w stercie.
Kompresję XML można włączyć lub wyłączyć w trybie online lub offline. Włączenie kompresji na stercie jest pojedyncze wątkowe dla operacji online.
Aby określić stan kompresji XML partycji w tabeli partycjonowanej, użyj xml_compression kolumny sys.partitions widoku wykazu.

Statystyki indeksu

Po utworzeniu indeksu magazynu wierszy aparat bazy danych tworzy również statystyki dotyczące kluczowych kolumn indeksu. Nazwa obiektu statystyk w widoku katalogu sys.stats jest zgodna z nazwą indeksu. W przypadku indeksu bez partycji statystyki są tworzone przy użyciu pełnego skanowania danych. W przypadku indeksu partycjonowanego statystyki są tworzone przy użyciu domyślnego algorytmu próbkowania.

Po utworzeniu indeksu magazynu kolumn aparat bazy danych tworzy również obiekt statystyk w pliku sys.stats . Ten obiekt statystyk nie zawiera danych statystycznych, takich jak histogram i wektor gęstości. Jest on używany podczas tworzenia klonowania bazy danych przez utworzenie skryptu bazy danych. W tym czasie polecenia i UPDATE STATISTICS ... WITH STATS_STREAM służą do uzyskiwania metadanych magazynu kolumn, DBCC SHOW_STATISTICS takich jak segment, słownik i rozmiar magazynu różnicowego, oraz dodawanie ich do statystyk dotyczących indeksu magazynu kolumn. Te metadane są uzyskiwane dynamicznie w czasie kompilacji zapytań dla regularnej bazy danych, ale są udostępniane przez obiekt statystyk klonowania bazy danych. Polecenie UPDATE STATISTICS nie jest obsługiwane dla obiektu statystyk w indeksie magazynu kolumn w żadnym innym scenariuszu.

Uprawnienia

ALTER Wymaga uprawnienia do tabeli lub widoku lub członkostwa w stałej db_ddladmin roli bazy danych.

Ograniczenia i ograniczenia

W usłudze Azure Synapse Analytics and Analytics Platform System (PDW) nie można utworzyć:

Klasterowany lub nieklastrowany indeks magazynu wierszy w tabeli magazynu danych, gdy indeks magazynu kolumn już istnieje. To zachowanie różni się od programu SQL Server SMP, który umożliwia współistnienie indeksów magazynu wierszy i magazynu kolumn w tej samej tabeli.
Nie można utworzyć indeksu w widoku.

Metadane

Aby wyświetlić informacje o istniejących indeksach, możesz wykonać zapytanie dotyczące widoku katalogu sys.indexes.

Informacje o wersji

Usługa Azure SQL Database nie obsługuje grup plików innych niż PRIMARY.
Usługi Azure SQL Database i Azure SQL Managed Instance nie obsługują opcji FILESTREAM.
Indeksy magazynu kolumn nie są dostępne przed programem SQL Server 2012 (11.x).
Operacje indeksowania z możliwością wznowienia są dostępne od programu SQL Server 2017 (14.x), w usłudze Azure SQL Database i w usłudze Azure SQL Managed Instance.

Przykłady: wszystkie wersje. Używa bazy danych AdventureWorks

A. Tworzenie prostego, nieklastrowanego indeksu magazynu wierszy

Poniższe przykłady tworzą indeks nieklastrowany w kolumnie VendorID tabeli Purchasing.ProductVendor.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. Tworzenie prostego, nieklastrowanego indeksu złożonego magazynu wierszy

W poniższym przykładzie tworzony jest nieklastrowany indeks złożony w kolumnach SalesQuota i SalesYTD tabeli Sales.SalesPerson.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. Tworzenie indeksu w tabeli w innej bazie danych

Poniższy przykład tworzy indeks klastrowany w kolumnie VendorID tabeli ProductVendor w bazie danych Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. Dodawanie kolumny do indeksu

Poniższy przykład tworzy indeks IX_FF z dwiema kolumnami z bazy danych. Tabela FactFinance. Następna instrukcja ponownie kompiluje indeks z jeszcze jedną kolumną i przechowuje istniejącą nazwę.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

Przykłady: SQL Server, Azure SQL Database

E. Tworzenie unikatowego indeksu nieklastrowanego

Poniższy przykład tworzy unikatowy indeks nieklastrowany w kolumnie Name tabeli Production.UnitMeasure w bazie danych AdventureWorks2022. Indeks wymusi unikatowość danych wstawionych do kolumny Name.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

Poniższe zapytanie testuje ograniczenie unikatowości, próbując wstawić wiersz o tej samej wartości co w istniejącym wierszu.

-- Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GETDATE());

Wynikowy komunikat o błędzie to:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F. Użyj opcji IGNORE_DUP_KEY

W poniższym przykładzie pokazano efekt opcji IGNORE_DUP_KEY przez wstawienie wielu wierszy do tabeli tymczasowej z opcją ustawioną na ON i ponownie z opcją ustawioną na OFF. Pojedynczy wiersz jest wstawiany do tabeli #Test, która celowo spowoduje zduplikowanie wartości po wykonaniu drugiej instrukcji INSERT z wieloma wierszami. Liczba wierszy w tabeli zwraca liczbę wstawionych wierszy.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

Oto wyniki drugiej instrukcji INSERT.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

Zwróć uwagę, że wiersze wstawione z tabeli Production.UnitMeasure, które nie naruszają ograniczenia unikatowości, zostały pomyślnie wstawione. Zostało wyświetlone ostrzeżenie i zduplikowany wiersz został zignorowany, ale cała transakcja nie została wycofana.

Te same instrukcje są wykonywane ponownie, ale z IGNORE_DUP_KEY ustawioną na OFF.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

Oto wyniki drugiej instrukcji INSERT.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

Zwróć uwagę, że żaden z wierszy z tabeli Production.UnitMeasure nie został wstawiony do tabeli, mimo że tylko jeden wiersz w tabeli naruszył ograniczenie indeksu UNIQUE.

G. Używanie DROP_EXISTING do upuszczania i ponownego tworzenia indeksu

Poniższy przykład powoduje porzucenie i ponowne utworzenie istniejącego indeksu w kolumnie ProductID tabeli Production.WorkOrder w bazie danych AdventureWorks2022 przy użyciu opcji DROP_EXISTING. Ustawiane są również opcje FILLFACTOR i PAD_INDEX.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H. Tworzenie indeksu w widoku

Poniższy przykład tworzy widok i indeks w tym widoku. Uwzględniono dwa zapytania korzystające z widoku indeksowanego.

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
  DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
  WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO

-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND ProductID BETWEEN 700 AND 800
    AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO

-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND DATEPART(mm, OrderDate) = 3
  AND DATEPART(yy, OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

Ja. Tworzenie indeksu z dołączonymi kolumnami (niekluczanymi)

Poniższy przykład tworzy indeks nieklastrowany z jedną kolumną klucza (PostalCode) i czterema kolumnami niebędącymi kluczami (AddressLine1, AddressLine2, City, StateProvinceID). Zapytanie, które jest objęte indeksem, następuje. Aby wyświetlić indeks wybrany przez optymalizator zapytań, w menu Zapytanie w programie SQL Server Management Studio wybierz Wyświetl rzeczywisty plan wykonania przed wykonaniem zapytania.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

J. Tworzenie indeksu podzielonego na partycje

W poniższym przykładzie tworzony jest nieklastrowany indeks partycjonowany na TransactionsPS1, istniejący schemat partycji w bazie danych AdventureWorks2022. W tym przykładzie przyjęto założenie, że przykład partycjonowanego indeksu został zainstalowany.

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K. Tworzenie filtrowanego indeksu

Poniższy przykład tworzy indeks filtrowany w tabeli Production.BillOfMaterials w bazie danych AdventureWorks2022. Predykat filtru może zawierać kolumny, które nie są kolumnami kluczowymi w filtrowanym indeksie. Predykat w tym przykładzie wybiera tylko wiersze, w których EndDate ma wartość inną niż NULL.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L. Tworzenie skompresowanego indeksu

Poniższy przykład tworzy indeks w niepartycyjnej tabeli przy użyciu kompresji wierszy.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

Poniższy przykład tworzy indeks w tabeli partycjonowanej przy użyciu kompresji wierszy na wszystkich partycjach indeksu.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

Poniższy przykład tworzy indeks w tabeli partycjonowanej przy użyciu kompresji strony na partycji 1 indeksu i kompresji wierszy na partycjach 2 przez 4 indeksu.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

M. Tworzenie indeksu z kompresją XML

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance.

Poniższy przykład tworzy indeks w tabeli niepartycyjnej przy użyciu kompresji XML. Co najmniej jedna kolumna w indeksie musi być typem danych xml.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (XML_COMPRESSION = ON);
GO

Poniższy przykład tworzy indeks w tabeli partycjonowanej przy użyciu kompresji XML na wszystkich partycjach indeksu.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (XML_COMPRESSION = ON);
GO

N. Tworzenie, wznawianie, wstrzymywanie i przerywanie operacji indeksu wznawianego

Dotyczy: SQL Server 2019 (15.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

O. TWORZENIE INDEKSU z różnymi opcjami blokady o niskim priorytekcie

W poniższych przykładach użyto opcji WAIT_AT_LOW_PRIORITY, aby określić różne strategie radzenia sobie z blokowaniem.

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

W poniższym przykładzie użyto zarówno opcji RESUMABLE, jak i określono dwie MAX_DURATION wartości, pierwsza dotyczy opcji ABORT_AFTER_WAIT, druga dotyczy opcji RESUMABLE.

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

Przykłady: Azure Synapse Analytics and Analytics Platform System (PDW)

P. Składnia podstawowa

Tworzenie, wznawianie, wstrzymywanie i przerywanie operacji indeksu wznawianego

Dotyczy: SQL Server 2019 (15.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

Q. Tworzenie indeksu nieklastrowanego w tabeli w bieżącej bazie danych

Poniższy przykład tworzy indeks nieklastrowany w kolumnie VendorID tabeli ProductVendor.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. Tworzenie indeksu klastrowanego w tabeli w innej bazie danych

Poniższy przykład tworzy indeks nieklastrowany w kolumnie VendorID tabeli ProductVendor w bazie danych Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. Tworzenie uporządkowanego indeksu klastrowanego w tabeli

Poniższy przykład tworzy uporządkowany indeks klastrowany w kolumnach c1 i c2 tabeli T1 w bazie danych MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. Konwertowanie CCI na uporządkowany indeks klastrowany w tabeli

Poniższy przykład konwertuje istniejący klastrowany indeks magazynu kolumn na uporządkowany indeks klastrowanego magazynu kolumn o nazwie MyOrderedCCI w kolumnach c1 i c2 tabeli T2 w bazie danych MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

architektura indeksu i przewodnik projektowania programu SQL Server
wykonywanie operacji indeksowania w trybie online
indeksy i alter TABLE
ALTER INDEX
CREATE PARTITION FUNCTION
TWORZENIE SCHEMATU PARTYCJI
CREATE SPATIAL INDEX
CREATE STATISTICS
CREATE TABLE
CREATE XML INDEX
typy danych
DBCC SHOW_STATISTICS
DROP INDEX
indeksów XML (SQL Server)
sys.indexes
sys.index_columns
sys.xml_indexes
EVENTDATA

Udostępnij za pośrednictwem

CREATE INDEX (Transact-SQL)

Składnia

Składnia dla programu SQL Server, usługi Azure SQL Database, wystąpienia zarządzanego Azure SQL

Indeks relacyjny zgodny z poprzednimi wersjami

Składnia dla usług Azure Synapse Analytics i Parallel Data Warehouse

Argumenty

NIEPOWTARZALNY

KLASTROWANY

NIEKLASTROWANE

index_name

kolumny

[ ASC | DESC ]

INCLUDE ( kolumna [ ,... n ] )

GDZIE <filter_predicate>

ON partition_scheme_name ( column_name )

ON filegroup_name

WŁĄCZONE [ustawienie domyślne]

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

<obiektu>::=

database_name

schema_name

table_or_view_name

<relational_index_option>::=

PAD_INDEX = { ON | WYŁ. }

FILLFACTOR = fillfactor

SORT_IN_TEMPDB = { ON | WYŁ. }

IGNORE_DUP_KEY = { ON | WYŁ. }

STATISTICS_NORECOMPUTE = { ON | WYŁ.}

STATISTICS_INCREMENTAL = { ON | WYŁ. }

DROP_EXISTING = { ON | WYŁ. }

ONLINE = { ON | WYŁ. }

WZNAWIANIE = { WŁ. | WYŁ. }

MAX_DURATION = czas [MINUT] używany z RESUMABLE = ON (wymaga ONLINE = ON)

ALLOW_ROW_LOCKS = { ON | WYŁ. }

ALLOW_PAGE_LOCKS = { ON | WYŁ. }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | WYŁ. }

MAXDOP = max_degree_of_parallelism

DATA_COMPRESSION

XML_COMPRESSION

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

Uwagi

Indeksy klastrowane

Indeksy nieklastrowane

Indeksy unikatowe

Partycjonowane indeksy

Przefiltrowane indeksy

Wymagane opcje ZESTAWU dla filtrowanych indeksów

Indeksy przestrzenne

Indeksy XML

Rozmiar klucza indeksu

Obliczone kolumny

Uwzględnione kolumny w indeksach

Określanie opcji indeksu

DROP_EXISTING, klauzula

Opcja ONLINE

Operacje indeksu z możliwością wznowienia

Zasoby

Bieżące ograniczenia funkcjonalności

WAIT_AT_LOW_PRIORITY z operacjami indeksowania online

Opcje blokad wierszy i stron

Sekwencyjne klucze

Kompresja danych

Kompresja XML

Statystyki indeksu

Uprawnienia

Ograniczenia i ograniczenia

Metadane

Informacje o wersji

Przykłady: wszystkie wersje. Używa bazy danych AdventureWorks

A. Tworzenie prostego, nieklastrowanego indeksu magazynu wierszy

B. Tworzenie prostego, nieklastrowanego indeksu złożonego magazynu wierszy

C. Tworzenie indeksu w tabeli w innej bazie danych

D. Dodawanie kolumny do indeksu

Przykłady: SQL Server, Azure SQL Database

E. Tworzenie unikatowego indeksu nieklastrowanego

F. Użyj opcji IGNORE_DUP_KEY

G. Używanie DROP_EXISTING do upuszczania i ponownego tworzenia indeksu

H. Tworzenie indeksu w widoku

Ja. Tworzenie indeksu z dołączonymi kolumnami (niekluczanymi)

MAX_DURATION = czas [MINUT] używany z `RESUMABLE = ON` (wymaga `ONLINE = ON`)