Although you can create indexes using the SQL CREATE INDEX command the ANSI syntax does not allow index expressions or conditional statements. If you need to create an index with an expression or use conditional statements you will need to use the system procedure sp_CreateIndex. The system procedure also allows you to specify the filename for the index, the CREATE INDEX statement places the index in the structural index file.
The procedure parameters include: Table Name, Index FileName, Tag Name, Expression, Condition, Options and Page Size. In version 9.x an updated version of the procedure (sp_CreateIndex90) includes a parameter for the collation. An example usage of these functions are below:
// Create an index concatenating two fields
EXECUTE PROCEDURE sp_CreateIndex( 'Customer', 'Cust.adi',
'NAME', 'LastName;FirstName', '', 2, 512);
// A collation can be specified for 9.x
EXECUTE PROCEDURE sp_CreateIndex90( 'Customer','Cust.adi',
'NAME', 'LastName;FirstName', '', 2, 512, '' );
The options value is specified as an integer. You set this option by adding together the values for the various options. For instance a compound candidate index would have an option value of 2050 (2 + 2048). The valid options and their values are listed below.
- ADS_DEFAULT = 0
- ADS_UNIQUE = 1
- ADS_COMPOUND = 2
- ADS_CUSTOM = 4
- ADS_DESCENDING = 8
- ADS_CANDIDATE = 2048
UPDATED 31 MAR 2009 – The original opening paragraph is below:
Although you can create indexes using the SQL CREATE INDEX command the ANSI syntax only accounts for single field indexes. If you need to create a multi-segmented index or use conditional statements you will need to use the system procedure sp_CreateIndex.
3 comments:
You state:
"Although you can create indexes using the SQL CREATE INDEX command the ANSI syntax only accounts for single field indexes."
However, I don't believe this is quite accurate..... Quoted directly from the help page is this example:
Create a two-column ascending index:
CREATE INDEX empndx ON emp ( emp_addr, emp_name );
You are correct that a multi-column index can be created using the CREATE INDEX command. I had overlooked the example you quoted in the help file.
The sp_CreateIndex system procedure allows for index expressions (i.e. Left(lastname, 5), CTOD(HireDate) ). Along with the other options described in the tip. I have revised the tip to reflect these changes.
Thanks for the comment.
Excellent tip. This method allows you to bypass the SQL compound index limit of 15 fields (error 2157 - Too many key columns for index), and explains why you can do it in ARC using table properties but not through SQL.
Post a Comment