How to improve performance of SQL Server Queries is a common topic of discussion among many of us. Much has been said, much has been discussed. Few days back, I had an interesting discussion with one of the Junior developers regarding performance improvement of SQL Server Queries. We discussed on how by using a separate hard drive for several database objects can right away improve performance. I suggested him that non clustered index and tempdb can be created on a separate disk to improve performance.
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.
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 (http://blog.sqlauthority.com)