Database Table Indexing

For an automated process any operation involving database is relatively expensive since it involves reading from and writing to disk storage. Database indices provide a way to speed up the location of data without having to search the entire table thereby reducing the cost. Consider this similar to an index in a book where you can quickly navigate to the desired page by looking up the word in the index.

In the context of SheetKraft, indices play an important role for database operations like DataFromDatabase and ExportToDatabase. Proper indexing can help bring down hours of execution time to as little as a couple of minutes.

Indices can be created on a database table by using a rather basic query. It is to note that the database engine never creates indices by itself - they have to be created or dropped manually. To create an index in MS SQL, open Database Explorer and run non-query

CREATE INDEX index_name
ON table_name (column1, column2, ...);

To enforce uniqueness between table rows use the UNIQUE keyword.

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

The most common use case for indexing is for a table containing records corresponding to a large number of dates. The basic approach for such a situation would be creating an index on the date column. For example in a table named TransactionLog containing a column named TransactionDate

CREATE INDEX idx_transactiondate
ON TransactionLog (TransactionDate);

To remove an index simply write

DROP INDEX table_name.index_name;

Note that the indices created above are non-clustered indexes, which is a way of saying that the index is stored differently outside of the parent table. A clustered index on the other hand is stored in the same place as the data and keeps the data rows organized according to its order. Therefore, a table can have only one clustered index.