In some cases, even though a column (or columns of a composite index) has a non-clustered index, the Query Optimizer may not use it (even though it should), instead performing a table scan (if the table is a heap) or a clustered index scan (if there is a clustered index). This, of course, can produce unwanted performance problems.
This particular problem can occur when there is a data correlation between the order of the rows in the table, and the order of the non-clustered index entries. This can occur when there is correlation between the clustered index and the non-clustered index. For example, the clustered index may be created on a date column, and the non-clustered index might be created on an invoice number column. If this is the case, then there is a correlation (or direct relationship) between the increasing dates and the increasing invoice numbers found in each row.
The reason this problem occurs is because the Query Optimizer assumes there is no correlation, and it makes its optimization decisions based on this assumption.
If you run into this problem, there are three potential resolutions to this problem:
* If possible, reorder the non-clustered index column (assuming a composite index) so that the column with the highest cardinality is the first column in the composite index.
* Create covering indexes.
* Add index hints to your queries to overrule the Query Optimizer.
Things borrower must know about Pre Closing Home loan - Most people tend to take a home loan for 15 to 20 years as this usually offers the lowest home loan interest rates. Now to prepay the loan, you have to pa...