An Index in SQL may be defined as the secondary database object which is connected to a specific table to enhance the process of retrieval of the associated rows from that particular table. It can be defined for a specific single column or for multiple columns. We can also view an index of SQL as an ordered map between the values in the column for which it is defined and the respective locations of row in the table.

• Read: More Interview Questions

Quick retrieval of data in SQL INDEX!

The most significant benefit of defining indexes is that these indexes help in quick retrieval of data. Let’s understand it with a simple example- Let us consider that you have an encyclopedia and you want to read about Dinosaurs. Instead of reading or going through each page to land upon the page where you can find the information about dinosaurs it is better and quicker to read the index of the book and turn to the particular page number written in front of the title you want to read about. Searching a column of the Index in SQL is more comfortable and hassle free than going through a number of pages. It saves a lot of time to reach a particular page through the Index. Hence, we can say that it is a very efficient way to retrieve the information we want at a particular time.

• SEE: Difference between Primary key & Foreign Key

Now let’s connect it to the SQL database. This same principle of retrieving the data through an index or a number of indexes from the tables of database is applied to get faster results. In the absence of indexes the database system searches through the whole table to position a particular piece of information and this lengthy process of finding some particular data is known as a Table scan, in case of big SQL tables this process can take a long time to fetch the required data. But if there is an index linked to a table, the data base system first reaches out to the index to know the location where the required data is sitting and then it goes to that location or locations to retrieve the desired data. This process is very fast and hence saves on time.

• Related: SQL & .NET Interview questions and Answers

Few tips while building and even using the index!

1. When it comes to saving space then it’s a good idea to build the desired index on the columns of the integer Type as integers require less space for storage purpose.
2. For the faster query run, a narrow index is a good option. Narrow index require less or considerably small space which in turn requires less processing time and hence results in a faster run of a query. So, keep your index narrow for quick results.
3. Cardinality refers to the number of the present distinct values associated with a particular column. If the indexes are covering many or multiple columns then the ordering of these columns is of great value. For the ultimate results it is best to use the column which has the lowest cardinality at first place and column which has the highest cardinal at last place.
4. For optimum results of quick retrieval of data, it is advisable that you declare the column NOT NULL for which the index is being made. This can considerably reduce the size of index resulting in the faster query run.
   5. Though, the sole purpose of building an index means faster data retrieval but it also results in more disk space usage and slowing down of basic processes like INSERT,UPDATE and DELETE.
5. Learn: Aptitude Learning