Partition

Check information on partitions.

--view partition funcitons
select * from sys.partition_functions

--view how the partition has happened

select * from sys.partition_range_values

--view the partition schema

select * from sys.partition_schemes

--view data spaces used by partition schema

select * from sys.destination_data_spaces

--check which partition the given value range in partition column is in

$PARTITION

$PARTITION.function_name(value)

An example to check how rows in table are distributed to partitions

SELECT $partition.pf(PT.column used for partition) --function name
                  AS [Partition Number]
      , min(PT.column used for partition) AS [MinID]
      , max(PT.column used for partition) AS [MaxID]
      , count(*) AS [Rows In Partition]
FROM dbo.partitionedTable AS PT (NOLOCK)
GROUP BY $partition.pf(PT.column used for partition) --partition number
ORDER BY [Partition Number]

--Verify rows inserted in partitions

select * from sys.partitions

where object_name(object_id)='partitionTable'

--switch a certain partition to a table
alter table partitiontable switch partition partitionnumber to destinationtable

(
--a technique to move table to a different file group: move the content while dropping clustered pk
alter table tablename drop clusteredPK with (move to filegroup)

)