Index Partitioning

Is it possible to partition an index without a table partition in SQL Server 2005/2008?

if yes, how and what are the advantage and disadvantage?

more ▼

asked Mar 18, 2010 at 07:20 AM in Default

kardile gravatar image

301 14 14 14

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Yes, you can partition indexes independently of tables. Partitioning indexes seperately from tables can be of benefit when the table isn't partitioned or the index doesn't include any of the columns involved in the table partition. There are costs associated. Read this article at MSDN for details. Presumably the best approach for doing this is when you have an index that is covering and the table is either not partitioned or partitioned with a different schema. If the amount of data is such that partitioning the index will benefit data access, you do have to wonder if the table wouldn't also benefit.

more ▼

answered Mar 18, 2010 at 09:22 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

I agree with Grant. I don't think there would be tons of examples of not partitioning them together. The purpose of the index is to get rapid access to data from the table. Chances are pretty good that they should be aligned for optimum performance.
Mar 18, 2010 at 10:56 AM Blackhawk-17
thanks Grant,should we create partition function to access the partitioned indexes? i get confused..
Mar 18, 2010 at 02:09 PM venkatreddy
The partition function defines the storage, not the access. You don't have to do anything special to access partitioned tables or indexes, just design them so that the queries that need them will function correctly.
Mar 18, 2010 at 02:50 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Thanks Grant.

Do you recommend to have table partition instaed of index partition in case of loading daily large data(ETL)?

Also, is it recommended to have clustered index on partition key?

more ▼

answered Mar 18, 2010 at 12:04 PM

kardile gravatar image

301 14 14 14

Like Blackhawk says, the most common method is to align indexes with tables. Yes, that will be off a clustered index, the table. The best recommendation I can make is to follow the link and read the documentation. There are lots of details, so you don't want to rely on simple blog posts.
Mar 18, 2010 at 12:18 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 18, 2010 at 07:20 AM

Seen: 1612 times

Last Updated: Mar 18, 2010 at 07:20 AM