Indexing in SQL is a simple topic when it comes to discussion with an SQL Expert, but over the past 10 years as a developer, I have seen people with good knowledge and experience fail to portray the primary meaning of indexes and their types.
Indexes help in retrieving data faster from a table when there is large amount of data in that table and it is taking longer time to retrieve data from it.
Typically indexes are applied on columns that are frequently a part of the where condition for a particular query. May it be a view or a stored procedure.
Indexes and keys are 2 completely different entities altogether, though they might be related directly or indirectly in some way.
There are 2 types of indexes that can be applied on a table viz. Clustered and Non-Clustered indexes. Both these indexes work exactly the same way when it comes to retrieving of data though there is just one minor difference in them. When a clustered index is applied to a column of the table, the column is physically sorted whereas when a non-clustered index is applied to a column in a table then that column is logically sorted
There can be a maximum of 16 columns in one index either clustered or non-clustered and since clustered indexed columns are physically sorted then we can only have one clustered index on a table. the total count of non-clustered indexes in a table can go up to 999.
Post comments if this helps.
