paratition tables in sql 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pelegk1 - 20 Jul 2008 10:13 GMT

how do i delete a paratition and all it's data from a paratitioned table?
if for examplei have a column which is the key for the paratition, and the
data is inserted by this key, for example the column is called :
sdate
=====
20080701235959
20080702235959
20080703235959
and so on....
and i want to delete the date 20080701235959 and all its data - how will i
do this?
thnaks in advance
peleg

Reply to this Message

Dan Guzman - 20 Jul 2008 14:01 GMT

> and i want to delete the date 20080701235959 and all its data - how will i
> do this?

Assuming the indexes are aligned (same partitioning column and
same/equivalent partition function). the most efficient way to remove all
data from a partition is to:

1) create a staging table with the same schema, indexes on the same
filegroup(s) as the partition to be deleted

2) switch the partition to the staging table using ALTER TABLE...SWITCH
PARTITION

3) MERGE the switched partition

4) drop (or truncate) the staging table

The other method is to simply 'DELETE...WHERE sdate = 20080701235959' and
then MERGE the emptied partition. However, this is fully logged and can
take a considerable amount of time if you have a lot of data.

If you need additional help, please post the relevant DDL (CREATE TABLE and
CREATE PARTITION FUNCTION).

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> how do i delete a paratition and all it's data from a paratitioned table?
> if for examplei have a column which is the key for the paratition, and
[quoted text clipped - 10 lines]
> thnaks in advance
> peleg

Reply to this Message

pelegk1 - 23 Jul 2008 11:23 GMT

this is the table stuctre (how do i get a script of the PARATITION FUNCTION?)
(the key of the table on which the paratition should be created is the
startdate)
REATE TABLE [dbo].[TargetTable1](
    [Company] [varchar](50) NOT NULL CONSTRAINT [DF_TargetTable1_Company_1]
DEFAULT (N''),
    [StartDate] [varchar](15) NOT NULL CONSTRAINT [DF_TargetTable1_StartDate_1]
DEFAULT (N''),
    [id] [bigint] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK_TargetTable1] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

> > and i want to delete the date 20080701235959 and all its data - how will i
> > do this?
[quoted text clipped - 34 lines]
> > thnaks in advance
> > peleg

Reply to this Message

Dan Guzman - 23 Jul 2008 13:19 GMT

> this is the table stuctre (how do i get a script of the PARATITION
> FUNCTION?)
[quoted text clipped - 14 lines]
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]

An easy way to script a partition function is from SQL Server Management
Studio. Expand the desired database Partition Functions node in Object
Explorer under Storage-->Partition Functions. Right-click on the function
and select Script Partition Function as...

The script below shows how to partition TargetTable1 on StartDate by
recreating the clustered primary key as a clustered partitioned index.
Since the partitioning column must be part of clustered and unique indexes,
I added StartDate to the primary key.

--create 7 partitions
CREATE PARTITION FUNCTION PF_TargetTable1(varchar(15))
AS RANGE LEFT FOR VALUES
(
'20080630000000',
'20080701000000',
'20080702000000',
'20080703000000',
'20080704000000',
'20080705000000'
)
GO

CREATE PARTITION SCHEME PS_TargetTable1
AS PARTITION PF_TargetTable1
ALL TO ([PRIMARY])
GO

ALTER TABLE dbo.TargetTable1
DROP CONSTRAINT PK_TargetTable1
GO

ALTER TABLE dbo.TargetTable1
ADD CONSTRAINT [PK_TargetTable1] PRIMARY KEY CLUSTERED
(
StartDate,
id
)
ON PS_TargetTable1(StartDate)
GO

Using the above table, the script below shows one method to efficiently
remove all data from a partition using SWITCH. Although not required, this
example uses a partitioned staging table, which ensures the source and
target partition(s) are on the same filegroup(s). I've found that technique
to be handy for more complex partition schemes.

--insert 8 rows of test data
INSERT INTO dbo.TargetTable1(Company, StartDate)
SELECT 'Company 1', '20080630010203'
UNION ALL SELECT 'Company 2', '20080630020304'
UNION ALL SELECT 'Company 3', '20080701030405'
UNION ALL SELECT 'Company 4', '20080701235959'
UNION ALL SELECT 'Company 5', '20080701235959'
UNION ALL SELECT 'Company 6', '20080701235959'
UNION ALL SELECT 'Company 7', '20080702040506'
UNION ALL SELECT 'Company 8', '20080702050607'
GO

--create staging table with same schema and on same file group as source
CREATE TABLE [dbo].[TargetTable1_Staging]
(
[Company] [varchar](50) NOT NULL ,
[StartDate] [varchar](15) NOT NULL,
[id] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_TargetTable1_Staging] PRIMARY KEY CLUSTERED
(
StartDate,
id
) ON PS_TargetTable1(StartDate)
)

--move partition 3 to staging table
--any value within the desired partition may be specified)
ALTER TABLE dbo.TargetTable1
SWITCH PARTITION $PARTITION.PF_TargetTable1('20080701235959')
TO dbo.TargetTable1_Staging PARTITION
$PARTITION.PF_TargetTable1('20080701235959')
GO

--show moved data
SELECT * FROM dbo.TargetTable1
SELECT * FROM dbo.TargetTable1_Staging
GO

--permanently delete data
TRUNCATE dbo.TargetTable1_Staging
GO

--merge partition 3 with partition 2
ALTER PARTITION FUNCTION PF_TargetTable1()
MERGE RANGE('20080701000000')
GO

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> this is the table stuctre (how do i get a script of the PARATITION
> FUNCTION?)
[quoted text clipped - 57 lines]
>> > thnaks in advance
>> > peleg

Reply to this Message