SQL Server Index Fundamentals

by Han C
This article was written by Robert Sheldon about the basics of SQL Server Index, an important component for server performance. According to Sheldon, Indexes can speed up queries by providing easier access to rows in data tables. He explains for example, “If you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data.” In this regard, a table scan can also be used to perform this query but can significantly slow down server performance.

His article goes into detail about how indexes can be used and also about the different styles of indexing, such as Clustered and Non-clustered indexes. A clustered index refers to data rows which are stored at the leaf level and non-clustered means that the leaf nodes contain only the values from the indexed columns and that the row locators will point to the actual data rows. He also offers suggestions in how to plan your indexing strategy. There is a list of six bullet-points on the link provided below.

I felt this article was useful because it elaborated more about the concepts of indexing and choosing the right types to identify your data. Understanding how this works can be a great benefit to future database administrators. I also stumbled across another tutorial with tips for indexing among other things, which might serve as a helpful guide. http://devguru.com/technologies/t-sql/7107.asp

Reference: Sheldon, Robert. 2008. http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/