Export (0) Print
Expand All
0 out of 5 rated this helpful - Rate this topic

Table and Index Partitioning Transact-SQL Enhancements

SQL Server 2005

Partitioned tables and indexes have their data divided into units which might be spread across more than one filegroup in a database.

Partitioning tables and indexes can provide the following benefits:

  • Large tables or indexes can be more manageable because of quick and efficient access to or management of data subsets, while maintaining the integrity of the overall collection.
  • Querying large tables or indexes may be faster and more efficient on multiple-CPU computers.

Partitioned tables and indexes are partitioned horizontally so that groups of rows are mapped into individual partitions. Operations performed on the data, such as queries, are executed as if the whole table or index is a single entity.

New statements Description

CREATE PARTITION FUNCTION

Creates a function in the current database that maps the rows of a table or index into partitions based on the values of one or more columns. Executing CREATE PARTITION FUNCTION is the first step in creating a partitioned table or index.

ALTER PARTITION FUNCTION

Alters a partition function and any tables and indexes that depend on it. You can use this statement to split a partition of a partitioned table or index into two partitions, or merge the rows of a partitioned table or index into one less partition.

DROP PARTITION FUNCTION

Removes a partition function from the current database.

CREATE PARTITION SCHEME

Creates a scheme in the current database that maps the partitions of a partitioned table or index to filegroups.

ALTER PARTITION SCHEME

Adds a filegroup to a partition scheme, or alters the state of the existing filegroups of a partition scheme. You can use this statement to receive an additional partition of a partitioned table or index.

DROP PARTITION SCHEME

Removes a partition scheme from the current database.

$partition

Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.

Enhanced statements Description

CREATE TABLE

Allows for creating of partitioned tables.

ALTER TABLE

Allows for reassigning a partition to another table.

CREATE INDEX

Allows for creating partitioned indexes.

ALTER INDEX

Allows for rebuilding and reorganizing of a single partition of a partitioned index.

DROP INDEX

Allows for dropping a clustered index and moving the resulting unordered table (heap) to another filegroup or partition scheme.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.