Creating Database Indexes

Indexes are used to speed-up query processes in a database. They are similar to an index for a book. If you need to go to a particular chapter you can go to the index, find the page number of the chapter and go directly to the page. Database index works the same way. If you create indexes, the database goes to that index first and then retrieves the corresponding table record directly.

You can create multiple indexes for a table, however, you can only one clustered index for a table and it is usually the primary key column for that table. You can create additional non-clustered indexes on a table use to speed up queries. If you have a query that looks for data in a non-primary key column, then creating an index on that column can drastically speed up query lookup for data in the column. The trade off is that the additional non-clustered indexes take up additional space and require constant maintenance of re-indexing.