INDEX ON partition_scheme_name, filegroup_name, and default

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!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *