Indexes are special data structures associated with tables or views that help speed up the query. SQL Server provides two types of indexes: clustered index and non-clustered index.
In this article, you will learn the basics about indexes.
Imagine you have an sql table having 100–1000 rows of data. Querying the data would be very fast as there are less number of rows. What if we have a million rows. Now what? Would it take the same time to query the data? If not what can we do to reduce the retrieval time?
These are some of the questions that are very important when using a database in general. SQL Server provides a dedicated structure to speed up retrieval of rows from a table called an index.
The questions arises, what does an index do which reduces the retrieval time.
To simply put it, it creates a dictionary of the index-key along with their reference. Whenever the user queries the database which matches the index, it looks up the dictionary and finds the reference to the row(s) and then returns them.
There are mainly 2 different types of indexes which you can use.
- Non-clustered Index
- Clustered index
A clustered index stores data rows in a sorted structure based on its key values. Each table has only one clustered index because data rows can be only sorted in one order. The table that has a clustered index is called a clustered table. A clustered index organizes data using a B-tree which enables searches, inserts, updates, and deletes in logarithmic time. When you create a table with a primary key, SQL Server automatically creates a corresponding clustered index based on columns included in the primary key. Clustered indexes store pointers to blocks not data.
A non-clustered index is similar to the index page of a book. The index page of a book consists of a chapter name and page number, if you want to read any topic or chapter then you can directly go to that page by using index of that book. No need to go through each and every page of a book. Similarly the data is stored in one place, and index is stored in another place. Since, the data and non-clustered index is stored separately, you can have multiple non-clustered index in a table. Non-clustered indexes store both value and a pointer to actual row that holds data.
Note :-
- Indexes sound fun and easy, but they can cause a lot of havoc and unexpected behaviours in your system. You need to carefully decide the index to optimize your reads and not have an impact on your writes.
Cheers!!
