Table and Index Partitioning - Microsoft SQL Server 2012 Performance Tuning - page 320

--Creating clustered index
--on tbl_MyStagingData
CREATE CLUSTERED INDEX idx_tbl_MyStagingData_RecordDateTime
ON tbl_MyStagingData(RecordDateTime,RecordID)
GO

7. Now, switch partition 2 of tbl_MyData to staging table tbl_MyStagingData

and truncate the staging table. After truncating the table, examine the number of
partitions and total rows in partitions. For this, execute the following script:
USE Sample_DB
GO

--Switching Partition 2 of tbl_MyData
--to tbl_MyStagingData and truncating table
ALTER TABLE tbl_MyData
SWITCH PARTITION 2 TO tbl_MyStagingData PARTITION 1
GO
TRUNCATE TABLE tbl_MyStagingData
GO

--Examining Partitions and row count
SELECT
partition_number
,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('tbl_MyData')
ORDER BY partition_number

8. The preceding query should return the following partition details:

9. Now, merge partition 2 with partition 1, set the next used filegroup for partition
scheme ps_Quaterly_RangeRight to PRIMARY and examine the number of
partitions, using the following script:
USE Sample_DB
GO

--Merging Partition 2 to Partitin 1
ALTER PARTITION FUNCTION pf_Quaterly_RangeRight()