revamicro

 Profile Answers by revamicro 

• Sep 4th, 2010

 

Clustered index is created on actual data.
Non clustered index ix created on pointer of data.

If there is primary key by default clustered index is created. else non clustered index created.

Only one clustered index is possible to create.
249 non clustered indexex can be created.

psingla

 Profile Answers by psingla Questions by psingla

• Nov 8th, 2010

 

The index in the sql server are in the form of btree having root and leaf nodes.
The root node contains the column key value contained in the index and the leaf node contains the table data or pointer to the table data
Clustered
   sorts the table data based on key columns.
   As only one physical ordering is possible so only one clustered index in allowed
   leaf node contains the actual data
   more pages are used to store the c indexes
 Non Clustered
   do not sorts the table data based on its key column
   so can have more than non clustered index on a table 249
   leaf node contains the pointer to the data pages
   if all the predicates that are used in query are there in the index key columns and selected column either in key column or included column then only non clustered index is used
if above is not the case then the all the values that are there in nc index will be fetched from nc index ,,for rest of the column values clustred index or rid lookup is used                
less pages are used to store the nc index

itmasterw

 Profile Answers by itmasterw 

• Dec 28th, 2010

 

In general you put a clustered index on the key columns that you will be querying on and you can put a non clustered index on other key columns in the select statement. But you need to really look at what will really give you the best performance here.

What you can do to find this is set and execution plan and run the quuery without any index and see what the costs is in the plan are. then run it with a clustered index, and finally run it with both indexes set and see what will give you the lost cost and that will be the best for you. However, you also have to take into account whenever the table is updated a lot of the time because you loose performance in the maintance of these indexes. So, if you are heavily updating this table you might want cut back on the nubmer of columns you index.