Creation of aligned partitioned indexes

Hi All,

In the manual I find the following comment for creating indexes.

"If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table."

We are creating some dimensional models in SQL Server with about 100M rows in the largest fact tables.

What I have created are base tables which were on 'primary' and then created a clustered index over a partitioning scheme. I did this as I figured the data will go into the clustered index in any case.

However, when I then create indexes on these tables they do not look like they are petitioned....at least not as far as I can tell.

Q1. Is there some way to tell if the index was partitioned properly?

Q2. If the index is not partitioned is there any simply way to alter the table to the partitioning scheme? My reading of the manual tells me I have to unload, truncate, alter and then reload the table. Is there a better way?

Thanks in advance for your assistance.

Best Regards

Peter

Hi All,

since I did the work to figure this out I thought I would share.....

If you go into the sql studio and look at the properties of the index you can see if it is partitions and if the partitioning column is there.

There is no way I have found to alter the table to the partitioning scheme. However, if you create the index and make the partitioning column the first element of the index and use the partitioning scheme for the table it will have exactly the same effect as partitioning the table and then providing no on clause in the create index.

So you have a choice of specifiying the on clause in the index create or letting sql server default and do this for you......I guess it is a personal opinion as to whether one wants explicit control or default control over creating indexes.....also, even if the table is partitioned if you use an on clause in the index you can partition the index and place it onto the file groups you want to....

Oh the joy of setting up partitioned indexes etc.. :-)

Peter

|||

And now I have one more question on this topic....

In oracle it is possible to partition indexes and have them be 'local' in that they are only related to the partition to which they point.

The reason for doing this is when loading the data the local bit mapped indexes are dropped and once the days transactions/summaries are loaded the local bit mapped indexes can be put back...so you are only every rebuilding the indexes for the data that changed and it is only one partition that you are pointing to for that bit map rebuild...this is is really handy because of the cost of index rebuilds. So you can easily put a few hundred million rows into a partition with little difficulty when doing it this way...

I wondered if SQL Sever had the abiity yet to remove just the portion of a partitioned index pointing to the current partition to have rows inserted/updated and then allowed that index to be rebuilt or updated after updates table place...What I want to avoid is the dropping and recreation of all the query indexes on the table because that would rebuild the index for all partitions and not just the one...

Can this be done? Can anyone point me to the relevant documentation on how to do this?

Thanks

Peter

|||

Hi All,

as we move forward with testing we had one other question we thought someone here might have seen before we test it out ourselves.

We have created aligned partitioning indexes for each table with each index containing the partitioning column and one dimension table key.

We have placed these indexes onto the same partitioning scheme as the table and therefore the same file groups and the table inside the clustered index.

We suspect that performance will be improved if we place the indexes onto their own file groups....but we wonder 'by how much' and 'is it worth it'.

Has anyone else out there performed testing on partitioned tables and aligned indexes both on the same file groups and on different file groups and have an idea of the difference in performance?

Thanks

Peter