table partition

-Creating
CREATE PARTITION FUNCTION IdPartition ( int ) AS RANGE RIGHT
FOR VALUES ( 10000 , 20000 , 30000, 40000, 50000 )

CREATE PARTITION SCHEME IdSchema AS PARTITION IdPartition
TO ( Filegroup1,Filegroup2,Filegroup3,Filegroup4,Filegroup5,Filegroup6)

Altering
ALTER PARTITION SCHEME IdSchema NEXT USED Filegroup2

ALTER PARTITION FUNCTION IdPartition () SPLIT RANGE ( 100000 )

retrieving partition status of databases

SELECT $partition.IdPartition(DCEProjects.ID) AS [Partition Number],max(DCEProjects.ID)
FROM dbo.DCEProjects
--WHERE $partition.IdPartition(DCEProjects.ID) = 2
GROUP BY $partition.IdPartition(DCEProjects.ID)
ORDER BY [Partition Number]