Consider the CREATE INDEX statement below:
CREATE INDEX ix_tableName_columnName ON tableName (columnName) ON { partitionSchemeName (columnName) | fileGroupName | default }
What’s it all about? Let’s take these one at a time, and dig in a little bit.
partitionSchemeName (columnName)
Before you can use this option, you have to have your partition scheme defined. Check out my article on CREATE PARTITION SCHEME. Basically, if you want to use an partition in your index, this is the option you’ll use.
Any column in your base table can be used in the partition…Just be careful when using one for UNIQUE indexes. You’ll have to use only those columns identified as UNIQUE in the index. Otherwise you’ll get an error.
Also, please note, you cannot specify a partitioning scheme on an XML index.
Since this is an advanced topic, I would like to refer you to .
This option is here to allow you to improve performance through data segmentation. This could be physical or logical separation depending on how you set up your partitions.
fileGroupName
If your database uses multiple filegroups, you can identify which one should hold the index. If you only have one defined, this is a useless option.
But, if you do have more than one defined, you can get some speed improvements by separating your indexes from your data. You could identify your filegroups for indexes to exist in a ram drive. That would make your lookups very fast! Or at the very least, you could put them on a faster hard drive with lower seek times.
This option is there to allow you to improve your performance through physical data relocation.
default
This option will create your index in the default filegroup. Nothing fancy about this option. You can save yourself some typing and omit it… unless you are identifying special filegroups for some indexes. In that case, remember you need to be explicit in your declarations, so the next guy will know what you mean by your statements.
I know we’re digging in deep here. Let me know if you have any questions. I’m here to help!