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
  • 2,051

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