CREATE INDEX (Transact-SQL)

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed InstanceAzure Synapse AnalyticsPlatform Platform System (PDW)

Vytvoří relační index v tabulce nebo zobrazení. Označuje se také jako index rowstore, protože se jedná o clusterovaný nebo neclusterovaný index B-tree. Index rowstore můžete vytvořit dříve, než budou v tabulce data. Pomocí indexu rowstore můžete zlepšit výkon dotazů, zejména pokud dotazy vybírají z konkrétních sloupců nebo vyžadují řazení hodnot v určitém pořadí.

Poznámka

Dokumentace používá termín B-tree obecně v odkazu na indexy. V indexech rowstore databázový stroj implementuje strom B+. To neplatí pro indexy columnstore ani indexy v tabulkách optimalizovaných pro paměť. Další informace najdete v SQL Serveru a architektuře indexu Azure SQL a průvodci návrhem.

Azure Synapse Analytics a systém PDW (Platform System) v současné době nepodporují jedinečná omezení. Všechny příklady odkazující na jedinečná omezení se vztahují pouze na SQL Server, Azure SQL Database a Azure SQL Managed Instance.

Informace o pokynech k návrhu indexu najdete v průvodci návrhem indexu SQL Serveru.

Příklady :

1. Vytvoření neclusterovaného indexu v tabulce nebo zobrazení

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. Vytvoření clusterovaného indexu v tabulce a použití názvu třetí části tabulky

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. Vytvoření neclusterovaného indexu s jedinečným omezením a určení pořadí řazení

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

Scénář klíče :

Počínaje SQL Serverem 2016 (13.x) ve službě Azure SQL Database a ve službě Azure SQL Managed Instance můžete pomocí neclusterovaného indexu indexu columnstore zlepšit výkon dotazů v datových skladech. Další informace najdete v tématu Indexy columnstore – datový sklad.

Další typy indexů najdete tady:

• CREATE XML INDEX
• VYTVOŘENÍ PROSTOROVÉHO INDEXu
• VYTVOŘENÍ INDEXU COLUMNSTORE

Transact-SQL konvence syntaxe

Syntax

Syntaxe pro SQL Server, Azure SQL Database, Azure SQL Managed Instance

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>

Zpětně kompatibilní relační index

Důležitý

Struktura syntaxe zpětně kompatibilního relačního indexu se odebere v budoucí verzi SQL Serveru. Nepoužívejte tuto strukturu syntaxe v nové vývojové práci a naplánujte úpravu aplikací, které tuto funkci aktuálně používají. Místo toho použijte strukturu syntaxe zadanou v <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
}

Syntaxe pro Azure Synapse Analytics a paralelní datový sklad

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

JEDINEČNÝ

Vytvoří jedinečný index v tabulce nebo zobrazení. Jedinečný index je jeden, ve kterém nejsou povoleny žádné dva řádky mají stejnou hodnotu klíče indexu.

Databázový stroj neumožňuje vytvoření jedinečného indexu u sloupců, které již obsahují duplicitní hodnoty, bez ohledu na to, zda IGNORE_DUP_KEY je nastavena na ONhodnotu . Pokud se o to pokusíte, databázový stroj zobrazí chybovou zprávu. Před vytvořením jedinečného indexu ve sloupci nebo sloupcích je nutné odebrat duplicitní hodnoty.

Omezení UNIQUE považuje NULL za hodnotu. Pokud je sloupec s možnou hodnotou null a UNIQUE ve sloupci existuje omezení, je povoleno maximálně jeden řádek s jedním řádkem NULL .

NASHROMÁŽDĚNÝ

Vytvoří index, ve kterém pořadí řazení zadané pro sloupce klíče indexu určuje pořadí stránek ve struktuře indexu na disku. Řádky na stránkách v dolní nebo listové úrovni clusterovaného indexu vždy obsahují všechny sloupce tabulky. Řádky na stránkách v horních úrovních indexu obsahují pouze klíčové sloupce.

Tabulka může mít pouze jeden clusterovaný index. Pokud clusterovaný index existuje v tabulce, obsahuje všechna data v tabulce. Tabulka bez clusterovaného indexu se nazývá halda.

Zobrazení s jedinečným clusterovaným indexem se nazývá indexované zobrazení. Indexované zobrazení může mít pouze jeden clusterovaný index. Vytvoření jedinečného clusterovaného indexu v zobrazení fyzicky materializuje zobrazení. Jedinečný clusterovaný index musí být vytvořen v zobrazení, aby bylo možné definovat všechny ostatní indexy ve stejném zobrazení. Další informace najdete v tématu Vytvoření indexovaných zobrazení.

Před vytvořením neclusterovaných indexů vytvořte clusterovaný index. Existující neclusterované indexy v tabulkách se znovu sestaví při vytvoření clusterovaného indexu, což je operace náročná na prostředky, pokud je tabulka velká.

Pokud není zadaný CLUSTERED, vytvoří se neclusterovaný index.

Poznámka

Vzhledem k tomu, že clusterovaný index obsahuje všechna data v tabulce, vytvoření clusterovaného indexu a efektivní použití ON partition_scheme_name klauzule nebo ON filegroup_name klauzule přesune tabulku ze skupiny souborů, na které byla tabulka vytvořena, do nového schématu oddílů nebo skupiny souborů. Před vytvořením tabulek nebo indexů v konkrétních skupinách souborů ověřte, které skupiny souborů jsou k dispozici a že mají dostatek volného místa pro index.

V některých případech může vytvoření clusterovaného indexu povolit dříve zakázané indexy. Další informace naleznete v tématu Povolení indexů a omezenía zakázání indexů a omezení.

NECLUSTERED

Vytvoří index, ve kterém pořadí řazení zadané pro sloupce klíče indexu určuje pořadí stránek ve struktuře indexu na disku. Na rozdíl od clusterovaného indexu řádky na stránkách na úrovni listu neclusterovaného indexu obsahují pouze sloupce klíče indexu. Volitelně můžete pomocí klauzule zahrnout INCLUDE podmnožinu sloupců, které nejsou klíči.

Každá tabulka může mít až 999 neclusterovaných indexů bez ohledu na to, jak se indexy vytvářejí: buď implicitně s omezeními PRIMARY KEY , UNIQUE nebo explicitně s CREATE INDEX.

U indexovaných zobrazení lze neclusterované indexy vytvořit pouze v zobrazení, které má již definovaný jedinečný clusterovaný index.

Pokud není zadán jinak, výchozí typ indexu neníclusterovaný.

index_name

Název indexu. Názvy indexů musí být jedinečné v rámci tabulky nebo zobrazení, ale nemusí být jedinečné v rámci databáze. Názvy indexů musí dodržovat pravidla identifikátorů .

sloupce

Sloupec nebo sloupce, na kterých je index založen. Zadejte dva nebo více názvů sloupců pro vytvoření složeného indexu pro kombinované hodnoty v zadaných sloupcích. Uveďte sloupce, které mají být zahrnuty do složeného indexu v pořadí podle priority řazení, uvnitř závorek za table_or_view_name.

Do jednoho složeného indexového klíče je možné zkombinovat až 32 sloupců. Všechny sloupce ve složené indexové klíči musí být ve stejné tabulce nebo zobrazení. Maximální povolená velikost kombinovaných hodnot indexu je 900 bajtů pro clusterovaný index nebo 1 700 pro neclusterovaný index. Limity jsou 16 sloupců a 900 bajtů pro verze před SQL Database a SQL Serverem 2016 (13.x).

Sloupce, které jsou datovými typy velkého objektu (LOB), ntext, textové, varchar(max), nvarchar(max), varbinary(max), xmlnebo obrázku nelze zadat jako klíčové sloupce indexu. Definice indexovaného zobrazení také nemůže obsahovat sloupce ntextu, textu nebo obrázku , i když se na ně v CREATE INDEX příkazu neodkazuje.

Indexy můžete vytvořit ve sloupcích typu definovaných uživatelem CLR, pokud typ podporuje binární řazení. Indexy můžete vytvořit také u počítaných sloupců, které jsou definované jako vyvolání metody ze sloupce definovaného uživatelem, pokud jsou metody označené deterministické a neprovádějí operace přístupu k datům. Další informace o indexování sloupců uživatelsky definovaných typů CLR najdete v tématu uživatelsky definované typy CLR.

[ ASC | DESC ]

Určuje vzestupný nebo sestupný směr řazení pro konkrétní sloupec indexu. Výchozí hodnota je ASC.

INCLUDE (sloupec [ ,... n ] )

Určuje sloupce bez klíče, které se mají přidat na úroveň listu neclusterovaného indexu. Neclusterovaný index může být jedinečný nebo ne jedinečný.

