amenjonathan (12/16/2010)
Chris Hamam (12/15/2010)
Hugh Scott (12/14/2010)
amenjonathan (12/14/2010)
Anybody have any idea what happens when you do not add the partitioned column to an index? Does it still create an aligned index (by secretly adding it anyway)?Nope. The index gets created on the default file group. Not that I would know from experience!:-D
Regards,
Hugh
From vague memory and recollections of BoL, the partitioned column is added (as an included column) to a non-clustered index only if there is no clustered index... although please don't quote me on this!
I think this is impossible actually. You must have a clustered index on the partitioned column in order to create a partitioned table. But in the same thread, every non-clustered index contains the clustered index as a reference, unless I'm remembering incorrectly. A non-clustered index on a heap table...not sure what the heck is going on in that scenario. haha!
FYI, you can partition a table that is stored as a heap (i.e. no clustered index). Partitioning a table in place which contains existing data, however, is much easier done by creating a partitioned clustered index. Rebuilding the clustered index on a new partition scheme (or no partition scheme) with DROP_EXISTING allows the partition scheme to be changed in only the time it takes to build a clustered index on the table.
The partition column must be part of the clustered index definition. It is optional for non-clustered indexes that use a partition scheme, but is added automatically as an included column, I believe.
Best,
--Mark