Udostępnij za pośrednictwem


CREATE INDEX (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 i usługi SQL Database.

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

przykłady :

  1. Tworzenie indeksu nieklastrowanego w tabeli lub widoku

    CREATE INDEX index1 ON schema1.table1 (column1);
    
  2. Tworzenie indeksu klastrowanego w tabeli i używanie 3-częściowej nazwy tabeli

    CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
    
  3. 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) i usługi SQL Database, możesz użyć indeksu nieklastrowanego w indeksie magazynu kolumn, aby zwiększyć wydajność zapytań magazynowania danych. Aby uzyskać więcej informacji, zobacz Indexes Columnstore Indexes — Data Warehouse.

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. Indeks klastrowany w widoku musi być unikatowy.

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 jest ustawiona na wartość WŁĄCZONE. Jeśli zostanie to wypróbowane, aparat bazy danych wyświetli komunikat o błędzie. Przed utworzeniem unikatowego indeksu w kolumnie lub kolumnach należy usunąć zduplikowane wartości. Kolumny używane w indeksie unikatowym powinny być ustawione na WARTOŚĆ NOT NULL, ponieważ wiele wartości null jest traktowanych jako duplikaty podczas tworzenia unikatowego indeksu.

KLASTROWANY

Tworzy indeks, w którym kolejność logiczna wartości klucza określa kolejność fizyczną odpowiadających wierszy w tabeli. Dolny lub liściowy poziom indeksu klastrowanego zawiera rzeczywiste wiersze danych tabeli. Tabela lub widok jest dozwolony dla jednego klastrowanego indeksu jednocześnie.

Widok z unikatowym indeksem klastrowanym jest nazywany widokiem indeksowanym. 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 Create Indexed Views.

Utwórz indeks klastrowany przed utworzeniem wszystkich indeksów nieklastrowanych. Istniejące indeksy nieklastrowane w tabelach są odbudowywane po utworzeniu indeksu klastrowanego.

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

Nuta

Ponieważ poziom liścia indeksu klastrowanego i stron danych są takie same według definicji, utworzenie indeksu klastrowanego i użycie klauzuli ON partition_scheme_name lub ON filegroup_name skutecznie przenosi tabelę 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 Enable Indexes and Constraints and Disable Indexes and Constraints.

NIEKLASTROWANE

Tworzy indeks określający kolejność logiczną tabeli. W przypadku indeksu nieklastrowanego fizyczna kolejność wierszy danych jest niezależna od ich kolejności indeksowania.

Każda tabela może zawierać maksymalnie 999 indeksów nieklastrowanych, niezależnie od sposobu tworzenia indeksów: niejawnie z ograniczeniami KLUCZ PODSTAWOWY i UNIKATOWY albo jawnie z CREATE INDEX.

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 widoku nie może zawierać ntekstu, tekstuani kolumn obrazu, nawet jeśli nie są one przywoływanych 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 środowiska CLR, zobacz 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 usługi 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 liście INCLUDE i nie mogą być używane jednocześnie jako kolumny klucza i niekluczesne. Indeksy nieklastrowane zawsze 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) i usługi Azure SQL Database, jeśli którakolwiek z określonych kolumn innych niż klucz jest varchar(max), nvarchar(max)lub varbinary(max) typów danych, indeks można skompilować lub ponownie skompilować przy użyciu opcji online.

Kolumny obliczane, które są deterministyczne i precyzyjne lub nieprecyzyjne, mogą zawierać kolumny. Obliczone kolumny pochodzące z obrazu, ntext, tekst, varchar(max), nvarchar(max)), varbinary(max)i xml typy danych mogą być uwzględniane w kolumnach innych niż kluczowe, o ile typy danych kolumn obliczeniowych są dozwolone jako dołączona kolumna. Aby uzyskać więcej informacji, zobacz Indeksy w kolumnach obliczanych.

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 UDT, kolumny typu danych przestrzennych ani kolumny typu danych hierarchyID. Porównania używające literałów NULL nie są dozwolone z operatorami porównania. 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 indeksów UNIKATOWYch 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óre będą 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ę, dla której zostanie podzielony indeks partycjonowany. 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 indeksu UNIQUE, 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, partycjonowane tabele i indeksy.

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 POZYCJI "default"

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

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

Nuta

Wartość domyślna nie wskazuje domyślnej grupy plików bazy danych w kontekście CREATE INDEX. Różni się to od CREATE TABLE, gdzie "default" lokalizuje 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, należy zdefiniować widok za pomocą funkcji SCHEMABINDING. Przed utworzeniem indeksu nieklastrowanego należy utworzyć unikatowy indeks klastrowany w widoku. Aby uzyskać więcej informacji na temat indeksowanych widoków, zobacz sekcję 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ą bazą danych lub database_name jest tempdb, a object_name zaczyna się od #.

<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 fillfactor jest stosowany do stron na poziomie pośrednim indeksu.

Nie określono off lub fillfactor
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.

Opcja PAD_INDEX jest przydatna tylko wtedy, gdy parametr FILLFACTOR jest określony, ponieważ PAD_INDEX używa wartości procentowej określonej przez FILLFACTOR. Jeśli wartość procentowa określona dla funkcji FILLFACTOR 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ż dwa, niezależnie od tego, jak niska 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 w sys.indexes.

Ważny

Utworzenie indeksu klastrowanego z FILLFACTOR mniejszym niż 100 wpływa na ilość miejsca do magazynowania zajmowanego przez aparat bazy danych, ponieważ aparat bazy danych redystrybuuje dane podczas tworzenia indeksu klastrowanego.

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 włączona, niezależnie od opcji określonej, chyba że jest używana ponowna ponowna kompilacja indeksu z możliwością wznowienia.

NA
Wyniki sortowania pośredniego używane do kompilowania indeksu są przechowywane w tempdb. Może to skrócić czas wymagany do utworzenia indeksu, jeśli tempdb znajduje się na innym zestawie dysków niż baza danych użytkownika. 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 do przechowywania wyników 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
Po wstawieniu zduplikowanych wartości klucza do unikatowego indeksu zostanie wyświetlony komunikat ostrzegawczy. Tylko wiersze naruszające ograniczenie unikatowości zakończy się niepowodzeniem.

OD
Po wstawieniu zduplikowanych wartości klucza do unikatowego indeksu zostanie wyświetlony komunikat o błędzie. Cała operacja INSERT zostanie wycofana.

IGNORE_DUP_KEY nie można ustawić wartości WŁĄCZONE dla indeksów utworzonych w widoku, indeksach innych niż unikatowe, indeksach XML, indeksach przestrzennych i filtrowanych indeksach.

Aby wyświetlić IGNORE_DUP_KEY, użyj 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 dystrybucji 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.

Ważny

Wyłączenie automatycznej ponownej kompilacji statystyk dystrybucji może uniemożliwić optymalizatorowi zapytań wybranie optymalnych planów wykonywania zapytań dotyczących tabeli.

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 (począwszy od programu SQL Server 2014 (12.x)) i usługi Azure SQL Database

Podczas ONtworzone statystyki są statystykami partycji. Gdy off, drzewo statystyk jest porzucane, 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 przypadku następujących typów statystyk:

  • 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 udziału intencji (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ółdzielona (S) jest przechowywana w obiekcie źródłowym przez bardzo krótki czas. Na końcu operacji przez krótki czas jest uzyskiwana blokada S (współużytkowana) w źródle, jeśli tworzony jest indeks nieklastrowany. Blokada Sch-M (modyfikacja schematu) jest uzyskiwana, gdy indeks klastrowany jest tworzony lub porzucany w trybie online oraz gdy indeks klastrowany lub nieklastrowany jest odbudowywany. Nie można ustawić trybu ONLINE na WŁ., gdy indeks jest tworzony w lokalnej tabeli tymczasowej.

Nuta

Tworzenie indeksu online może ustawić opcje low_priority_lock_wait, 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 albo 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 offline, która tworzy indeks nieklastrowany, uzyskuje blokadę współużytkowaną (S) w tabeli. Zapobiega to aktualizacjom tabeli bazowej, ale umożliwia operacje odczytu, takie jak instrukcje SELECT.

Aby uzyskać więcej informacji, zobacz Perform Index Operations 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
  • Nieklastrowane indeksy magazynu kolumn w programie SQL Server 2016 (13.x)) i starszych
  • 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) i usługi Azure SQL Database, gdy tabela zawiera varchar(max) lub kolumny varbinary(max), indeks klastrowany zawierający inne kolumny można skompilować lub skompilować przy użyciu opcji ONLINE.
  • Indeksy nieklastrowane w tabeli z klastrowanym indeksem magazynu kolumn

