Skip to main content

Types of Secondary XML Indexes in SQL Server

These are non-clustered index of the primary XML index. There must be a primary xml index before each secondary xml index. Following are some types of secondary xml indexes:

Path Indexes

The path index is built on the path value columns of the primary XML indexes. This index improves the performance of queries that use paths and values to select data.

For example, if you execute a query that checks for the existence of a product model ID using an XQuery expression as /PD:ProductDescription/@ProductModelID[.=”19”], you can create a path secondary index on the CatalogDescription column of the ProductModel table. In this path index, you can use the primary index created previously.

The following statement creates a Path index on the CatalogDescription column:

CREATE XML INDEX PIdx_ProductModel_CatalogDescription_PATH ON Production.ProductModel (CatalogDesctiption)USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH

The preceding code create a path index, Pldx_ProductModel_CatalogDesctiption_PATH

Value Indexes

The value indexes contain the same items as path indexes but in the reverse order. It contains the value of the column first and then the path id. This index improves the performance of queries that use paths to select data.

For example, if you execute a query that checks the existence of a node in an XQuery expression such as//Item@ProductID=”1”], you can create a value secondary index by using the primary index created previously.

The following statement creates a value index on the CatalogDesctiption column:

CREATE XML INDEX PIdx_ProductModel_CatalogDesctiption_VALUE ON Production.ProductModel (CatalogDesctiption)
USING XML INDEX PXML_ProductModel_CatalogDescription
FOR VALUE

The preceding code creates a value index,
PIdx_ProductModel_CatalogDescription_VALUE on the CatalogDescription column of the table.

Property Indexes

The property index contains the primary key of the base table, path id, and the clause columns of primary XML indexes. This index improves the performance of queries that use paths to select data.

For example, if you execute a query that returns a value of the node in an XQuery expression, such as /ItemList/Item/@ProductID)[1], you can create a property secondary index on the CatalogDescription column of the ProductModel table by using the following statement:

CREATE XML INDEX PIdx_ProductModel_CatalogDescription_PROPERTY ON Production.ProductModel (CatalogDescription)
USING XML INDEX PXML_ProductModel_CatalogDescription FOR PROPERTY

The preceding code creates a property index, PIdx_ProductModel_CatalogDescription_PROPERTY, on the CatalogDescription column of the table.

You need to consider the following guidelines while creating an XML index:

  • XML indexes can be created only on XML columns.
  • XML indexes only support indexing a single XML column.
  • XML indexes can only be added to tables, views, table-valued variables with XML column or XML variables.
  • XML indexes created on a table do not allow you to modify the primary key. To do so, you first need to drop all the XML indexes on the table.

Comments

Popular posts from this blog

difference between structure and union in C Language

In c language article we will see the difference between union and structure. Both are the user define datatype in c language. See the table which is mentioned below: ASP.NET Video Tutorial Series Structure Union1.The keywordstruct is used to define a structure 1. The keyword union is used to define a union. 2. When a variable is associated with a structure, the compiler allocates the memory for each member. The size of structure is greater than or equal to the sum ofsizes of its members. The smaller members may end with unused slack bytes. 2. When a variable is associated with a union, thecompiler allocates thememory by considering the size of the largest memory. So, size of union is equal to the size of largest member. 3. Each member within a structure is assigned unique storage area of location. 3. Memory allocated is shared by individual members of union. 4. The address of each member will be in ascending order This indicates that memory for each member will start at different offset v…

Difference between Linear search and Binary Search in c language

SQL Video Channel : Download all SQL Video



Binary Search Linear Search Works only on sorted items. such as  1,2,3,4,5,6  etc
Works on sorted as well as unsorted items. 12,4,5,3,2,1 etc Very efficient if the items are sorted Very efficient if the items are less and present in the beginning of the list. such as Suppose your list items are : 12,3,4,5,1 and you want to search 12 number then you get beginning in the list. Works well with arrays and not on linked lists. Works with arrays and linked lists.
Number of comparisons are less More number of comparisons are required if the items are present in the later part of the array or its elements are more.

Memory representation of Linked List Data Structures in C Language

Memory representation of Linked List

             In memory the linked list is stored in scattered cells (locations).The memory for each node is allocated dynamically means as and when required. So the Linked List can increase as per the user wish and the size is not fixed, it can vary.

               Suppose first node of linked list is allocated with an address 1008. Its graphical representation looks like the figure shown below:


      Suppose next node is allocated at an address 506, so the list becomes,



  Suppose next node is allocated with an address with an address 10,s the list become,


The other way to represent the linked list is as shown below:




 In the above representation the data stored in the linked list is “INDIA”, the information part of each node contains one character. The external pointer root points to first node’s address 1005. The link part of the node containing information I contains 1007, the address of next node. The last node …