"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

http://blogs.msdn.com/sqlcat/archive/2006/02/17/Partition-Elimination-in-SQL-Server-2005.aspx


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

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

--STEP2
--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


Thanks,
Sivaram

No comments: