0

RDBMS: SQL Server 2005 Standard Edition

I have a table with 1 billion rows.

What I would like to do is to vertically partition it with a partition function and apply a partition scheme with the function on a table, say BigTable with ID column as a Primary key column name.

Suppose that I have 2 file groups,

  • FG1: partition 1 of BigTable is stored here
  • FG2: partition 2, here.

And FG1 contains ID value between 1 through 5 million. And FG2 contains ID value between 5 million + 1 and so on.

If I were to rebuild index on the BigTable, when the SQL Server rebuild indexes for indexes for ID between 1 through 5 million, would it affect users who access BigTable where ID is greater 5 million + 1?

dance2die
  • 1,961
  • 7
  • 31
  • 40

1 Answers1

1

If you were to rebuild the entire index on BigTable using an OFFLINE (default) rebuild, the entire table would be offline for querying for the duration of the rebuild. You also have the option of rebuilding the entire index using an ONLINE rebuild, which would keep the entire table query-able (both read and write) for the nearly the entire duration (there are 2 relatively short phases at the beginning and end of the operation that are not entirely online) of the rebuild (with some other potential side-effects such as using more disk space, taking longer, etc.). For more information on Online index operations, see how they work and guidelines for performing them.

If you want to rebuild just a single partition of the BigTable, you have very similar options as with the entire table, however the concurrency impact is restricted to the partition you are rebuilding. There are limitations to single-partition rebuilds, such as the fact that you can't perform a single partition rebuild ONLINE (i.e. if you decide to rebuild a single partition, you must do so OFFLINE, which means the data for the given partition will be inaccessible for the duration of the rebuild). For details on options here, see the single-partition options in the ALTER INDEX statement documentation.

As for specifically answering when/how the Sql engine determines what portions of a given index (partitioned or not) should be rebuilt/accessed during a rebuild operation, it really doesn't matter - the options you have in terms of concurrency are:

  • Rebuild the entire index (or all partitions)
  • Rebuild a single partition

If you specify the entire index/all partitions, you'll wind up with the concurrency semantics outlined in the first paragraph above. If you specify a single partition, the concurrency semantics would be as outlined in the second paragraph.

boydc7
  • 531
  • 2
  • 6
  • @chadhoc: I am using a standard edition so ONLINE rebuild cannot be utilized. So I was stuck with an OFFLINE rebuilding. But so long as only the partition on which index is being rebuilt is affected, I can deal with since I am trying to create more than 2 partitions actually. Thanks, chadhoc. – dance2die Nov 23 '09 at 16:58
  • If you are using the standard edition, then you won't have partitioning support either (like ONLINE index operations, it is only supported in the enterprise edition). It seems like you may be trying this on the developer version first, but partitioning certainly will not work / is not supported in the standard edition either. – boydc7 Nov 23 '09 at 17:05