In a database, SQL indexes are used to accelerate the query searching process. But using indexes will consume a lot of space and require regular maintenance. The regular maintenance on indexes will ensure the smooth running of database. This article will provide you some guidelines and tips to properly utilize indexes without any hassles.
Index Unique and Primary Key Columns
The tables created using primary key has dedicated unique index which is created automatically by SQL server. However indexing is required if you want maintain the uniqueness in another column which also contains unique data. The query performance of joins and searches can be improved through indexing of unique column.
Index Foreign Key Column
One or more foreign keys in a table may reference to other tables, the unique key present in the column of the referenced table needs to be indexed. The columns present in the foreign key are not unique and have one to many relationship. This one to many relationship will create duplicate values every time a foreign column is related two or more rows in the detail table. Creating unique index in foreign key will enforce one to one relationship. The one to one relationship will prevent the duplicated values. Indexing foreign column will force the SQL server to index the rows needed for scanning both the tables.
Cover Index the Queries Which Return Few Columns
Cover index can be defined as the index which contains all columns in every part of query. The indexed query part will also contain WHERE and FROM clauses. The index of data needed by the SQL server are scanned without reading the associated data pages. Since the reading of associated data pages is not required, this cover index process will improve the performance of database query.
Always Prefer Clustered Index for Large Tables
Clustered index are used to determine the storage order of records in the disk. Let us consider that we have a table named Student. The following query will be used to call the Student table in the database.
SELECT * FROM Students
The above query won’t specify any order criteria and result of the query will be displayed in a clustered order. The query performance will be improved by using a clustered index. But Join query performance is still better than the clustered index performance.
Index Aggregate, Sorting and Grouping Columns
Indexing the columns which has functions like AVG(), SUM(), COUNT(), MIN() and MAX() can improve the query performance. Because indexes used in MIN() and MAX() will arrange the table in ascending order which will enable the server to find the minimum and maximum values very easily. Filter constraints like SELECT in WHERE and HAVING clause will limit the row returned by the query. The data’s which are indexed can be easily sorted using the ORDER BY clause. Similarly GROUP BY clause can be used to group the indexed data.
Indexing the transactional environment may be a balancing act. But query response can be enhanced by creating indexes. Index creation will decrease the performance of keywords like delete, update and index. If transactional requirements are compromised in your query then creating a decision support system is the only way. DSS will offload the transactional requirements in the query which may lead to improved query performance.
Do not Over Index
Indexes in database may improve query performance but they acquire a lot of spaces. Having too much indexes will result in space impact which ultimately lead to reduced query performance. Improper indexing of a database will result in a sluggish database. Indexing process of a database may vary between different databases so it is up to the user to choose the best way to index the database.
Use Indexes with High Selectivity Rate
The general thumb rule in selecting an index is to choose an index which has selectivity more than .33. The indexes with selectivity rate 1.0 are completely unique and are considered to be the best available option.