Database Table Indexing

Posted under tips on January 03 , 2020 by Swanand Nirgudkar

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.



TAGGED:DatabaseIndexDatabase Explorer


Recent Posts


Truncating Database Transaction Log

November 02, 2019 byNidhisha Shetty

Databases are usually in Full Recovery mode. This means that every transaction is retained in the…

Continue Reading

ActivityRunner Not Starting

October 11, 2019 bySwanand Nirgudkar

One may face issue while applying SheetKraft patch wherein ActivityRunner service refuses to start…

Continue Reading

Reshape instead of Fill-Down condition

May 02, 2019 bySaloni Gude

Originally published on Thu, 2 Mar, 2017 at 6:59 AM Fill is used to generate multiple values from an…

Continue Reading

DataFromExcel Fails in Excel 2010

May 02, 2019 byUpasana

Originally published on Wed, 1 Mar, 2017 at 6:13 AM This problem occurs when you try to Import data…

Continue Reading

Let's talk

Address

7th floor, Unit No. 701, C Wing,
Kailas Business Park, S. Veer Savarkar Marg,
Park Site, Vikhroli (W), Mumbai-400079.

 

Phone

+91 22 62362043

 

For General Queries & Technical Support

contact@quantumphinance.com

 

For Sale and Demos

sales@quantumphinance.com