Názvy sloupců se v seznamu nedají opakovat INCLUDE a nedají se použít současně jako klíčové i neklíčové sloupce. Neclusterované indexy vždy implicitně obsahují sloupce clusterovaného indexu, pokud je v tabulce definován clusterovaný index. Další informace najdete v tématu Vytváření indexů se zahrnutými sloupci.

Všechny datové typy jsou povoleny s výjimkou textové, textu a obrázku. Počínaje SQL Serverem 2012 (11.x), ve službě Azure SQL Database a ve spravované instanci Azure SQL, pokud některý ze zadaných sloupců bez klíčů je varchar(max), nvarchar(max) nebo varbinary(max ), je možné index sestavit nebo znovu sestavit pomocí ONLINE této možnosti.

Počítané sloupce, které jsou deterministické a přesné nebo nepřesné, můžou být zahrnuté sloupce. Počítané sloupce odvozené z obrázku, ntextu, textu, varchar(max), nvarchar(max), varbinary(max) a datových typů XML lze zahrnout, pokud je datový typ počítaného sloupce povolený jako zahrnutý sloupec. Další informace najdete v tématu Indexy ve vypočítaných sloupcích.

Informace o vytvoření indexu XML naleznete v tématu CREATE XML INDEX.

WHERE <filter_predicate>

Vytvoří filtrovaný index zadáním řádků, které se mají zahrnout do indexu. Filtrovaný index musí být neclusterovaný index tabulky. Vytvoří filtrované statistiky pro řádky dat ve filtrovaného indexu.

Predikát filtru používá jednoduchou porovnávací logiku a nemůže odkazovat na počítaný sloupec, sloupec definované uživatelem (UDT), sloupec prostorového datového typu nebo sloupec datového typu hierarchie . Porovnání s NULL literály používajícími relační operátory nejsou povoleny. Místo toho použijte operátory IS NULL a IS NOT NULL.

Tady je několik příkladů predikátů filtru pro tabulku Production.BillOfMaterials:

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

WHERE ComponentID IN (533, 324, 753)

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

Filtrované indexy se nevztahují na indexy XML a fulltextové indexy. U UNIQUE indexů musí mít pouze vybrané řádky jedinečné hodnoty indexu. Filtrované indexy neumožňují možnost IGNORE_DUP_KEY.

ON partition_scheme_name ( column_name )

Určuje schéma oddílů, které definuje skupiny souborů, na které jsou namapovány oddíly děleného indexu. Schéma oddílů musí existovat v databázi spuštěním příkazu CREATE PARTITION SCHEME nebo ALTER PARTITION SCHEME. column_name určuje sloupec dělení indexu. Tento sloupec musí odpovídat datovému typu, délce a přesnosti argumentu funkce oddílu, který partition_scheme_name používá. column_name nejsou omezeny na sloupce v definici indexu. Můžete zadat libovolný sloupec v základní tabulce s výjimkou rozdělení jedinečného indexu, column_name musí být vybrány z těch, které se používají jako jedinečný klíč. Toto omezení umožňuje databázovému stroji ověřit jedinečnost hodnot klíčů pouze v rámci jednoho oddílu.

Poznámka

Když rozdělíte ne jedinečné clusterované indexy, databázový stroj ve výchozím nastavení přidá sloupec dělení do seznamu clusterovaných indexových klíčů, pokud ještě není zadaný. Při dělení nevýznamného neclusterovaného indexu přidá databázový stroj sloupec dělení jako sloupec bez klíče (zahrnutého) indexu, pokud ještě není zadaný.

Pokud partition_scheme_name nebo skupinu souborů nezadáte a tabulka se rozdělí, index se umístí do stejného schématu oddílů pomocí stejného sloupce dělení jako podkladová tabulka.

Poznámka

V indexu XML nelze zadat schéma dělení. Pokud je základní tabulka rozdělená na oddíly, index XML používá stejné schéma oddílů jako tabulka.

Další informace o dělení indexů, dělených tabulek a indexů.

ZAPNUTO filegroup_name

Vytvoří zadaný index pro zadanou skupinu souborů. Pokud není zadáno žádné umístění a tabulka nebo zobrazení nejsou rozdělené na oddíly, index použije stejnou skupinu souborů jako podkladová tabulka nebo zobrazení. Skupina souborů už musí existovat.

ZAPNUTO [výchozí]

Vytvoří zadaný index ve stejné skupině souborů nebo schématu oddílů jako tabulka nebo zobrazení.

Termín defaultv tomto kontextu není klíčovým slovem. Jedná se o identifikátor pro schéma skupiny souborů nebo dělené schéma tabulky nebo zobrazení a musí být oddělené jako v ON "default" nebo ON [default]. Pokud "default" je tato možnost zadaná, QUOTED_IDENTIFIER musí být ON pro aktuální relaci. Toto je výchozí nastavení. Další informace naleznete v tématu SET QUOTED_IDENTIFIER.

Poznámka

V kontextu CREATE INDEX"default" a [default] neukazujte výchozí skupinu souborů databáze. Označují schéma skupiny souborů nebo oddílu používané základní tabulkou nebo zobrazením. To se liší od CREATE TABLEmísta a "default"[default] umístění tabulky do výchozí skupiny souborů databáze.

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

Určuje umístění dat FILESTREAM pro tabulku při vytvoření clusterovaného indexu. Klauzule FILESTREAM_ON umožňuje přesun dat FILESTREAM do jiného schématu filegroup nebo oddílu FILESTREAM.

Filestream_filegroup_name je název filegroup FILESTREAM. Skupina souborů musí mít jeden soubor definovaný pro skupinu souborů pomocí příkazu CREATE DATABASE nebo ALTER DATABASE; v opačném případě se vyvolá chyba.

Pokud je tabulka rozdělená na oddíly, musí být zahrnuta klauzule FILESTREAM_ON a musí určovat schéma oddílů souborových skupin FILESTREAM, které používá stejnou funkci oddílu a sloupce oddílů jako schéma oddílů pro tabulku. V opačném případě se vyvolá chyba.

Pokud tabulka není rozdělená na oddíly, sloupec FILESTREAM nemůže být rozdělený na oddíly. Data FILESTREAM pro tabulku musí být uložena v jedné skupině souborů, která je zadána v klauzuli FILESTREAM_ON.

FILESTREAM_ON NULL lze zadat v příkazu CREATE INDEX, pokud se vytváří clusterovaný index a tabulka neobsahuje sloupec FILESTREAM.

Další informace naleznete v tématu FILESTREAM (SQL Server).

<objektu >::=

Plně kvalifikovaný nebo nekvalifikovaný objekt, který se má indexovat.

database_name

Název databáze.

schema_name

Název schématu, do kterého patří tabulka nebo zobrazení.

table_or_view_name

Název tabulky nebo zobrazení, které se má indexovat.

Chcete-li vytvořit index v zobrazení, musí být zobrazení definováno pomocí SCHEMABINDING. Před vytvořením neclusterovaného indexu musí být v zobrazení vytvořen jedinečný clusterovaný index. Další informace o indexovaných zobrazeních naleznete v tématu Poznámky.

Počínaje SQL Serverem 2016 (13.x) může být objekt tabulkou uloženou s clusterovaným indexem columnstore.

Azure SQL Database podporuje formát třídílných názvů <database_name>.<schema_name>.<object_name>, pokud je <database_name> aktuálním názvem databáze, nebo <database_name>tempdb a <object_name> začíná # nebo ##. Pokud je název schématu dbo, můžete <schema_name> vynechat.

<relational_index_option>::=

Určuje možnosti, které se mají použít při vytváření indexu.

PAD_INDEX = { ON | VYPNUTO }

Určuje odsazení indexu. Výchozí hodnota je OFF.

• NA

  Procento volného místa určeného faktorem výplně se použije na stránky indexu na střední úrovni. Pokud FILLFACTOR není zadán současně PAD_INDEX je nastavena na ON, použije se hodnota vyplnit v sys.indexes.

• PRYČ

  Zprostředkující stránky jsou vyplněné na blízkou kapacitě a dostatek místa pro alespoň jeden řádek maximální velikosti indexu může mít vzhledem k sadě klíčů na přechodných stránkách. K tomu dochází také v případě, že je PAD_INDEX nastavena na ON, ale není zadaný faktor výplně.

Možnost PAD_INDEX je užitečná pouze v případě FILLFACTOR , že je zadána, protože PAD_INDEX používá procento FILLFACTORurčené . Pokud procento zadané pro FILLFACTOR není dostatečně velké, aby umožňovalo jeden řádek, databázový stroj interně přepíše procento tak, aby umožňovalo minimum. Počet řádků na zprostředkující indexové stránce není nikdy menší než dvě, bez ohledu na to, jak nízká je hodnota FILLFACTOR.

V zpětně kompatibilní syntaxi je WITH PAD_INDEX ekvivalentní WITH PAD_INDEX = ON.

FILLFACTOR = fillfactor

