What is the difference between clustered and Non Clustered indexes in SQL.  Backgound : Indexes provide SQL server (or any other RDMS for that matter) with additional ways to look up and take short cuts to that data's physical location. Adding the right index can cut huge percentages off your query executions. Unfortunately, too many indexes, or poorly planned one, can actually increase the time it takes for your query to run. Types of Indexes SQL server uses two types of indexes: Clustered and Non - Clustered. The rimary difference between the two index types is the way data is stored in the tables -  randomly for non-clustered indexes and sorted for clustered indexes. Note:  SQL Server also contains Primary and Secondary XML Indices, which I will not be discussing in this blog topic.  Clustered Indexes In a clustered index the table is actually stored sorted on the key value(s) in the index. This means that the concept of a heap no longer applies. As the table data can be physically sorted way, only one clustered index can be created per table. Clustered indexes give fast access to values frequently searched by range, or that are accessed in sorted order. Example : CREATE CLUSTERED INDEX index_clus_TbProductList ON ETigers.TbProductList (ProductName) GO A table storing phone book data, an Index on LastName, FirstName would be better candidate for a Clustered index than the phone number column. Non-Clustered Indexes (Default type) Non Clustered Indexes do not reorder table data. They don't require the table to be reordered, so multiple non-clustered indexes can be created per table. You can create up to 249 non-clustered indexes per table. Non Clustered indexes can be created on tables with clustered indexes, and then use the clustered index key to locate rows in the table. As the table data is not stored sorted on the non-clustered key, range scans are inefficient, but equality searches are fast. Example : CREATE  INDEX index_TbProductList ON ETigers.TbProductList (ProductCategoryID) GO if we dont type any type, the default type is NONCLUSTERED If you knew the phone number but needed the matching name or address, creating a non-clustered index on phonenumber would speed the search as this would be an equality search. Drop an Index Drop INDEX ETigers.TbProductList.index_TbProductList         [Schemaname].[Tablename].[indexname] Covered Queries  If you have a combination of the two indexes in place on a table, then queries to a telephone operator could be handeled effciently by the clustered index and the non-clustered index. Uniqueness  An Index can be defined as either unique or non-unique. A unique index ensures that the values contained within the unique index columns will appear only once within the table, including a value of NULL. SQL Server automatically enforces the uniqueness of the columns contained within a unique index. If an attempt is made to insert a value that already exists in the table, an error will be generated and the attempt to insert or modify the data will fail.A nonunique index is perfectly valid. However, as there can be duplicated values, a nonunique index has more overhead than a unique index when retrieving data. SQL Server will need to check if there are multiple entries to return, compared with a unique index where SQL Server knows to stop searching after finding the first row.Unique indexes are commonly implemented to support constraints such as the primary key.Nonunique indexes are commonly implemented to support locating rows using a nonkey column.