SQL Server indexes!
Most of us know that indexes will help our application queries to execute faster but few of us know if the database engine will actually take advantage of those indexes or not. So I hope that by the end of this article you will be able to identify when to create indexes and maximize your use of them.
What are SQL Server indexes?
SQL Server uses indexes just like you use the book index to retrieve a subject. Say you need to read about subject X in a book, you will simply go the index and search for the number of pages where X is mentioned. SQL Server does the same thing; it uses the index as a reference to retrieve data from a certain table.
Clustered indexes:
One of the best examples to demonstrate how does a clustered index work is that of a phone book. Each phone book entry represents a row in a table. Our index here should be based on the first and last name, so if you are looking for some one who's last name is "Smith" you will looking for pages having family names beginning with "S". Once you get to the correct page, you will use the first name to get to the correct entry. This process is actually called "Index Seek".
Now let's suppose you want to get all persons having a first name "Mark". This is feasible, but since your phone book is sorted by last name first and then by first name, so you will be forced to go through all entries in the phone book to get the desired result. This is called "Index Scan", an exhausting operation on big tables.
The mechanism of a clustered index is quite simple. The idea is to sort the data in the table according to the data in the index column (using a B-Tree structure) so that SQL Server can fetch the targeted row(s) faster.
A B-Tree is a data structure that supports fast searching with a minimum number of disk reads, and this is what enables our database engine to quickly get the query result. Note that you can have one and only one clustered index on any given table, because the data can be sorted in one order physically.
Non-Clustered indexes:
A non-clustered index contains the indexed column in addition to a pointed to the actual row of data. A search engine is a good example, whenever you search on Google, the result you get is a set of link to existing web pages, so you have to click any of these link to get "more" information. The same applies to SQL Server, if the information you need are included within the index, then you are done, otherwise you have to go to the actual rows of data.
We saw the advantages that indexes offer, but what about the disadvantages?
Disk space
Indexes occupy an amount of disk space when they are created and this amount keeps on increasing as your data grows. This can be a disadvantage if you are limited on disk space.
Data manipulation
Whenever you do a modification your table data, be it INSERT, UPDATE or DELETE, your database engine will have to update all involved indexes, and this is an expensive operation. Thus you should take into consideration how frequent does your data get modified when you are about to create an index on any table.
Conclusion
Indexing is a must to improve performance especially when we are dealing with large databases. However, your indexes should be well chosen because they might exhaust your engine in some cases.