What is the use of CLUSTERED AND NON CLUSTERED INDEX on same column

I am new to sql and I am wondering if somebody can tell me the use of CLUSTERED AND NON CLUSTERED INDEX on same column. I was looking over some table scripts at my work but didn't understand what is the use of having both clustered and non clustered indexes on one column.its an ID column and it is in a开发者_Python百科ll the tables in database.

A clustered index is not really an index - in the sense that it's all the data organized in the tree according to the key.

A non-clustered index is just the key in the tree with any additional included columns and the necessary bookmark to get to the data row when necessary. The data itself may be stored in a clustered index or a heap. And there can be many non-clustered indexes, but obviously only one real way to choose to store the data.

For performance of a particular query, a covering non-clustered index on a heap (or whatever, it doesn't matter, since the index is covering) can often easily outperform a clustered index, since the index may fit more rows per page and requires no bookmark lookups to get data out of the row, while a clustered index seek/scan is going to read and discard a lot more data and fit less rows per page.

Typically you will want a clustered index and the clustering key should be narrow, static, increasing, unique.

But for query performance you really want to look at non-clustered indexes and have a selection which are covering and which have sort orders in the right direction where applicable.

In really simple terms the difference between a CLUSTERED and NON CLUSTERED INDEX is that the CLUSTERED index is a physical ordering of the records in the table, based on the values in the indexed field, whereas a NON CLUSTERED index is a logical ordering of the records in the table based on values in the indexed field(s). This logical ordering is decided by the SQL database engine based on the statistical distribution of values in the table.

Another important difference is that for a table you can have only one CLUSTERED index (because it's a physical ordering of data stored in that table). While you can have many different NON CLUSTERED indexes on different fields, on the same table.

With that being said, of course you can have both CLUSTERED and NON CLUSTERED indexes on the same field of a table and that's useful because generally CLUSTERED indexes are specifically good at accessing data sequentially while NON CLUSTERED indexes can be optimized for random access to the data by the SQL database engine. This way a combination of CLUSTERED and NON CLUSTERED indexes on the same column gives you highly optimized access to records in that table when searching based on values in the indexed field.