x

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

avatar image

kardile
301 14 14 16

(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

avatar image

Grant Fritchey ♦♦
137k 20 42 81

(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

avatar image

kardile
301 14 14 16

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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x60

asked: Mar 18, 2010 at 07:20 AM

Seen: 1791 times

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

Copyright 2016 Redgate Software. Privacy Policy