ALTER INDEX (Selective XML Indexes)
TOC
Collapse the table of content
Expand the table of content

ALTER INDEX (Selective XML Indexes)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2012)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Modifies an existing selective XML index. The ALTER INDEX statement changes one or more of the following items:

  • The list of indexed paths (FOR clause).

  • The list of namespaces (WITH XMLNAMESPACES clause).

  • The index options (WITH clause).

You cannot alter secondary selective XML indexes. For more information, see Create, Alter, and Drop Secondary Selective XML Indexes.

Topic link icon Transact-SQL Syntax Conventions

  
ALTER INDEX index_name  
    ON <table_object>   
    [WITH XMLNAMESPACES ( <xmlnamespace_list> )]  
    FOR ( <promoted_node_path_action_list> )  
    [WITH ( <index_options> )]  
  
<table_object> ::=   
{ [database_name. [schema_name ] . | schema_name. ] table_name }  
<promoted_node_path_action_list> ::=   
<promoted_node_path_action_item> [, <promoted_node_path_action_list>]  
  
<promoted_node_path_action_item>::=   
<add_node_path_item_action> | <remove_node_path_item_action>  
  
<add_node_path_item_action> ::=  
ADD <path_name> = <promoted_node_path_item>  
  
<promoted_node_path_item>::=  
<xquery_node_path_item> | <sql_values_node_path_item>  
  
<remove_node_path_item_action> ::= REMOVE <path_name>   
  
<path_name_or_typed_node_path>::=   
<path_name> | <typed_node_path>  
  
<typed_node_path> ::=   
<node_path> [[AS XQUERY <xsd_type_ext>] | [AS SQL <sql_type>]]  
  
<xquery_node_path_item> ::=   
<node_path> [AS XQUERY <xsd_type_or_node_hint>] [SINGLETON]  
  
<xsd_type_or_node_hint> ::=   
[<xsd_type>] [MAXLENGTH(x)] | 'node()'  
  
<sql_values_node_path_item> ::=   
<node_path> AS SQL <sql_type> [SINGLETON]  
  
<node_path> ::=   
character_string_literal  
  
<xsd_type_ext> ::=   
character_string_literal  
  
<sql_type> ::=   
identifier  
  
<path_name> ::=   
identifier  
  
<xmlnamespace_list> ::=   
<xmlnamespace_item> [, <xmlnamespace_list>]  
  
<xmlnamespace_item> ::=   
<xmlnamespace_uri> AS <xmlnamespace_prefix>  
  
<xml_namespace_uri> ::= character_string_literal  
<xml_namespace_prefix> ::= identifier  
  
<index_options> ::=   
(   
  | PAD_INDEX  = { ON | OFF }  
  | FILLFACTOR = fillfactor  
  | SORT_IN_TEMPDB = { ON | OFF }  
  | IGNORE_DUP_KEY =OFF  
  | DROP_EXISTING = { ON | OFF }  
  | ONLINE =OFF  
  | ALLOW_ROW_LOCKS = { ON | OFF }  
  | ALLOW_PAGE_LOCKS = { ON | OFF }  
  | MAXDOP = max_degree_of_parallelism  
)  

index_name
Is the name of the existing index to alter.

<table_object>
Is the table that contains the XML column to index. Use one of the following formats:

  • database_name.schema_name.table_name

  • database_name..table_name

  • schema_name.table_name

  • table_name

[WITH XMLNAMESPACES ( <xmlnamespace_list> )]
Is the list of namespaces used by the paths to index. For information about the syntax of the WITH XMLNAMESPACES clause, see WITH XMLNAMESPACES (Transact-SQL).

FOR ( <promoted_node_path_action_list> )
Is the list of indexed paths to add or remove.

  • ADD a path. When you ADD a path, you use the same syntax that is used to create paths with the CREATE SELECTIVE XML INDEX statement. For information about the paths that you can specify in the CREATE or ALTER statement, see Specify Paths and Optimization Hints for Selective XML Indexes.

  • REMOVE a path. When you REMOVE a path, you provide the name that was given to the path when it was created.

[WITH ( <index_options> )]
You can only specify <index_options> when you use ALTER INDEX without the FOR clause. When you use ALTER INDEX to add or remove paths in the index, the index options are not valid arguments. For information about the index options, see CREATE XML INDEX (Selective XML Indexes).

System_CAPS_ICON_important.jpg Important


When you run an ALTER INDEX statement, the selective XML index is always rebuilt. Be sure to consider the impact of this process on server resources.

Permissions

ALTER permission on the table or view is required to run ALTER INDEX.

The following example shows an ALTER INDEX statement. This statement adds the path '/a/b/m' to the XQuery part of the index and deletes the path '/a/b/e' from the SQL part of the index created in the example in the topic CREATE SELECTIVE XML INDEX (Transact-SQL). The path to delete is identified by the name that was given to it when it was created.

ALTER INDEX sxi_index  
ON Tbl  
FOR   
(  
    ADD pathm = '/a/b/m' as XQUERY 'node()' ,  
    REMOVE pathabe  
);  

The following example shows an ALTER INDEX statement that specifies index options. Index options are permitted because the statement does not use a FOR clause to add or remove paths.

ALTER INDEX sxi_index  
ON Tbl  
PAD_INDEX = ON;  

Selective XML Indexes (SXI)
Create, Alter, and Drop Selective XML Indexes
Specify Paths and Optimization Hints for Selective XML Indexes

Community Additions

ADD
Show:
© 2016 Microsoft