March 6, 2014 at 1:14 pm
Hi
The question is in subject itself:
In Partitioning,Is it mandatory for partitioned column to be part of clustered Index?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
March 6, 2014 at 1:18 pm
If you partition a table: YES.
If you partition just an index: not necessary.
March 6, 2014 at 1:23 pm
I was partitioning a table. It has PK as clustered index but the column I want to partition on is in a non clustered index. Table has around 1 billion records.Seems I am looking at a tough and long job on production server.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
March 6, 2014 at 1:57 pm
If you don't have any FK referencing your PK, you can add this column to PK and partition it.
However, if you have FK from other tables referencing your PK, you will need to recreate existing PK as non-clustered, and make your index by which you are going to partition table - clustered.
March 6, 2014 at 1:59 pm
Thanks for your prompt replies. I'll have the impacts of these options.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
March 26, 2014 at 8:25 am
This doesn't seem to be true. You can partition on a nonclustered column even when u already have data and a clustered index on other column.
Is there any issue with this?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
March 26, 2014 at 9:04 am
The various ways to specify the partition with various types of index are detailed in this article - http://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx
March 26, 2014 at 9:12 am
S_Kumar_S (3/26/2014)
This doesn't seem to be true. You can partition on a nonclustered column even when u already have data and a clustered index on other column.
But that's not partitioning the table, that's just partitioning a nonclustered index. To partition the table itself, you need to rebuild the clustered index onto the partition scheme
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply