Ajax toolkit
Ajax tutorials
|
|
7.5. Indexing XML Dataxml data type instances are stored as binary large objects (BLOB ) in xml data type columns. If these columns are not indexed, they must be shredded at runtime for each row in the table to evaluate a query. This can be costly, especially with large xml data type instances or a large number of rows in the table. Building primary and secondary XML indexes on xml data type columns can significantly improve query performance.
An xml data type column can have one primary XML index and multiple secondary XML indexes, where:
An XML index can be created only on a single xml data type column. XML indexes cannot be created on the following:
A relational index cannot be created on an xml data type column. You must set the SQL Server 2005 options listed in Table 7-4 when creating or rebuilding an XML index on an xml data type column. If these options are not set, you will not be able to create or rebuild the XML index, and you will not be able to insert values into or modify values in indexed xml data type columns.
Primary and secondary indexes on xml data type columns are created, changed, and dropped similarly to indexes on non-xml data type columns. The following subsections describe managing indexes on xml data type columns. 7.5.1. Creating an XML IndexThe CREATE INDEX statement is used to create a new primary or secondary XML index on an xml data type column. The syntax is:
CREATE [ PRIMARY ] XML INDEX index_name
ON <object> ( xml_column_name )
[ USING XML INDEX xml_index_name
[ FOR { VALUE | PATH | PROPERTY } ]
[ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]
<object> ::=
{ [ database_name . [ schema_name ] . | schema_name . ] table_name }
<xml_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
}
where:
A table must have a clustered primary key with less than 16 columns in it before a primary XML index can be created. The following example creates a primary XML index on the xmlCol xml data type column in the xmlTable table created in the "Creating xml Data Type Columns and Variables" section earlier in this chapter:
CREATE PRIMARY XML INDEX xmlColIndex
ON xmlTable(xmlCol)
The following example creates a secondary VALUE index on the xmlCol column:
CREATE XML INDEX xmlColValueIndex
ON xmlTable(xmlCol)
USING XML INDEX xmlColIndex
FOR VALUE
7.5.2. Altering an XML IndexThe ALTER INDEX statement is used to modify an existing XML index created using the CREATE INDEX statement. The syntax is:
ALTER INDEX { index_name | ALL }
ON <object>
{ REBUILD
[ WITH ( <rebuild_index_option> [ ,...n ] ) ]
| DISABLE
| SET ( <set_index_option> [ ,...n ] )
}
[ ; ]
<object> ::=
{
[ database_name. [ schema_name ] . | schema_name. ]
table_or_view_name
}
<rebuild_index_option > ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
}
<set_index_option>::=
{
ALLOW_ROW_LOCKS= { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
}
The arguments are described in the "Creating an XML Index" subsection earlier in this section. You need supply arguments only for index characteristics that you are changing. 7.5.3. Dropping an XML IndexThe DROP INDEX statement is used to remove one or more XML indexes from the database. The syntax is:
DROP INDEX
{ index_name ON <object> [ ,...n ] }
<object> ::=
{
[ database_name. [ schema_name ] . | schema_name. ]
table_or_view_name
}
The arguments are described in the "Creating an XML Index" subsection earlier in this section. The following example drops the secondary value index created on the xmlTable table in the "Creating an XML Index" subsection earlier in this section:
DROP INDEX xmlColValueIndex ON xmlTable
7.5.4. Viewing XML IndexesThe xml_indexes catalog view returns information about primary and secondary XML indexes in a database. The following query returns the XML indexes on the Individual table in AdventureWorks:
USE AdventureWorks
SELECT o.name TableName, xi.*
FROM sys.xml_indexes xi
JOIN sys.objects o ON xi.object_id = o.object_id
WHERE o.name = 'Individual'
Partial results are shown in Figure 7-11. Figure 7-11. Results for viewing XML indexes example In the example, the sys.xml_indexes catalog view is joined to the sys.objects catalog view to return as the first column in the result set the table that the index belongs to. |
|
|
Ajax toolkit
Ajax tutorials
