Use Vertical Index Tables

The most common form of index tables is the vertical index tables. Horizontal index tables are used for more than just finding the index key. A few of the applications include finding the value of an indexed column in a table, finding the value of a foreign key, finding the column index to which a value refers, and finding the address of a column that is not indexed.

horizontal index tables

In an organization where all the data on a particular table is indexed and access-controlled, it makes sense to separate the different data fields into the horizontal index tables. By creating a table in a SQL database to hold this kind of information, it can help to keep track of the different variations in the data that has been stored. It can also allow the personnel to easily locate the data and also provides the data access controller (DAC) with a very easy way to update the data that is contained in the stored procedure. The common practice in many organizations is to create a table to contain the indexed fields. These tables are referred to as a column index and are helpful in maintaining these kinds of modifications.

It is necessary to note that not all fields are automatically indexed. Depending on the type of field that is indexed, a customer field may not be indexed and, therefore, not able to be modified. The information stored in the field must be changed if the field is indexed because of the capabilities of the database.

A given stored procedure should always consider the indexed field when the stored procedure is running. This is because it is possible that changes may be made that will affect the indexed fields. Although there is not much an organization can do if the indexed field is not indexed, there are some options that a user can utilize that may make the entire operation a little easier.

First, the code should always refer to the column as a name that is associated with the table. Whenever the stored procedure is run, the column needs to be changed to the name that the data is stored as in the database. When this is done, the reference to the column can then be changed back to the actual column. Even though the code could be going to refer to the index as a table name, the code could not use a table name, but an index name. There are two exceptions: when the stored procedure will refer to the index as a table name and when the function will be referencing indexes within a table.

Some DBA’s will assign a table as an index and store the information in a table. The index contains a foreign key that allows two tables to share data. The tables do not need to be exactly the same as the one that the stored procedure will reference.

If the code needs to retrieve the data that is located within the indexed column, there are several ways to accomplish this. The data can be retrieved by the index number that is stored within the table. For example, if the stored procedure refers to the column named CustomerID, then the code can refer to the column as the number of the CustomerID that is stored in the table. If there is a foreign key between CustomerID and EmployeeID that allows the information stored in the CustomerID column to be accessed, then the code can refer to the number as the reference for the foreign key. If the function does not refer to the index as a table name, then the code can use the index name as the reference to the column.

An organization can create a table in a database to contain the indexed column for the stored procedure to refer to. When the stored procedure is executed, the index will be used as a table name that will allow the organization to modify the data that is stored within the indexed column.