Hello everybody,
how can I verify a partition scheme in SQL Server 2005 (June edition)?
SELECT p.*FROMsys.partitions p,sys.objects o
WHERE
o.name='mytable'AND
o.object_id= p.object_idGO
this always returns only one partition
thank you
Eckard
This query will list all the tables in AdventureWorks (change to yours) and their partitions:
USE
GO
SELECT
QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX([object_id], 'SchemaId') AS int))) + N'.' + QUOTENAME(OBJECT_NAME([object_id])) AS [ObjectName] , *-- SELECT *
FROM
[sys].[partitions]WHERE
OBJECTPROPERTYEX([object_id], 'IsMSShipped') = 0ORDER
BY [ObjectName]I'm not sure these are the "official" definitions, but here is how I think of them:
1) A partition is a horizontal slice of a table's data. Every table has >=1 partition. Each partition contains a separate B-Tree. Partitions are numbered starting at 1.
Catalog view is: sys.partitions
2) A partition function is a pseudo-schema scoped, arity-1, integer-valued function. Pseudo-schema means is is not contained in a named schema like other kinds of functions. The $PARTITION construct can be looked at as pseudo schema. Arity-1 means it takes 1 parameter. The 1-based integer it returns identifies a partition.
Catalog view is: sys.partition_functions
3) A partition scheme is a binding between a partition function and 1 or more physical filegroups. A partition function embedded in a partition scheme maps inputs to filegroups.
Catalog views are:
sys.partition_schemes
sys.filegroups
sys.destination_data_spaces -- binds partition scheme to filegroups
Note: a partition function can be used in more than 1 partition scheme
4) A data space is an abstraction of filegroups and partition schemes. A data space is entity onto which you can map your table's data. The reason this is useful is because a table or index can be partitioned or not. If partitioned, we need to link it to a partition scheme. If not partitioned, we need to link it to a filegroup. But we only use a single foreign key, e.g.
sys.indexes.data_space_id
so a partitioned index will reference a "partition_scheme" kind of data space and a non-partitioned index will reference a "filegroup" kind of data space.
The catalog views are:
sys.data_spaces -- UNION of all partition schemes and filegroups
sys.filegroups -- "derived from" sys.data_spaces, only shows filegroups
sys.partition_scheme -- "derived from" sys.data_spaces, for partition schemes
Hope that helps.