"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

June 30, 2009

Partition Elimination in SQL Server 2005

Partition function and Partition Scheme Step by Step Example


Tried the same for Table test1. Deleting data from a Partition
--STEP 1

select $partition.PF1(a) [Partition Number], a, b from test1

--To Delete this data from a Partition in the Table, Create another table and switch the partition
create table test1_truncate (a int, b int)

DECLARE @PartitionNumber INT
SET @PartitionNumber = 1
ALTER TABLE dbo.test1 SWITCH PARTITION @PartitionNumber TO test1_truncate;

TRUNCATE TABLE test1_truncate

--STEP 3
select $partition.PF1(a) [Partition Number], a, b from test1

Partitioning Tips
Handling Large SQL Server Tables with Data Partitioning
Dynamically controlling the number of rows affected by a SQL Server query


No comments: