In SQL Server database there are mainly two types of indexes, Clustered index and Non Clustered index, and difference between Clustered and Non Clustered index is very important from SQL performance perspective. It is also one of the most common SQL Interview question, similar to difference between truncate and delete, primary key or unique key or correlated vs non correlated subquery. For those, who are not aware of benefits of Index or why we use index in database, they help in making your SELECT query faster. A query with index is sometime 100 times faster than a query without index, of course depending upon how big your table is, but, you must index on columns which are frequently used in WHERE clause of SELECT query, or which forms major criterion for searching in database. For example in Employee database, EmployeeId or EmployeeName are common conditions to find an Employee in database. As I said, there can be either clustered index or non clustered index in database, former is used to decide how data is physically stored in disk and that's why there can be only one clustered index in any table. In this article, we will explore more about both of this indexes and learn some key difference between clustered and non clustered index from interview and performance perspective.