Aby uzyskać więcej informacji, zobacz How Online Index Operations Work.

WZNAWIANIE = { WŁ. | WYŁ. }

Dotyczy: SQL Server (począwszy od programu SQL Server 2019 (15.x)) i usługi Azure SQL Database

Określa, czy operacja indeksu online jest wznawiana.

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 (począwszy od programu SQL Server 2019 (15.x)) i usługi Azure SQL Database

Wskazuje czas (wartość całkowita określona w minutach), którą jest wykonywana operacja wznawiania indeksu online przed wstrzymaniem.

Ważny

Aby uzyskać bardziej szczegółowe informacje na temat operacji indeksowania, które można wykonać w trybie online, zobacz Guidelines for Online Index Operations.

Nuta

Ponowne kompilowanie indeksów online nie jest obsługiwane w indeksach magazynu kolumn ani w indeksach wyłączonych.

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 (począwszy od programu SQL Server 2019 (15.x)) i usługi Azure SQL Database

Określa, czy należy zoptymalizować pod kątem rywalizacji o wstawienie 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 na czas trwania operacji indeksu. Aby uzyskać więcej informacji, zobacz Configure the max degree of parallelism Server Configuration Option. Użyj opcji MAXDOP, aby ograniczyć liczbę procesorów używanych w równoległym wykonywaniu planu. Maksymalna wartość to 64 procesory.

max_degree_of_parallelism może to być:

1
Pomija generowanie planu równoległego.

>1
Ogranicza maksymalną liczbę procesorów używanych 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 rzeczywistej liczby procesorów lub mniej na podstawie bieżącego obciążenia systemu.

Aby uzyskać więcej informacji, zobacz Configure Parallel Index Operations.

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.

SZEREG
Indeks lub określone partycje są kompresowane przy użyciu kompresji wierszy.

STRONA
Indeks lub określone partycje są kompresowane przy użyciu kompresji strony.

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.

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 wygeneruje 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 wyrazem 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

Instrukcja CREATE INDEX jest zoptymalizowana jak każde inne zapytanie. Aby zapisać operacje we/wy, procesor 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że używać większej liczby procesorów do wykonywania operacji skanowania i sortowania skojarzonych z tworzeniem indeksu w taki sam sposób, jak w przypadku innych zapytań. Aby uzyskać więcej informacji, zobacz Configure Parallel Index Operations.

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. Po usunięciu tabeli lub zakończeniu sesji indeksy zostaną porzucone.

Indeks klastrowany można utworzyć na zmiennej tabeli podczas tworzenia klucza podstawowego. Po zakończeniu zapytania lub zakończeniu sesji indeks zostanie porzucony.

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 (Tworzenie indeksów klastrowanych) oraz architektura indeksów i przewodnik projektowania programu SQL Server.

Indeksy nieklastrowane

Począwszy od programu SQL Server 2016 (13.x) i usługi Azure SQL Database, 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 utrwalany, 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.

Ograniczenia i ograniczenia:

  • 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 przez operacje wstawiania. Wstaw operacje, które wygenerują zduplikowane wartości kluczy, zostaną wycofane, a aparat bazy danych wyświetli komunikat o błędzie. Jest to prawdą, nawet jeśli operacja wstawiania zmienia wiele wierszy, ale powoduje tylko jeden duplikat. Jeśli zostanie podjęta próba wprowadzenia danych, dla których istnieje unikatowy indeks, a klauzula IGNORE_DUP_KEY jest ustawiona na WŁ., tylko wiersze naruszające indeks UNIKATOWY kończą się niepowodzeniem.

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 tylko wyrównanych indeksów, 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 i przewodnik projektowania programu SQL Server.

