Table and Index Partitioning Transact-SQL Enhancements
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
| New statements | Description |
|---|---|
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. |
|
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. |
|
Removes a partition function from the current database. |
|
Creates a scheme in the current database that maps the partitions of a partitioned table or index to filegroups. |
|
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. |
|
Removes a partition scheme from the current database. |
|
Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function. |
Enhanced Statements
| Enhanced statements | Description |
|---|---|
Allows for creating of partitioned tables. |
|
Allows for reassigning a partition to another table. |
|
Allows for creating partitioned indexes. |
|
Allows for rebuilding and reorganizing of a single partition of a partitioned index. |
|
Allows for dropping a clustered index and moving the resulting unordered table (heap) to another filegroup or partition scheme. |
See Also
Reference
Data Definition Language (DDL) Statements (Transact-SQL)
Other Resources
What's New and Enhanced in Transact-SQL (Transact-SQL)