X$Index

---

The X$Index system table is associated with the file INDEX.DDF. X$Index contains information about all the indexes defined on the tables in the database. The structure of X$Index is as follows:

 
Table C-6 X$Index System Table Structure

Column Name	Type	Size	Case Insensitive	Description
Xi$File	USMALLINT	2	N/A	Unique ID of the table to which the index belongs. It corresponds to Xf$Id in X$File.
Xi$Field	USMALLINT	2	N/A	Unique ID of the index column. It corresponds to Xe$Id in X$Field.
Xi$Number	USMALLINT	2	N/A	Index number (range 0-119).
Xi$Part	USMALLINT	2	N/A	Segment number (range 0-119).
Xi$Flags	USMALLINT	2	N/A	Index attribute flags.

The Xi$File column corresponds to the Xf$Id column in the X$File system table. The Xi$Field column corresponds to the Xe$Id column in the X$Field system table. Thus, an index segment entry is linked to a file and to a field.

The Xi$Flags column contains integer values that define the index attributes. The following table describes how Pervasive.SQL interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.

 
Table C-7 X$Index System Table Index Definitions

Bit Position	Decimal Equivalent	Description
0	1	Index allows duplicates.
1	2	Index is modifiable.
2	4	Indicates an alternate collating sequence.
3	8	Null values are not indexed (refers to Btrieve NULLs, not SQL true NULLS).
4	16	Another segment is concatenated to this one in the index.
5	32	Index is case-insensitive.
6	64	Index is collated in descending order.
7	128	Index is a named index.
8	256	Index is a Btrieve extended key type.
13	8192	Index is a foreign key.
14	16384	Index is a primary key.

The value in the Xi$Flags column for a particular index is the sum of the decimal values that correspond to that index's attributes. Three indexes are defined for the X$Index table, as follows:

 
Table C-8 X$Index System Table Index Definitions

Index Number	Segment Number	Column Name	Duplicates	Case Insensitive	Segmented
0	0	Xi$File	Yes	N/A	No
1	0	Xi$Field	Yes	N/A	No
2	0	Xi$File	No	N/A	Yes
2	1	Xi$Number	No	N/A	Yes
2	2	Xi$Part	No	N/A	No

Index Number corresponds to the value stored in the Xi$Number column in the X$Index system table. Segment Number corresponds to the value stored in the Xi$Part column in the X$Index system table.

To see the information about the index segments defined for the Billing table, for example, issue the following query:

SELECT Xe$Name,Xe$Offset, "X$Index".* 
FROM X$File,X$Index,X$Field 
WHERE Xf$Id=Xi$File and Xi$Field=Xe$Id and Xf$Name = 'Billing' 

ORDER BY Xi$Number,Xi$Part