Best Practice for Clutered and Non Clustered Index in table with milions of rows

Question

We have Table in Sql Server 2008 with columns as under

 CLI_ID
 INSURANCEPOliCY_No
 INS_Cmpny
 SUM_ASSURE
 ...........

Here CLI_ID , INSURANCEPOliCY_No are the composite primary key.There are more then 2 crores of rows in the table.Now we are creating a non-clustered index on CLI_ID.Is this going to improve the performance in searching or tell me any way to make it more faster.Any help is appreciated.

Answer 1

As your primary key is composite key of CLI_ID and INSURANCEPOliCY_No, So, Cluster Index will be created on these both, In order to improve performance you should create another non-clusters indexes as follows:

1) CLI_Id

2) INSURANCEPOliCY_No