W programie SQL Server statystyki nie są tworzone przez skanowanie wszystkich wierszy w tabeli podczas tworzenia lub odbudowy indeksu partycjonowanego. Zamiast tego optymalizator zapytań używa domyślnego algorytmu próbkowania do generowania statystyk. 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 SET w kolumnie Wymagana wartość są wymagane za każdym razem, gdy wystąpi dowolny z następujących warunków:

  • Utwórz filtrowany indeks.

  • Operacja INSERT, UPDATE, DELETE lub MERGE modyfikuje dane w filtrowanym indeksie.

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

    OPCJE ZESTAWU Wymagana wartość Wartość domyślna serwera Domyślny

    Wartość OLE DB i ODBC
    Domyślny

    DB-Library wartość
    ANSI_NULLS NA NA NA OD
    ANSI_PADDING NA NA NA OD
    ANSI_WARNINGS* NA NA NA OD
    ARITHABORT NA NA OD OD
    CONCAT_NULL_YIELDS_NULL NA NA NA OD
    NUMERIC_ROUNDABORT OD OD OD OD
    QUOTED_IDENTIFIER NA NA NA OD
    • Ustawienie ANSI_WARNINGS wartość ON niejawnie ustawia wartość ARITHABORT na WŁ., 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ą, opcja ARITHABORT musi być jawnie ustawiona na WARTOŚĆ WŁĄCZONE.

Jeśli opcje ZESTAWU są niepoprawne, mogą wystąpić następujące warunki:

  • Filtrowany indeks nie jest tworzony.
  • Aparat bazy danych generuje błąd i cofa instrukcje INSERT, UPDATE, DELETE lub MERGE, które zmieniają 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 Tworzenie filtrowanych indeksów oraz Architektura indeksu programu SQL Server i przewodnik projektowania.

Indeksy przestrzenne

Aby uzyskać informacje o indeksach przestrzennych, zobacz CREATE SPATIAL INDEX i Spatial Indexes Overview.

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 podczas tworzenia indeksu; jednak kolejne akcje wstawiania lub aktualizowania kolumn, które powodują, że całkowity rozmiar będzie większy niż limit, zakończy się niepowodzeniem. 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 jednostce alokacji IN_ROW_DATA, kolejne akcje wstawiania lub aktualizacji w kolumnie, która wypchnie dane poza wierszem, zakończy się niepowodzeniem.

