SQL Server - creating nonclustered Index on specific partitions

Question

One of the biggest benefits of table partitioning is that, "it is possible to rebuild an index on specific partition".

Imagine there is a partitioned table (has 12 partitions for now), which has clustered and a few non-clustered indices, all partition aligned.

I want to add a new nonclustered index to table, which does not have to be built for old partitions. I need this index for only the last 3 partitions.

So, how can I create a new nonclustered index for last 3 partitions of 12 partitioned table?

usr · Accepted Answer · 2012-12-20 21:14:21Z

1

That is not possible. It would create problems for parameterized queries because the query planner would never statically know that the index can be used (except if there was a constant-expression predicate).

You can create a filtered index with `where partitionKey >= startOfSomePartition'. Your queries have to include this predicate statically, though.

You might try a view over two partitioned tables which have different schema. That's not very convenient to develop though.

answered Dec 20, 2012 at 21:14

usr

172k38 gold badges251 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brian Over a year ago

+1 @usr We use the filtered index strategy on big fact table FKs. It helps us to minimize storage and maintenance costs. Since the filter has to be static (can't reference functions like GETDATE()) we usually drop and create once a week, using dynamic sql to build the new index predicate definition. Also, partition elimination is key to getting the index to be used, but that's a given any time you deal with big partitioned tables.

Orlando Colamatteo Over a year ago

One item of note: filtered indexes using >=, <=, >, < work well unless you are using parameters, i.e. they only work if you are using literals in your where-clause and not a variable or parameter.

Collectives™ on Stack Overflow

SQL Server - creating nonclustered Index on specific partitions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related