SQL SERVER – Clustered Index on Separate Drive From Table Location

SQL SERVER - Clustered Index on Separate Drive From Table Location hard-discs-stack-with-a-magnifier-tool 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.

Solarwinds

Let us first be clear about the difference between a clustered and a non clustered index.

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

Non-Clustered Index

  • 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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Query Optimizer Hint ROBUST PLAN – Question to You
Next Post
SQL Server – Understanding Table Hints with Examples

Related Posts

11 Comments. Leave new

  • santosh chandavaram
    June 18, 2009 7:50 am

    any classical examples of columns (typical) for each type?

    Reply
  • Just to clarify – it is possible to create a clustered index on a different filegroup (or drive) than the original one. It just moves the table to the new filegroup, without moving its corresponding indexes.

    For example, say that you create a table with a clustered index and five nonclustered indexes all in the primary filegroup. If you drop that clustered index and create a new clustered index on a new filegroup, the table will move over to it – but the five nonclustered indexes won’t.

    Reply
  • Hi Brent,

    Very correct. In fact I think what you said really goes well with this post.

    I will create example of the same with due credit to you and will post in new blog post.

    Kind Regards,
    Pinal

    Reply
  • can i have different .LDF and .MDF files for one database like for diffrent transections we can hae diffrent ldf and mdf file

    Reply
  • Hi Pinal,

    Please leave the msg regarding the examples of clustered index n non clustered index n what is the meaning of primary file group n all

    Thanks,
    Narenndra

    Reply
  • Atin Srivastava
    August 5, 2009 2:57 pm

    Hi Amit,

    Yes we can have multiple .mdf or .ldf files (we can have even different extensions)
    Just Right Click on DB goto Properties and click on Files Options… from here you can add different log and data files

    You can also restrict the size of each chunk you created
    Hope u got it

    Reply
  • After creating index’s i was able to reduce time of a query from one whole night to 3 minutes

    Many people talk about index’s but dont tell others how simple it is to create them

    1) Just right click a table

    2) Select Indexces/keys

    3) Click add

    4) use the dropdown hence to create index on a column you are using in the query

    5) add index to all the columns used in query by clicking
    add button

    Reply
  • samba shiva reddy
    February 28, 2010 9:52 pm

    hi pinal,
    The post is nice but if u include b-tree structure in that post it will be very nice.
    Regards,
    Samba Shiva Reddy.M

    Reply
  • Hello Pinal,

    I’m SQL junks.

    How do I decide or select the columns that I need to indexing?

    Is that any best practice?

    Please help and advice.

    Thanks,
    edwin

    Reply
  • excellent article…..thank you Pinal

    Reply
  • :-(

    I didn’t understand Clustering. Can you give me a url of basic guide to clustering?

    Reply

Leave a Reply

Menu