Indeksy nieklastrowane mogą zawierać kolumny inne niż kluczowe na poziomie liści indeksu. Te kolumny nie są brane pod uwagę przez aparat bazy danych podczas obliczania rozmiaru klucza indeksu . Aby uzyskać więcej informacji, zobacz Create Indexes with Included Columns (Tworzenie indeksów z dołączonymi kolumnami) oraz SQL Server Index Architecture and Design Guide (Przewodnik projektowania i architektura 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 kolumny obliczeniowe 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 właściwości PERSISTED rozszerza typ kolumn obliczanych z możliwością indeksowania, aby uwzględnić następujące elementy:

  • 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 opcji ZESTAWU, 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ż obliczona kolumna c powoduje błąd arytmetyczny, instrukcja INSERT działa.

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

Jeśli zamiast tego po utworzeniu tabeli utworzysz indeks dla obliczonej kolumny c, ta sama instrukcja INSERT zakończy się teraz 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 w kolumnach obliczanych.

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ń lokalizuje wszystkie wymagane informacje ze skanowania indeksu; dostęp do danych tabeli lub indeksu klastrowanego nie jest dostępny. Aby uzyskać więcej informacji, zobacz Create Indexes with Included Columns (Tworzenie indeksów z dołączonymi kolumnami) oraz SQL Server Index Architecture and Design Guide (Przewodnik projektowania i architektura indeksu programu SQL Server).

Określanie opcji indeksu

Program SQL Server 2005 (9.x) wprowadził nowe opcje indeksu, a także modyfikuje sposób, w jaki są określone opcje. W składni zgodnej z poprzednimi wersjami WITH option_name jest równoważna 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 ograniczenie KLUCZ PODSTAWOWY lub UNIKATOWY, a definicja indeksu nie zostanie w żaden sposób zmieniona, indeks zostanie porzucony i ponownie utworzony z zachowaniem istniejącego ograniczenia. Jeśli jednak definicja indeksu zostanie zmieniona, instrukcja nie powiedzie się. Aby zmienić definicję ograniczenia KLUCZ PODSTAWOWY lub UNIKATOWY, 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 indeksy nieklastrowane wcześniej dopasowane do indeksu klastrowanego mogą już nie być wyrównane. Aby uzyskać więcej informacji na temat wyrównania indeksu partycjonowanego, zobacz Partycjonowane tabele i indeksy.

Klauzula DROP_EXISTING nie będzie ponownie sortować 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 opcja ONLINE jest ustawiona na wartość OFF. Jeśli indeks klastrowany jest wyłączony, należy wykonać operację CREATE INDEX WITH DROP_EXISTING z ustawieniem ONLINE na wartość OFF. Jeśli indeks nieklastrowany jest wyłączony i nie jest skojarzony z wyłączonym indeksem klastrowanym, można wykonać operację CREATE INDEX WITH DROP_EXISTING z włączonym lub wyłączonym trybem ONLINE.

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.

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 argumentu low_priority_lock_wait umożliwia podjęcie decyzji, jak operacja indeksu może być kontynuowana po zablokowaniu blokady Sch-M.

Aby uzyskać więcej informacji, zobacz Perform Index Operations Online.

Zasoby

Następujące zasoby są wymagane do wznowienia operacji tworzenia indeksu online:

  • Dodatkowe miejsce wymagane do utrzymania kompilowania indeksu, w tym czasu wstrzymania indeksu
  • 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.
  • Stan DDL uniemożliwiający wszelkie modyfikacje DDL
  • Czyszczenie duchów jest blokowane w indeksie kompilacji przez czas trwania operacji zarówno podczas wstrzymania, jak i podczas wykonywania operacji.

Bieżące ograniczenia funkcjonalności

Następujące funkcje są wyłączone dla operacji tworzenia indeksu z możliwością wznowienia:

  • Po wstrzymaniu operacji tworzenia indeksu online możliwego do wznowienia nie można zmienić początkowej wartości MAXDOP

  • Utwórz indeks zawierający:

    • Kolumny obliczone lub TIMESTAMP jako kolumny klucza
    • Kolumna LOB jako dołączona kolumna do tworzenia indeksu z możliwością wznowienia
    • Filtrowany indeks

Operacje indeksu z możliwością wznowienia

Dotyczy: SQL Server (począwszy od programu SQL Server 2019 (15.x)) i usługi Azure SQL Database

Następujące wytyczne dotyczą operacji indeksowania z możliwością wznowienia:

  • Tworzenie indeksu online jest określane jako wznawiane przy użyciu opcji RESUMABLE = ON.
  • 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.
  • opcja MAX_DURATION jest obsługiwana tylko w przypadku opcji RESUMABLE = ON.
  • MAX_DURATION dla opcji RESUMABLE określa interwał czasu dla tworzonego indeksu. Gdy ten czas zostanie użyty, kompilacja indeksu zostanie wstrzymana lub zakończy wykonywanie. Użytkownik decyduje, kiedy można wznowić kompilację dla wstrzymanego indeksu. Czas w minutach dla MAX_DURATION musi być dłuższy niż 0 minut i równy tydzień (7 * 24 * 60 = 10080 minut). Posiadanie długiej przerwy dla operacji indeksowania może mieć wpływ na wydajność DML w określonej tabeli, a także pojemność dysku bazy danych, ponieważ oba indeksy oryginalnego i nowo utworzonego wymagają miejsca na dysku i muszą zostać zaktualizowane podczas operacji 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.
  • Aby natychmiast wstrzymać operację indeksu, możesz zatrzymać (Ctrl-C) bieżące polecenie, wykonać polecenie ALTER INDEX PAUSE lub wykonać polecenie KILL <session_id>. Po wstrzymaniu polecenia można go wznowić przy użyciu polecenia ALTER INDEX.
  • Ponowne wykonanie oryginalnej instrukcji CREATE INDEX dla indeksu z możliwością wznowienia powoduje automatyczne wznowienie operacji tworzenia wstrzymanego indeksu.
  • Opcja SORT_IN_TEMPDB = ON nie jest obsługiwana dla indeksu możliwego do wznowienia.
  • Nie można wykonać polecenia DDL z RESUMABLE = ON wewnątrz jawnej transakcji (nie można być częścią bloku rozpoczynania TRAN ... COMMIT).
  • Aby wznowić/przerwać tworzenie/ponowne kompilowanie indeksu, użyj ALTER INDEX składni języka T-SQL.
  • Wyłączone indeksy nie są obsługiwane.

Nuta

Polecenie DDL jest uruchamiane do momentu zakończenia, wstrzymania lub niepowodzenia. Jeśli polecenie zostanie wstrzymane, zostanie wyświetlony błąd wskazujący, że operacja została wstrzymana i że tworzenie indeksu nie zostało ukończone. Więcej informacji o bieżącym stanie indeksu można uzyskać z sys.index_resumable_operations. Tak jak wcześniej w przypadku awarii zostanie również wystawiony błąd.

Aby wskazać, że tworzenie indeksu jest wykonywane jako operacja wznawiana i aby sprawdzić jego bieżący stan wykonywania, zobacz sys.index_resumable_operations.

WAIT_AT_LOW_PRIORITY z operacjami indeksowania online

Dotyczy: ta składnia dla CREATE INDEX dotyczy obecnie programu SQL Server 2022 (16.x), usługi Azure SQL Database i tylko usługi Azure SQL Managed Instance. W przypadku ALTER INDEXta składnia dotyczy programu SQL Server (począwszy od programu SQL Server 2014 (12.x)) i usługi Azure SQL Database. Aby uzyskać więcej informacji, zobacz ALTER INDEX.

Składnia low_priority_lock_wait umożliwia określenie zachowania WAIT_AT_LOW_PRIORITY. WAIT_AT_LOW_PRIORITY można używać tylko z ONLINE=ON.

Opcja WAIT_AT_LOW_PRIORITY umożliwia administratorom baz danych zarządzanie Sch-S i Sch-M blokadami wymaganymi do tworzenia indeksu online i umożliwia wybranie jednej z 3 opcji. We wszystkich 3 przypadkach, jeśli w czasie oczekiwania MAX_DURATION = n [minutes], nie ma żadnych działań blokujących, ponowne kompilowanie indeksu online jest wykonywane natychmiast bez oczekiwania i instrukcja DDL zostanie ukończona.

WAIT_AT_LOW_PRIORITY wskazuje, że operacja tworzenia indeksu online będzie czekać na blokady o niskim priorytcie, co umożliwia kontynuowanie innych operacji podczas oczekiwania na operację kompilacji indeksu online. 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 [MIN]

Czas oczekiwania (wartość całkowita określona w minutach), który indeks online tworzy blokady, będzie czekać z niskim priorytetem podczas wykonywania polecenia DDL. Jeśli operacja zostanie zablokowana przez czas MAX_DURATION, zostanie wykonana określona akcja ABORT_AFTER_WAIT. MAX_DURATION czas jest zawsze w minutach, a wyraz MINUTy można pominąć.

ABORT_AFTER_WAIT = [NONEBLOCKERS } ]

NONE Kontynuuj oczekiwanie na blokadę z normalnym (regularnym) priorytetem.

SELF Zakończ działanie indeksu online, aby utworzyć obecnie wykonywaną operację DDL bez podejmowania żadnych akcji. Opcji SELF nie można używać z MAX_DURATION 0.

BLOCKERS Zabije wszystkie transakcje użytkownika, które blokują operację DDL ponownego kompilowania indeksu online, aby operacja mogła kontynuować. Opcja BLOCKERS wymaga, aby logowanie miało uprawnienia .

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.

Sekwencyjne klucze

Dotyczy: SQL Server (począwszy od programu SQL Server 2019 (15.x)) i usługi Azure SQL Database

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 zostaną wstawione 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 PAGELATCH_EX dla danej strony.

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

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.

Wyświetlanie informacji o indeksie

Aby zwrócić informacje o indeksach, można użyć widoków wykazu, funkcji systemowych i procedur składowanych systemu.

Kompresja danych

Kompresja danych została opisana w temacie Kompresja danych. Poniżej przedstawiono kluczowe kwestie, które należy wziąć pod uwagę:

  • 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.

Następujące ograniczenia dotyczą indeksów partycjonowanych:

  • Nie można zmienić ustawienia kompresji pojedynczej partycji, jeśli tabela ma nieprzywiązane indeksy.
  • Składnia ALTER INDEX <index> ... REBUILD PARTITION ... ponownie kompiluje określoną partycję indeksu.
  • Składnia ALTER INDEX <index> ... REBUILD WITH ... ponownie kompiluje wszystkie partycje indeksu.

Aby ocenić, jak zmiana stanu kompresji wpłynie na 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 z tych samych 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, wykonaj zapytanie w kolumnie xml_compression widoku wykazu sys.partitions.

Uprawnienia

Wymaga ALTER uprawnienia do tabeli lub widoku lub członkostwa w db_ddladmin stałej 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 SQL Database nie obsługuje opcji grupy plików i strumienia plików.

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 (począwszy od programu SQL Server 2019 (15.x)) i usługi Azure SQL Database

-- 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 (począwszy od programu SQL Server 2019 (15.x)) i usługi Azure SQL Database

-- 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);

Zobacz też