Určuje procento, které určuje, jak by měl databázový stroj během vytváření nebo opětovného sestavení vytvořit úroveň listu každé stránky indexu. Hodnota fillfactor musí být celočíselná hodnota od 1 do 100. Hodnoty výplňového faktoru 0 a 100 jsou ve všech ohledech stejné. Pokud fillfactor je 100, databázový stroj vytvoří indexy s listovými stránkami vyplněnými do kapacity.

Nastavení FILLFACTOR platí pouze v případech, kdy se index vytvoří nebo znovu sestaví. Databázový stroj dynamicky neuchová zadané procento prázdného místa na stránkách.

Chcete-li zobrazit nastavení faktoru vyplnění, použijte fill_factor sloupec v zobrazení katalogu sys.indexes .

Důležitý

Vytvoření indexu s FILLFACTOR menší než 100 zvyšuje velikost prostoru úložiště, který data zabírá, protože databázový stroj data redistribuuje podle faktoru vyplnění při vytváření nebo opětovném sestavení indexu.

Další informace naleznete v tématu Určení vyplnit faktoru indexu.

SORT_IN_TEMPDB = { ON | VYPNUTO }

Určuje, zda se mají ukládat dočasné výsledky řazení do tempdb. Výchozí hodnota je OFF s výjimkou hyperškálování Služby Azure SQL Database. U všech operací sestavení indexu v Hyperscale se SORT_IN_TEMPDB vždy ON, pokud se nepouužijí znovu použitelné sestavení indexu. U obnovovatelných sestavení indexů je SORT_IN_TEMPDB vždy OFF.

• NA

  Přechodné výsledky řazení, které se používají k sestavení indexu, jsou uloženy v tempdb. To může zkrátit dobu potřebnou k vytvoření indexu. Tím se ale zvýší množství místa na disku, které se použije při sestavení indexu.

• PRYČ

  Výsledky zprostředkujícího řazení jsou uloženy ve stejné databázi jako index.

Kromě místa potřebného k vytvoření indexu tempdb v uživatelské databázi musí mít přibližně stejné množství místa pro uložení výsledků přechodného řazení. Další informace naleznete v tématu SORT_IN_TEMPDB možnost indexů.

V zpětně kompatibilní syntaxi je WITH SORT_IN_TEMPDB ekvivalentní WITH SORT_IN_TEMPDB = ON.

IGNORE_DUP_KEY = { ON | VYPNUTO }

Určuje chybovou odpověď, když se operace vložení pokusí vložit duplicitní hodnoty klíče do jedinečného indexu. Možnost IGNORE_DUP_KEY se vztahuje pouze na operace vložení po vytvoření nebo vytvoření indexu. Možnost nemá žádný vliv při provádění CREATE INDEX, ALTER INDEXnebo UPDATE. Výchozí hodnota je OFF.

• NA

  Při vložení duplicitních hodnot klíče do jedinečného indexu dojde k upozornění. Nevloží se jenom řádky, které porušují omezení jedinečnosti.

• PRYČ

  Při vložení duplicitních hodnot klíče do jedinečného indexu se zobrazí chybová zpráva. Celý INSERT příkaz se vrátí zpět.

IGNORE_DUP_KEY nelze nastavit na ON pro indexy vytvořené v zobrazení, ne jedinečné indexy, indexy XML, prostorové indexy a filtrované indexy.

Chcete-li zobrazit nastavení IGNORE_DUP_KEY indexu, použijte sloupec ignore_dup_key v zobrazení katalogu sys.indexes.

V zpětně kompatibilní syntaxi je WITH IGNORE_DUP_KEY ekvivalentní WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | VYPNUTO}

Určuje, jestli se přepočítají statistiky. Výchozí hodnota je OFF.

• NA

  Zastaralé statistiky se automaticky nepřepočítají.

• PRYČ

  Jsou povoleny automatické aktualizace statistik.

Pokud chcete obnovit automatickou aktualizaci statistik, nastavte STATISTICS_NORECOMPUTE na OFF nebo spusťte UPDATE STATISTICS bez klauzule NORECOMPUTE.

Varování

Pokud automatické přepočítání statistik zakážete nastavením STATISTICS_NORECOMPUTE = ON, můžete optimalizaci dotazů zabránit v výběru optimálních plánů provádění pro dotazy zahrnující tabulku.

Nastavení STATISTICS_NORECOMPUTE na ON nezabrání aktualizaci statistik indexu, ke kterým dochází během operace opětovného sestavení indexu.

V zpětně kompatibilní syntaxi je WITH STATISTICS_NORECOMPUTE ekvivalentní WITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = { ON | VYPNUTO }

platí pro: SQL Server 2014 (12.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance

Když ONse vytvoří statistika pro jednotlivé oddíly. Když OFFse strom statistiky vyřadí a SQL Server znovu vypočítá statistiku. Výchozí hodnota je OFF.

Pokud statistiky jednotlivých oddílů nejsou podporované, možnost se ignoruje a vygeneruje se upozornění. Přírůstkové statistiky nejsou podporovány v následujících případech:

• Statistiky vytvořené pomocí indexů, které nejsou v souladu se základní tabulkou
• Statistiky vytvořené v sekundárních databázích s možností čtení AlwaysOn
• Statistiky vytvořené pro databáze jen pro čtení
• Statistiky vytvořené pro filtrované indexy
• Statistiky vytvořené v zobrazeních
• Statistiky vytvořené v interních tabulkách
• Statistiky vytvořené pomocí prostorových indexů nebo indexů XML

DROP_EXISTING = { ON | VYPNUTO }

Je možnost odstranit a znovu sestavit existující clusterovaný nebo neclusterovaný index s upravenými specifikacemi sloupců a zachovat stejný název indexu. Výchozí hodnota je OFF.

• NA

  Určuje, že chcete odstranit a znovu sestavit existující index, který musí mít stejný název jako parametr index_name.

• PRYČ

  Určuje, že se má existující index vynechat a znovu sestavit. SQL Server zobrazí chybu, pokud zadaný název indexu již existuje.

Pomocí DROP_EXISTINGmůžete změnit:

• Neclusterovaný index rowstore do clusterovaného indexu rowstore.

V DROP_EXISTINGnemůžete změnit:

• Clusterovaný index rowstore do neclusterovaného indexu rowstore.
• Clusterovaný index columnstore libovolného typu indexu rowstore.

V zpětně kompatibilní syntaxi je WITH DROP_EXISTING ekvivalentní WITH DROP_EXISTING = ON.

ONLINE = { ON | VYPNUTO }

Určuje, jestli jsou podkladové tabulky a přidružené indexy dostupné pro dotazy a úpravy dat během operace indexu. Výchozí hodnota je OFF.

Důležitý

Online indexovací operace nejsou k dispozici v každé edici Microsoft SQL Serveru. Seznam funkcí podporovaných edicemi SQL Serveru najdete v tématu Edice a podporované funkce systému SQL Server 2022.

• NA

  Dlouhodobé zámky tabulek se neuchovávají po dobu trvání operace indexu. Během hlavní fáze operace indexu se ve zdrojové tabulce uchovává pouze zámek sdíleného záměru (IS). To umožňuje pokračovat v dotazech nebo aktualizacích podkladové tabulky a indexů. Na začátku operace se na zdrojovém objektu nachází sdílený zámek (S) po krátkou dobu. Na konci operace se po krátkou dobu na objekt získá sdílený zámek (S) v případě, že se vytváří neclusterovaný index. Zámek schématu (Sch-M) se získá při vytvoření nebo vyřazení clusterovaného indexu online a při vytvoření clusterovaného nebo neclusterovaného indexu. ONLINE nelze nastavit na ON při vytváření indexu v místní dočasné tabulce.

  Poznámka

  Možnost WAIT_AT_LOW_PRIORITY můžete použít k omezení nebo zabránění blokování během online operací indexu. Další informace najdete v tématu WAIT_AT_LOW_PRIORITY s operacemi online indexu.

• PRYČ

  Zámky tabulek se použijí po dobu trvání operace indexu. Offline operace indexu, která vytváří, znovu sestavuje nebo zahodí clusterovaný, prostorový nebo index XML nebo znovu sestaví nebo zamkne neclusterovaný index, získá v tabulce zámek schématu (Sch-M). Tím zabráníte všem uživatelům přístup k podkladové tabulce po dobu trvání operace. Operace offline indexu, která vytvoří neclusterovaný index, na začátku získá sdílený zámek (S) v tabulce. To brání úpravám definice podkladové tabulky, ale umožňuje čtení a úpravy dat v tabulce, zatímco probíhá sestavení indexu.

Další informace naleznete v tématu Provádění operací indexu online a Pokyny pro operace online indexů.

Indexy, včetně indexů v globálních dočasných tabulkách, lze vytvořit online s výjimkou následujících případů:

• Index XML
• Indexování místní dočasné tabulky
• Počáteční jedinečný clusterovaný index v zobrazení
• Zakázané clusterované indexy
• Clusterované indexy columnstore v SQL Serveru 2017 (14.x)) a starších verzích
• Neclusterované indexy columnstore v SQL Serveru 2016 (13.x)) a starších verzích
• Skupinový index, pokud podkladová tabulka obsahuje datové typy LOB (image, ntext, text) a prostorové datové typy
• varchar(max) a varbinary(max) sloupce nemůžou být součástí indexového klíče. V SQL Serveru (počínaje SQL Serverem 2012 (11.x)) ve službě Azure SQL Database a ve službě Azure SQL Managed Instance je možné pomocí této možnosti vytvořit nebo znovu sestavit ONLINE clusterovaný index obsahující další sloupce.
• Neclusterované indexy v tabulce s clusterovaným indexem columnstore

Další informace naleznete v tématu Jak online operace indexu fungují.

RESUMABLE = { ON | VYPNUTO }

platí pro: SQL Server 2019 (15.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance

Určuje, jestli je operace online indexu obnovitelná. Další informace naleznete v tématu Resumable index operations and Resumable index considerations.

• NA

  Operace indexu je obnovitelná.

• PRYČ

  Operace indexu není obnovitelná.

MAX_DURATION = čas [MINUTES] použitý s RESUMABLE = ON (vyžaduje ONLINE = ON)

platí pro: SQL Server 2019 (15.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance

Určuje, jak dlouho se v minutách provede obnovitelná operace indexu, než se pozastaví.

ALLOW_ROW_LOCKS = { ON | VYPNUTO }

Určuje, jestli jsou povolené zámky řádků. Výchozí hodnota je ON.

• NA

  Zámky řádků jsou povoleny při přístupu k indexu. Databázový stroj určuje, kdy se používají zámky řádků.

• PRYČ

  Zámky řádků se nepoužívají.

ALLOW_PAGE_LOCKS = { ON | VYPNUTO }

Určuje, jestli jsou povolené zámky stránek. Výchozí hodnota je ON.

• NA

  Zámky stránek jsou povoleny při přístupu k indexu. Databázový stroj určuje, kdy se používají zámky stránek.

• PRYČ

  Zámky stránek se nepoužívají.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | VYPNUTO }

platí pro: SQL Server 2019 (15.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance

Určuje, zda se má optimalizovat, aby nedocházelo k kolizím vložení poslední stránky. Výchozí hodnota je OFF. Další informace najdete v části Sekvenční klíče .

MAXDOP = max_degree_of_parallelism

Přepíše maximální stupeň paralelismu možnost konfigurace pro operaci indexu. Další informace najdete v tématu Konfigurace maximálního stupně paralelismu Možnosti konfigurace serveru. Pomocí MAXDOP omezte stupeň paralelismu a výsledné spotřeby prostředků pro operaci sestavení indexu.

max_degree_of_parallelism může být:

• 1

  Potlačí generování paralelního plánu.

• >1

  Omezuje maximální stupeň paralelismu použitý v operaci paralelního indexu na zadané číslo nebo méně na základě aktuální systémové úlohy.

• 0 (výchozí)

  Používá stupeň paralelismu zadaný na úrovni serveru, databáze nebo skupiny úloh, pokud není omezen na základě aktuální systémové úlohy.

Další informace najdete v tématu Konfigurace paralelních operací indexu.

Poznámka

Paralelní indexovací operace nejsou k dispozici v každé edici Microsoft SQL Serveru. Seznam funkcí podporovaných edicemi SQL Serveru najdete v tématu Edice a podporované funkce systému SQL Server 2022.

DATA_COMPRESSION

Určuje možnost komprese dat pro zadaný index, číslo oddílu nebo rozsah oddílů. Možnosti jsou následující:

• ŽÁDNÝ

  Indexované nebo zadané oddíly nejsou komprimované. To neplatí pro indexy columnstore.

• VESLOVAT

  Indexované nebo zadané oddíly se komprimují pomocí komprese řádků. To neplatí pro indexy columnstore.

• STRÁNKA

  Index nebo zadané oddíly se komprimují pomocí komprese stránky. To neplatí pro indexy columnstore.

• COLUMNSTORE

  platí pro: SQL Server 2014 (12.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance

  Platí pouze pro indexy columnstore, včetně neclusterovaných indexů columnstore i clusterovaných indexů columnstore.

• COLUMNSTORE_ARCHIVE

  platí pro: SQL Server 2014 (12.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance

  Platí pouze pro indexy columnstore, včetně neclusterovaných indexů columnstore i clusterovaných indexů columnstore. COLUMNSTORE_ARCHIVE dále komprimuje zadaný oddíl na menší velikost. Můžete ho použít pro archivaci nebo v jiných situacích, které vyžadují menší velikost úložiště a mohou si dovolit více času pro ukládání a načítání.

Další informace o kompresi naleznete v tématu Komprese dat.

XML_COMPRESSION

platí pro: SQL Server 2022 (16.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance

Určuje možnost komprese XML pro zadaný index, který obsahuje jeden nebo více xml sloupce datového typu. Možnosti jsou následující:

• NA

  Indexování nebo zadané oddíly jsou komprimovány pomocí komprese XML.

• PRYČ

  Indexování nebo zadané oddíly nejsou komprimovány pomocí komprese XML.

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

Určuje oddíly, na které se vztahují nastavení DATA_COMPRESSION nebo XML_COMPRESSION. Pokud index není rozdělený do oddílů, ON PARTITIONS argument vygeneruje chybu. Pokud není klauzule ON PARTITIONS k dispozici, použije se možnost DATA_COMPRESSION nebo XML_COMPRESSION pro všechny oddíly děleného indexu.

<partition_number_expression> lze zadat následujícími způsoby:

• Zadejte číslo oddílu, například: ON PARTITIONS (2).
• Zadejte čísla oddílů pro několik jednotlivých oddílů oddělených čárkami, například: ON PARTITIONS (1, 5).
• Zadejte rozsahy i jednotlivé oddíly, například: ON PARTITIONS (2, 4, 6 TO 8).

<range> lze zadat jako čísla oddílů oddělená klíčovým slovem TO, například: ON PARTITIONS (6 TO 8).

Pokud chcete nastavit různé typy komprese dat pro různé oddíly, zadejte DATA_COMPRESSION možnost více než jednou, například:

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

Můžete také zadat XML_COMPRESSION možnost více než jednou, například:

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

Poznámky

Při vytváření plánu dotazu pro příkaz CREATE INDEX se optimalizátor dotazů může rozhodnout, že místo prohledávání tabulky prohledá jiný index. Operace řazení může být v některých situacích vyloučena. V počítačích CREATE INDEX s více procesory může paralelismus použít pro operace prohledávání a řazení spojené s vytvářením indexu stejným způsobem jako ostatní dotazy. Další informace najdete v tématu Konfigurace paralelních operací indexu.

Operace CREATE INDEX může být zaprotokolována minimálně, pokud je model obnovení databáze nastavený buď na hromadně protokolovaný, nebo jednoduchý.

Indexy lze vytvořit v dočasné tabulce. Při vyřazení tabulky nebo výpadku oboru se indexy zahodí.

Clusterovaný index je postaven na proměnné tabulky při přidání omezení primárního klíče. Podobně je neclusterovaný index postaven na proměnné tabulky při přidání jedinečného omezení. Když proměnná tabulky přestane být oborem, indexy se zahodí.

Indexy podporují rozšířené vlastnosti.

CREATE INDEX se v Microsoft Fabric nepodporuje.

Clusterované indexy

Vytvoření clusterovaného indexu v tabulce (haldě) nebo vyřazení a opětovné vytvoření existujícího clusterovaného indexu vyžaduje, aby byl v databázi k dispozici další pracovní prostor, který bude vyhovovat řazení dat, a dočasnou kopii původní tabulky nebo existujících dat clusterovaného indexu. Další informace o clusterovaných indexech najdete v tématu Vytváření clusterovaných indexů a průvodce návrhem a architekturou indexu SQL Serveru.

Neclusterované indexy

Počínaje SQL Serverem 2016 (13.x) ve službě Azure SQL Database a ve službě Azure SQL Managed Instance můžete vytvořit neclusterovaný index v tabulce uložené jako clusterovaný index columnstore. Pokud nejprve vytvoříte neclusterovaný index v tabulce uložené jako haldu nebo clusterovaný index, index se zachová, pokud později převedete tabulku na clusterovaný index columnstore. Při opětovném sestavení clusterovaného indexu columnstore není také nutné vynechat neclusterovaný index.

Možnost FILESTREAM_ON není platná při vytváření neclusterovaného indexu v tabulce uložené jako clusterovaný index columnstore.

Jedinečné indexy

Pokud existuje jedinečný index, databázový stroj kontroluje duplicitní hodnoty při každém přidání nebo změně dat. Operace, které by vygenerovaly duplicitní hodnoty klíče, se vrátí zpět a databázový stroj vrátí chybovou zprávu. To platí i v případě, že operace přidání nebo úpravy dat změní mnoho řádků, ale způsobí pouze jeden duplikát. Pokud se pokusíte vložit řádky, pokud existuje jedinečný index s možností nastavenou IGNORE_DUP_KEY na ON, řádky, které porušují jedinečný index, budou ignorovány.

Dělené indexy

Dělené indexy se vytvářejí a udržují podobným způsobem jako dělené tabulky, ale stejně jako běžné indexy se zpracovávají jako samostatné databázové objekty. V tabulce, která není rozdělená na oddíly, můžete mít dělený index a v tabulce, která je rozdělená do oddílů, můžete mít nedílený index.

Pokud vytváříte index v dělené tabulce a nezadáte skupinu souborů, na kterou se má index umístit, index se rozdělí stejným způsobem jako podkladová tabulka. Důvodem je to, že indexy se ve výchozím nastavení umístí do stejných skupin souborů jako jejich podkladové tabulky a pro dělenou tabulku ve stejném schématu oddílů, které používají stejné sloupce dělení. Pokud index používá stejné schéma oddílů a sloupec dělení jako tabulka, index je zarovnaný s tabulkou.

Varování

Vytvoření a opětovné sestavení nerovnaných indexů v tabulce s více než 1 000 oddíly je možné, ale nepodporuje se. To může způsobit snížení výkonu nebo nadměrné využití paměti během těchto operací. Doporučujeme použít pouze zarovnané indexy, pokud počet oddílů překročí 1 000.

Při dělení ne jedinečného clusterovaného indexu databázový stroj ve výchozím nastavení přidá do seznamu clusterovaných indexových klíčů všechny sloupce dělení, pokud ještě nejsou zadané.

Indexovaná zobrazení lze vytvořit v dělených tabulkách stejným způsobem jako indexy v tabulkách. Další informace o dělených indexech najdete v tématu Dělené tabulky a indexy a průvodce návrhem a architekturou indexu SQL Serveru.

Při vytvoření nebo vytvoření indexu dotaz optimalizuje statistiky o indexu. Pro dělený index používá optimalizátor dotazů výchozí algoritmus vzorkování místo prohledávání všech řádků v tabulce za nedílný index. Pokud chcete získat statistiky o dělených indexech prohledáváním všech řádků v tabulce, použijte CREATE STATISTICS nebo UPDATE STATISTICS s klauzulí FULLSCAN.

Filtrované indexy

Filtrovaný index je optimalizovaný neclusterovaný index, který je vhodný pro dotazy, které z tabulky vyberou malé procento řádků. Používá predikát filtru k indexování části dat v tabulce. Dobře navržený filtrovaný index může zlepšit výkon dotazů, snížit náklady na úložiště a snížit náklady na údržbu.

Požadované možnosti SET pro filtrované indexy

Možnosti SET ve sloupci Požadovaná hodnota jsou vyžadovány vždy, když dojde k některé z následujících podmínek:

• Vytvoříte filtrovaný index.

• Výraz INSERT, UPDATE, DELETEnebo MERGE příkaz upravuje data ve filtrované indexu.

• Filtrovaný index používá optimalizátor dotazů k vytvoření plánu dotazu.

  možnost SET	Požadovaná hodnota	Výchozí hodnota serveru	Výchozí hodnota OLE DB a ODBC	Výchozí hodnota DB-Library
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS 1	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

  ¹ Nastavení ANSI_WARNINGS implicitně ON nastaví ARITHABORT , ON když je úroveň kompatibility databáze nastavena na 90 nebo vyšší. Pokud je úroveň kompatibility databáze nastavená na 80 nebo starší, ARITHABORT musí být možnost explicitně nastavena na ONhodnotu .

SET Pokud jsou možnosti nesprávné, mohou nastat následující podmínky:

• Vytvoření filtrovaného indexu se nezdaří.
• Databázový stroj vygeneruje chybu a vrátí INSERTzpět příkaz , UPDATEDELETE, nebo MERGE příkaz, který mění data v indexu.
• Optimalizátor dotazů nebere v plánu provádění index pro žádné příkazy Transact-SQL.

Další informace o filtrovaných indexech najdete v tématu Vytvoření filtrovaných indexů a průvodce návrhem a architekturou indexu SQL Serveru.

Prostorové indexy

Informace o prostorových indexech naleznete v tématu CREATE SPATIAL INDEX a Spatial indexes overview.

Indexy XML

Informace o indexech XML naleznete CREATE XML INDEX a XML Indexy (SQL Server).

Velikost klíče indexu

Maximální velikost klíče indexu je 900 bajtů pro clusterovaný index a 1 700 bajtů pro neclusterovaný index. (Před SQL Database a SQL Serverem 2016 (13.x) byl limit vždy 900 bajtů.) Indexy u sloupců varchar , které překračují limit bajtů, lze vytvořit, pokud existující data ve sloupcích nepřekračují limit v době vytvoření indexu; následné operace vložení nebo aktualizace sloupců, které způsobují, že celková velikost je větší než limit, se nezdaří. Klíč indexu seskupeného indexu nemůže obsahovat varchar sloupce, které mají aktuální data v ROW_OVERFLOW_DATA alokační jednotce. Pokud je clusterovaný index vytvořen ve sloupci varchar a existující data jsou v IN_ROW_DATA alokační jednotce, následné operace vložení nebo aktualizace ve sloupci, které by nasdílely data mimo řádek, selžou.

Neclusterované indexy můžou obsahovat sloupce bez klíče (zahrnuté) na úrovni listu indexu. Tyto sloupce databázový stroj při výpočtu velikosti klíče indexu nepovažuje za tyto sloupce. Další informace najdete v tématu Vytváření indexů s zahrnutými sloupci a průvodce návrhem a architekturou indexu SQL Serveru.

Poznámka

Pokud jsou tabulky rozdělené na oddíly, sloupce klíče dělení ještě nejsou v ne jedinečném clusterovém indexu, přidají se do indexu databázovým strojem. Kombinovaná velikost indexovaných sloupců (nepočítají se zahrnuté sloupce) a žádné přidané sloupce dělení nesmí překročit 1800 bajtů v ne jedinečném clusterovém indexu.

Počítané sloupce

Indexy je možné vytvořit na počítaných sloupcích. Kromě toho počítané sloupce mohou mít vlastnost PERSISTED. To znamená, že databázový stroj ukládá vypočítané hodnoty v tabulce a aktualizuje je, když se aktualizují všechny další sloupce, na kterých závisí vypočítaný sloupec. Databázový stroj používá tyto trvalé hodnoty při vytváření indexu ve sloupci a při odkazování na index v dotazu.

Aby bylo možné indexovat vypočítaný sloupec, musí být vypočítaný sloupec deterministický a přesný. Použití PERSISTED vlastnosti ale rozšiřuje typ indexovatelných počítaných sloupců, aby zahrnoval:

• Počítané sloupce založené na funkcích Transact-SQL a CLR a metodách typů definovaných uživatelem CLR, které jsou označeny deterministicky uživatelem.
• Počítané sloupce založené na výrazech, které jsou deterministické podle definice databázového stroje, ale nepřesné.

Trvalé počítané sloupce vyžadují, aby byly nastaveny následující SET možnosti, jak je znázorněno v předchozí části Požadované možnosti SET pro filtrované indexy.

PRIMARY KEY Omezení UNIQUE může obsahovat počítaný sloupec, pokud splňuje všechny podmínky indexování. Konkrétně musí být vypočítaný sloupec deterministický a přesný nebo deterministický a trvalý. Další informace o determinismu naleznete v tématu Deterministické a nedeterministické funkce.

Vypočítané sloupce odvozené zobrázku , ntext, textové, varchar(max), nvarchar(max), varbinary(max)a xml datových typů lze indexovat buď jako klíč, nebo zahrnout sloupec bez klíče, pokud je datový typ vypočítaného sloupce povolený jako sloupec s klíčem indexu nebo neklíčový sloupec. Nemůžete například vytvořit primární index XML ve vypočítané xml sloupci. Pokud velikost klíče indexu přesahuje 900 bajtů, zobrazí se zpráva s upozorněním.

Vytvoření indexu ve vypočítaném sloupci může způsobit selhání operace vložení nebo aktualizace, která dříve fungovala. K takovému selhání může dojít, když vypočítaný sloupec způsobí aritmetickou chybu.

Například v následující tabulce se zdá, že výraz počítaného sloupce c má za následek aritmetickou chybu při vložení řádku, INSERT příkaz funguje.

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

Pokud však vytvoříte index pro počítaný sloupec c, stejný INSERT příkaz selže.

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

Další informace najdete v tématu Indexy ve vypočítaných sloupcích.

Zahrnuté sloupce v indexech

Neklíčové sloupce, označované jako zahrnuté sloupce, lze přidat na úroveň listu neclusterovaného indexu, aby se zlepšil výkon dotazů pokrytím dotazu. To znamená, že všechny sloupce odkazované v dotazu se do indexu zahrnou jako klíčové nebo neklíčové sloupce. To umožňuje optimalizátoru dotazů získat všechny požadované informace z neclusterovaného indexového prohledávání nebo hledání; k tabulce nebo clusterovaným datům indexu se nepřistupuje. Další informace najdete v tématu Vytváření indexů s zahrnutými sloupci a průvodce návrhem a architekturou indexu SQL Serveru.

Určení možností indexu

SQL Server 2005 (9.x) zavedl nové možnosti indexu a také upravil způsob, jakým jsou zadány možnosti. V zpětně kompatibilní syntaxi WITH option_name je ekvivalentní WITH (option_name = ON). Při nastavování možností indexu platí následující pravidla:

• Nové možnosti indexu lze zadat pouze pomocí WITH (<option_name> = <ON | OFF>).
• Možnosti nelze zadat pomocí zpětně kompatibilní i nové syntaxe ve stejném příkazu. Například zadání WITH (DROP_EXISTING, ONLINE = ON) způsobí selhání příkazu.
• Při vytváření indexu XML je nutné zadat možnosti pomocí WITH (<option_name> = <ON | OFF>).

klauzule DROP_EXISTING

Klauzuli DROP_EXISTING můžete použít k opětovnému sestavení indexu, přidání nebo přetažení sloupců, úpravě možností řazení sloupců nebo změně schématu oddílů nebo skupiny souborů.

Pokud index vynucuje PRIMARY KEY omezení a UNIQUE definice indexu se nijak nezmění, index se zahodí a znovu vytvoří zachování stávajícího omezení. Pokud je však definice indexu změněna, příkaz selže. Pokud chcete změnit definici PRIMARY KEY nebo UNIQUE omezení, přetáhněte omezení a přidejte omezení s novou definicí.

DROP_EXISTING zvyšuje výkon při opětovném vytvoření clusterovaného indexu se stejnou nebo jinou sadou klíčů v tabulce, která má také neclusterované indexy. DROP_EXISTING nahradí spuštění příkazu DROP INDEX na starém clusterovém indexu následovaného spuštěním příkazu CREATE INDEX nového clusterovaného indexu. Neclusterované indexy se znovu sestaví jednou a pak pouze v případě, že se změnila definice indexu. Klauzule DROP_EXISTING nepřebuduje neclusterované indexy, pokud má definice indexu stejný název indexu, sloupce klíčů a oddílů, atribut jedinečnosti a pořadí řazení jako původní index.

Bez ohledu na to, jestli se neclusterované indexy znovu sestaví, zůstanou vždy ve svých původních skupinách souborů nebo schématech oddílů a používají původní funkce oddílů. Pokud se clusterovaný index znovu sestaví do jiné skupiny souborů nebo schématu oddílů, nepřesouvají se neclusterované indexy tak, aby se shodovaly s novým umístěním clusterovaného indexu. Proto i v případě, že se neclusterované indexy dříve zarovnaly se clusterovaným indexem, nemusí být už s ním zarovnané. Další informace o zarovnání dělených indexů najdete v tématu Dělené tabulky a indexy.

Klauzule DROP_EXISTING data seřadí znovu, pokud jsou stejné sloupce klíče indexu použity ve stejném pořadí a se stejným vzestupným nebo sestupným pořadím, pokud příkaz indexu neurčí neclusterovaný index a ONLINE možnost je nastavená na OFF. Pokud je clusterovaný index zakázaný, CREATE INDEX WITH DROP_EXISTING musí být operace provedena s nastavenou ONLINE na OFFhodnotu . Pokud je neclusterovaný index zakázaný a není přidružený k zakázanému clusterovaného indexu, CREATE INDEX WITH DROP_EXISTING lze operaci provést s nastaveným OFF parametrem ONLINE nebo ON.

Poznámka

Když se indexy s rozsahy 128 nebo více vyřadí nebo znovu sestaví, databázový stroj odblokuje skutečné přidělení stránky a jejich přidružené zámky, dokud po potvrzení transakce. Další informace naleznete v tématu Deferred deallocation.

Možnost ONLINE

Pro online provádění operací indexu platí následující pokyny:

• Podkladovou tabulku nelze změnit, zkrátit ani vynechat, když probíhá online operace indexu.
• Během operace indexu je vyžadováno další dočasné místo na disku.
• Online operace je možné provádět s dělenými indexy a indexy, které obsahují trvalé počítané sloupce nebo zahrnuté sloupce.
• Možnost WAIT_AT_LOW_PRIORITY argumentu umožňuje rozhodnout, jak operace indexu pokračuje, když čeká na Sch-M zámek. Další informace najdete v tématu WAIT_AT_LOW_PRIORITY

Další informace naleznete v tématu Provádění operací indexu online.

Operace indexu s možností obnovení

platí pro: SQL Server 2019 (15.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance

Operaci vytvoření online indexu můžete obnovit. To znamená, že sestavení indexu je možné zastavit a později restartovat z místa, kde se zastavil. Pokud chcete spustit sestavení indexu jako obnovitelné, zadejte RESUMABLE = ON možnost.

Následující pokyny platí pro obnovení operací indexu:

• Pokud chcete použít možnost RESUMABLE, musíte také použít možnost ONLINE.
• Možnost RESUMABLE se neuchovává v metadatech daného indexu a vztahuje se pouze na dobu trvání aktuálního příkazu DDL. Proto musí být klauzule RESUMABLE = ON zadána explicitně, aby bylo možné obnovitelnost.
• Možnost MAX_DURATION lze zadat ve dvou kontextech:
  • MAX_DURATION RESUMABLE pro možnost určuje časový interval pro znovu sestavený index. Po uplynutí této doby a pokud je opětovné sestavení indexu stále spuštěné, je pozastaveno. Rozhodnete se, kdy bude možné obnovit opětovné sestavení pozastaveného indexu. Doba v minutách pro MAX_DURATION musí být větší než 0 minut a menší než nebo rovna jednomu týdnu (7 * 24 * 60 = 10080 minut). Dlouhé pozastavení operace indexu může výrazně ovlivnit výkon DML na konkrétní tabulce i kapacitu disku databáze, protože původní index i nově vytvořený index vyžadují místo na disku a musí být aktualizovány operacemi DML. Pokud není MAX_DURATION možnost vynechána, operace indexu pokračuje, dokud nedojde k dokončení nebo dokud nedojde k selhání.
  • MAX_DURATION pro možnost WAIT_AT_LOW_PRIORITY určuje dobu čekání pomocí zámků s nízkou prioritou, pokud je operace indexu zablokovaná, před provedením akce. Další informace najdete v tématu WAIT_AT_LOW_PRIORITY s operacemi online indexu.
• Pokud chcete operaci indexu pozastavit okamžitě, můžete spustit příkaz ALTER INDEX PAUSE nebo spustit příkaz KILL <session_id>.
• Opětovné spuštění původního CREATE INDEX příkazu se stejnými parametry obnoví pozastavenou operaci sestavení indexu. Spuštěním příkazu můžete také obnovit pozastavenou operaci sestavení indexu ALTER INDEX RESUME .
• Příkaz ABORT ukonče relaci, na které běží sestavení indexu, a zruší operaci indexu. Operaci indexu, která byla přerušena, nelze obnovit.

Obnovitelná operace indexu se spustí, dokud se nedokončí, pozastaví nebo selže. Pokud se operace pozastaví, zobrazí se chyba, která značí, že operace byla pozastavena a že se vytvoření indexu nedokončilo. V případě selhání operace se také zobrazí chyba.

Pokud chcete zjistit, jestli se operace indexu provádí jako obnovitelná operace a chcete zkontrolovat její aktuální stav spuštění, použijte zobrazení katalogu sys.index_resumable_operations.

Prostředky

Pro operace s obnovitelným indexem jsou vyžadovány následující prostředky:

• Další místo potřebné k zachování sestavení indexu, včetně času pozastavení sestavení.
• Další propustnost protokolu během fáze řazení. Celkové využití místa v protokolu pro obnovitelný index je menší než běžné vytvoření online indexu a umožňuje zkrácení protokolu během této operace.
• Příkazy DDL, které se pokoušejí upravit tabulku přidruženou k indexu, který se vytváří, zatímco operace indexu je pozastavena, nejsou povoleny.
• Čištění duchů je v indexu v buildu blokováno po dobu trvání operace, a to jak během pozastavení, tak i v době, kdy je operace spuštěná.
• Pokud tabulka obsahuje sloupce LOB, vyžaduje opětovné sestavení clusterovaného indexu na začátku operace zámek schématu (Sch-M).

Aktuální funkční omezení

Operace vytváření obnovitelného indexu mají následující omezení:

• Po pozastavení operace vytvoření obnovitelného online indexu nelze počáteční hodnotu MAXDOP změnit.
• Možnost SORT_IN_TEMPDB = ON není podporovaná pro operace s obnovitelným indexem.
• Příkaz DDL s RESUMABLE = ON nelze spustit uvnitř explicitní transakce.
• Nelze vytvořit obnovitelný index, který obsahuje:
  • Počítané nebo timestamp (rowversion) sloupce jako klíčové sloupce
  • Obchodní sloupec jako zahrnutý sloupec.
• Operace indexu s možností obnovení nejsou podporovány pro:
  • Příkaz ALTER INDEX REBUILD ALL
  • Příkaz ALTER TABLE REBUILD
  • Indexy sloupcové
  • Filtrované indexy
  • Zakázané indexy

WAIT_AT_LOW_PRIORITY s online indexovacími operacemi

platí pro: SQL Server 2022 (16.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance

Pokud tuto možnost nepoužíváte WAIT_AT_LOW_PRIORITY , musí se všechny aktivní blokující transakce obsahující zámky v tabulce nebo indexu dokončit, aby se operace vytvoření indexu spustila a dokončila. Po spuštění operace online indexu a před jejím dokončením musí získat sdílenou (S) nebo změnu schématu (Sch-M) uzamknout v tabulce a po krátkou dobu ji podržet. I když se zámek uchovává jen na krátkou dobu, může výrazně ovlivnit propustnost úloh, zvýšit latenci dotazů nebo způsobit vypršení časového limitu provádění.

Abyste se těmto problémům vyhnuli, možnost WAIT_AT_LOW_PRIORITY umožňuje spravovat chování S nebo Sch-M zámky vyžadované pro spuštění a dokončení online indexovací operace výběrem ze tří možností. Ve všech případech, pokud během doby čekání určené MAX_DURATION = n [minutes] neexistuje žádné blokování, které zahrnuje operaci indexu, operace indexu okamžitě pokračuje.

WAIT_AT_LOW_PRIORITY čeká operace online indexu pomocí zámků s nízkou prioritou, což umožňuje dalším operacím, které používají normální zámky s prioritou, pokračovat do té doby. Vynechání možnosti WAIT_AT_LOW_PRIORITY odpovídá WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = čas [MINUTES]

Doba čekání (celočíselná hodnota zadaná v minutách), kterou operace online indexu čeká pomocí zámků s nízkou prioritou. Pokud je operace zablokovaná pro MAX_DURATION čas, provede se zadaná ABORT_AFTER_WAIT akce. MAX_DURATION čas je vždy v minutách a slovo MINUTES je možné vynechat.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

• NONE: Pokračujte v čekání na zámek s normální prioritou.
• SELF: Ukončete aktuálně spuštěnou online operaci indexu, aniž byste provedli žádnou akci. Možnost SELF nelze použít, pokud je MAX_DURATION 0.
• BLOCKERS: Ukončete všechny uživatelské transakce, které blokují operaci online indexu, aby operace mohl pokračovat. Možnost BLOCKERS vyžaduje, aby objekt zabezpečení spouštěný příkazem CREATE INDEX nebo ALTER INDEX měl oprávnění ALTER ANY CONNECTION.

Pomocí následujících rozšířených událostí můžete monitorovat operace indexu, které čekají na zámky s nízkou prioritou:

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Možnosti zámků řádků a stránek

Pokud jsou při přístupu k indexu povoleny zámky na úrovni řádků, stránek a tabulek ALLOW_ROW_LOCKS = ON a ALLOW_PAGE_LOCK = ON. Databázový stroj zvolí příslušný zámek a může zámek eskalovat z řádku nebo zámku stránky na zámek tabulky.

Při ALLOW_ROW_LOCKS = OFF a ALLOW_PAGE_LOCK = OFFje při přístupu k indexu povolen pouze zámek na úrovni tabulky.

Varování

Nedoporučuje se zakázat uzamčení řádků nebo stránek v indexu. Můžou nastat problémy související s souběžností a některé funkce můžou být nedostupné. Například index nelze změnit uspořádání, pokud je ALLOW_PAGE_LOCKS nastavena na OFF.

Sekvenční klávesy

Platí pro: SQL Server 2019 (15.x) a novější verze, ve službě Azure SQL Database a ve spravované instanci Azure SQL.

Kolize vložení poslední stránky je běžný problém s výkonem, ke kterému dochází, když se velký počet souběžných vláken pokusí vložit řádky do indexu se sekvenčním klíčem. Index se považuje za sekvenční, pokud počáteční klíčový sloupec obsahuje hodnoty, které se neustále zvyšují (nebo snižují), například sloupec identity nebo datum, které je výchozí pro aktuální datum a čas. Protože vložené klíče jsou sekvenční, všechny nové řádky se vloží na konec struktury indexu – jinými slovy, na stejné stránce. To vede k kolizí stránky v paměti, které lze pozorovat jako několik vláken čekajících na získání západky pro danou stránku. Odpovídající typ čekání je PAGELATCH_EX.

Povolením možnosti indexu OPTIMIZE_FOR_SEQUENTIAL_KEY povolíte optimalizaci v databázovém stroji, která pomáhá zlepšit propustnost vkládání do indexu s vysokou souběžností. Je určen pro indexy, které mají sekvenční klíč, a proto jsou náchylné k závěru kolize vložení poslední stránky, ale může také pomoci s indexy, které mají aktivní místa v jiných oblastech struktury indexu B-Tree.

Poznámka

Dokumentace používá termín B-tree obecně v odkazu na indexy. V indexech rowstore databázový stroj implementuje strom B+. To neplatí pro indexy columnstore ani indexy v tabulkách optimalizovaných pro paměť. Další informace najdete v SQL Serveru a architektuře indexu Azure SQL a průvodci návrhem.

Komprese dat

Další informace o kompresi dat naleznete v tématu Komprese dat.

Tady jsou klíčové body, které je potřeba vzít v úvahu v kontextu operací sestavení indexu při použití komprese dat:

• Komprese umožňuje ukládání více řádků na stránce, ale nemění maximální velikost řádku.
• Jiné než listové stránky indexu nejsou komprimované, ale dají se komprimovat řádky.
• Každý neclusterovaný index má individuální nastavení komprese a nedědí nastavení komprese podkladové tabulky.
• Při vytvoření clusterovaného indexu v haldě clusterovaný index zdědí stav komprese haldy, pokud není zadán alternativní stav komprese.

Pokud chcete vyhodnotit, jak změna stavu komprese ovlivňuje využití místa tabulkou, indexem nebo oddílem, použijte sp_estimate_data_compression_savings uloženou proceduru.

Komprese XML

platí pro: SQL Server 2022 (16.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance.

Mnoho aspektů komprese dat platí pro kompresi XML. Měli byste také vědět o následujících aspektech:

• Při zadání seznamu oddílů lze u jednotlivých oddílů povolit kompresi XML. Pokud není zadaný seznam oddílů, jsou všechny oddíly nastavené tak, aby používaly kompresi XML. Při vytvoření tabulky nebo indexu je komprese dat XML zakázána, pokud není zadáno jinak. Při úpravě tabulky se stávající komprese zachová, pokud není zadáno jinak.
• Pokud zadáte seznam oddílů nebo oddíl, který je mimo rozsah, vygeneruje se chyba.
• Při vytvoření clusterovaného indexu v haldě clusterovaný index zdědí stav komprese XML haldy, pokud není zadána alternativní možnost komprese.
• Změna nastavení komprese XML haldy vyžaduje, aby byly všechny neclusterované indexy v tabulce znovu sestaveny tak, aby měly ukazatele na nová umístění řádků v haldě.
• Kompresi XML můžete povolit nebo zakázat online nebo offline. Povolení komprese haldy je jedno vlákno pro online operaci.
• Chcete-li určit stav komprese XML oddílů v dělené tabulce, použijte xml_compression sloupec sys.partitions zobrazení katalogu.

Statistika indexu

Při vytvoření indexu rowstore vytvoří databázový stroj také statistiky klíčových sloupců indexu. Název objektu statistiky v zobrazení katalogu sys.stats odpovídá názvu indexu. Pro nedělený index se statistiky sestavují pomocí úplného prohledávání dat. Pro dělený index se statistiky sestavují pomocí výchozího algoritmu vzorkování.

Při vytvoření indexu columnstore vytvoří databázový stroj také objekt statistiky v sys.stats . Tento objekt statistiky neobsahuje data statistik, jako jsou histogram a vektor hustoty. Používá se při vytváření klonu databáze skriptováním databáze. V té době se příkazy a UPDATE STATISTICS ... WITH STATS_STREAM příkazy používají k získání metadat columnstore, DBCC SHOW_STATISTICS jako jsou segment, slovník a rozdílová velikost úložiště, a přidají je do statistiky indexu columnstore. Tato metadata se získávají dynamicky v době kompilace dotazu pro běžnou databázi, ale poskytuje ji objekt statistiky pro klon databáze. Příkaz UPDATE STATISTICS není podporován pro objekt statistiky indexu columnstore v žádném jiném scénáři.

Dovolení

ALTER Vyžaduje oprávnění k tabulce nebo zobrazení nebo členství v db_ddladmin pevné databázové roli.

Omezení a omezení

V systému Azure Synapse Analytics a Analytics Platform System (PDW) nemůžete vytvářet:

• Clusterovaný nebo neclusterovaný index rowstore v tabulce datového skladu, pokud již index columnstore existuje. Toto chování se liší od SQL Serveru SMP, který umožňuje, aby indexy rowstore i columnstore existovaly společně ve stejné tabulce.
• V zobrazení nelze vytvořit index.

Metadata

Chcete-li zobrazit informace o existujících indexech, můžete dotazovat sys.indexes zobrazení katalogu.

Poznámky k verzi

• Azure SQL Database nepodporuje jiné skupiny souborů než PRIMARY.
• Azure SQL Database a Azure SQL Managed Instance nepodporují možnosti FILESTREAM.
• Indexy columnstore nejsou k dispozici před SQL Serverem 2012 (11.x).
• Operace obnovení indexu jsou dostupné od SQL Serveru 2017 (14.x), ve službě Azure SQL Database a ve službě Azure SQL Managed Instance.

Příklady: Všechny verze. Používá databázi AdventureWorks.

A. Vytvoření jednoduchého neclusterovaného indexu rowstore

Následující příklady vytvoří neclusterovaný index ve sloupci VendorID tabulky 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. Vytvoření jednoduchého neclusterovaného složeného indexu rowstore

Následující příklad vytvoří neclusterovaný složený index na SalesQuota a SalesYTD sloupce tabulky Sales.SalesPerson.

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

C. Vytvoření indexu v tabulce v jiné databázi

Následující příklad vytvoří clusterovaný index ve sloupci VendorID tabulky ProductVendor v databázi Purchasing.

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

D. Přidání sloupce do indexu

Následující příklad vytvoří index IX_FF se dvěma sloupci z dbo. Tabulka FactFinance. Další příkaz znovu sestaví index s jedním dalším sloupcem a zachová existující název.

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

Příklady: SQL Server, Azure SQL Database

E. Vytvoření jedinečného neclusterovaného indexu

Následující příklad vytvoří jedinečný neclusterovaný index ve sloupci Name tabulky Production.UnitMeasure v AdventureWorks2022 databázi. Index vynutí jedinečnost dat vložených do sloupce Name.

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

Následující dotaz testuje omezení jedinečnosti tím, že se pokusí vložit řádek se stejnou hodnotou jako v existujícím řádku.

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

Výsledná chybová zpráva:

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. Použití možnosti IGNORE_DUP_KEY

Následující příklad ukazuje účinek IGNORE_DUP_KEY možnost vložením více řádků do dočasné tabulky nejprve s možností nastavenou na ON a znovu s možností nastavenou na OFF. Do tabulky #Test se vloží jeden řádek, který záměrně způsobí duplicitní hodnotu při spuštění druhého víceřádkového příkazu INSERT. Počet řádků v tabulce vrátí počet vložených řádků.

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

Tady jsou výsledky druhého příkazu INSERT.

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

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

Všimněte si, že řádky vložené z tabulky Production.UnitMeasure, které neporušovaly omezení jedinečnosti, byly úspěšně vloženy. Bylo vydáno upozornění a duplicitní řádek ignorován, ale celá transakce nebyla vrácena zpět.

Stejné příkazy se spustí znovu, ale s IGNORE_DUP_KEY nastaveným 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

Tady jsou výsledky druhého příkazu 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

Všimněte si, že do tabulky Production.UnitMeasure nebyly vloženy žádné řádky z tabulky, i když omezení indexu UNIQUE porušilo pouze jeden řádek v tabulce.

G. Použití DROP_EXISTING k vyřazení a opětovnému vytvoření indexu

Následující příklad zahodí a znovu vytvoří existující index ve sloupci ProductID tabulky Production.WorkOrder v AdventureWorks2022 databázi pomocí možnosti DROP_EXISTING. Jsou také nastaveny možnosti FILLFACTOR a PAD_INDEX.

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

H. Vytvoření indexu v zobrazení

Následující příklad vytvoří zobrazení a index v tomto zobrazení. Součástí jsou dva dotazy, které používají indexované zobrazení.

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

Já. Vytvoření indexu se zahrnutými (neklíčovými) sloupci

Následující příklad vytvoří neclusterovaný index s jedním klíčovým sloupcem (PostalCode) a čtyřmi neklíčovými sloupci (AddressLine1, AddressLine2, City, StateProvinceID). Dotaz, na který se vztahuje index, následuje. Pokud chcete zobrazit index vybraný optimalizátorem dotazů, v nabídce Query v aplikaci SQL Server Management Studio vyberte Zobrazit skutečný plán provádění před spuštěním dotazu.

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. Vytvoření děleného indexu

Následující příklad vytvoří neclusterovaný dělený index na TransactionsPS1, existující schéma oddílů v AdventureWorks2022 databázi. Tento příklad předpokládá, že se nainstalovala ukázka děleného indexu.

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

K. Vytvoření filtrovaného indexu

Následující příklad vytvoří filtrovaný index v tabulce Production.BillOfMaterials v databázi AdventureWorks2022. Predikát filtru může obsahovat sloupce, které nejsou klíčovými sloupci ve filtrovaném indexu. Predikát v tomto příkladu vybere pouze řádky, ve kterých je Hodnota EndDate nenulová.

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

L. Vytvoření komprimovaného indexu

Následující příklad vytvoří index pro tabulku, která není součástí, pomocí komprese řádků.

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

Následující příklad vytvoří index pro dělenou tabulku pomocí komprese řádků ve všech oddílech indexu.

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

Následující příklad vytvoří index v dělené tabulce pomocí komprese stránky na oddílu 1 komprese indexu a řádku u oddílů 2 prostřednictvím 4 indexu.

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. Vytvoření indexu s kompresí XML

platí pro: SQL Server 2022 (16.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance.

Následující příklad vytvoří index u tabulky, která není součástí, pomocí komprese XML. Nejméně jeden sloupec v indexu musí být datový typ xml.

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

Následující příklad vytvoří index pro dělenou tabulku pomocí komprese XML pro všechny oddíly indexu.

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

N. Vytvoření, obnovení, pozastavení a přerušení obnovovatelných operací indexu

platí pro: SQL Server 2019 (15.x) a novější verze, Azure SQL Database a 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. CREATE INDEX s různými možnostmi uzamčení s nízkou prioritou

Následující příklady používají možnost WAIT_AT_LOW_PRIORITY k určení různých strategií pro řešení blokování.

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

Následující příklad používá možnost RESUMABLE a určuje dvě MAX_DURATION hodnoty, první platí pro ABORT_AFTER_WAIT možnost, druhá platí pro RESUMABLE možnost.

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

Příklady: Azure Synapse Analytics a Analytický platformový systém (PDW)

P. Základní syntaxe

Vytvoření, obnovení, pozastavení a přerušení obnovovatelných operací indexu

platí pro: SQL Server 2019 (15.x) a novější verze, Azure SQL Database a 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. Vytvoření neclusterovaného indexu v tabulce v aktuální databázi

Následující příklad vytvoří neclusterovaný index ve sloupci VendorID tabulky ProductVendor.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. Vytvoření clusterovaného indexu v tabulce v jiné databázi

Následující příklad vytvoří neclusterovaný index ve sloupci VendorID tabulky ProductVendor v Purchasing databázi.

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

S. Vytvoření uspořádaného clusterovaného indexu v tabulce

Následující příklad vytvoří uspořádaný clusterovaný index ve sloupcích c1 a c2 tabulky T1 v databázi MyDB.

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

T. Převod CCI na uspořádaný clusterovaný index v tabulce

Následující příklad převede existující clusterovaný index columnstore na uspořádaný clusterovaný index columnstore s názvem MyOrderedCCI na c1 a c2 sloupce tabulky T2 v databázi MyDB.

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

Související obsah

• průvodce architekturou a návrhem indexu SQL Serveru
• provádět online operace indexování
• indexy a ALTER TABLE
• ALTER INDEX
• VYTVOŘENÍ FUNKCE ODDÍLU
• VYTVOŘENÍ SCHÉMATU ODDÍLŮ
• VYTVOŘENÍ PROSTOROVÉHO INDEXu
• VYTVOŘIT STATISTIKY
• CREATE TABLE
• CREATE XML INDEX
• datových typů
• DBCC SHOW_STATISTICS
• DROP INDEX
• indexy XML (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA