When to use clustered Index and Non-clustered Index?

Question

When to use clustered Index and Non-clustered Index? In which scenarios?

Ashiwani Sharma · Answer

Clustered indexes sorts and stores the data rows in the table or view based on their key values. These are the columns included in the index definition. And the most important thing is we can have only one clustered index per table, because the data rows themselves can be sorted in only one order. Nonclustered indexes are just like a “table of content” of a book which have a separate structure from the actual data rows of the table. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value. For More visit this link :-- http://www.sqlrelease.com/clustered-vs-nonclustered-indexes-when-to-choose-which-one

Vinay Singh · Answer

Hi Have a look of the detail Clustered Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order. The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap. Nonclustered Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value. The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key. You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries. For more information, see Create Indexes with Included Columns . now take the example for more detail generally we use table like below COUNTRY: countryid int primary key countrycode char(2) contrydescription varchar(30) EMPLOYEE: EMPID int primarykey name varchar(50) countryid int As empid and country id is primary key than default cluster index will automatically apply. Now our more transaction will occur in empid and countryid then we will remove cluster index from empid and apply to country id to boost of the performance Similarly if we are using join base on countrycode EMPLOYEE: EMPID int primarykey name varchar(50) countrycode char(2) than we will remove cluster index from empid and countryid both and will keep on countrycode of both table. Conclusion is that in which more column are using for join we need to keep cluster index on that to boost of the performance. Please let me know if require more info.