No sooner had I given my suggestion than I received a question – What will happen if we can create clustered index on a separate drive from the table on which it is built.
My answer is : No! It is not possible at all.
Let us first be clear about the difference between a clustered and a non clustered index.
- Only 1 allowed per table
- Physically rearranges data in the table to conform to the index constraints
- For use on columns that are frequently searched for ranges of data
- For use on columns with low selectivity
- Up to 249 (for SQL Server 2005) and 999 (for SQL Server 2008) allowed per table
- Creates a separate list of key values with pointers to the location of the data in the data pages
- For use on columns that are searched for single values
- For use on columns with high selectivity
A table devoid of primary key index is called heap, and here data is not arranged in a particular order, which gives rise to issues that adversely affect performance. Data must be stored in some kind of order. If we put clustered index on it then the order will be forced by that index and the data will be stored in that particular order.
Reference : Pinal Dave (https://blog.sqlauthority.com)