partition table code with delete

[Pramod Jamadagni]

hi,

 

------------------------------------- creation of partition table -------------------------------------

declare @MP int;

select @MP = max(Period_id) from [IRI_PPMS_Store_Week_PPG]

CREATE PARTITION FUNCTION iri_store_week_part_func (int)

AS RANGE LEFT

FOR VALUES (@MP - 40, @MP - 30, @MP - 20, @MP - 10)

 

CREATE PARTITION SCHEME iri_store_week_partscheme

AS PARTITION iri_store_week_part_func

TO (fg1, fg2, fg3, fg4, fg5)

 

CREATE TABLE [dbo].[IRI_PPMS_Store_Week_PPG_BKP](

      [Store_ID] [int] NOT NULL,

      [Period_ID] [int] NOT NULL,

      [Product_ID] [int] NOT NULL,

      [Base_Price] [real] NULL,

      [Actual_Price] [real] NULL,

      [Base_Volume_Sales] [real] NULL,

      [Base_Unit_Sales] [real] NULL,

      [Base_Dollar_Sales] [real] NULL,

      [ACV] [real] NULL,

      [Actual_Volume_Sales] [real] NULL,

      [Actual_Unit_Sales] [real] NULL,

      [Actual_Dollar_Sales] [real] NULL,

      [Consumption] [real] NULL,

      [Promoted_Sales] [real] NULL,

      [Incr_Due_To_Promotion] [real] NULL,

      [ACV_FO] [real] NULL,

      [ACV_DO] [real] NULL,

      [ACV_TPR] [real] NULL DEFAULT ((0))

) on iri_store_week_partscheme(Period_ID);

 

------------------------------------- query partition table -------------------------------------

 

select ps.Name PartitionScheme, pf.name PartitionFunction

from sys.indexes i

join sys.partition_schemes ps on ps.data_space_id = i.data_space_id

join sys.partition_functions pf on pf.function_id = ps.function_id

where i.object_id = object_id('IRI_PPMS_Store_Week_PPG_BKP')

 

------------------------------------- drop partition table -------------------------------------

 

drop table IRI_PPMS_Store_Week_PPG_BKP

 

drop partition scheme iri_store_week_partscheme

drop partition function iri_store_week_part_func

 

DBCC SHRINKFILE (N'PTA1' , 0, TRUNCATEONLY)

DBCC SHRINKFILE (N'PTA2' , 0, TRUNCATEONLY)

DBCC SHRINKFILE (N'PTA3' , 0, TRUNCATEONLY)

DBCC SHRINKFILE (N'PTA4' , 0, TRUNCATEONLY)

DBCC SHRINKFILE (N'PTA5' , 0, TRUNCATEONLY)

 

---

NOTICE TO RECIPIENT: THIS E-MAIL (INCLUDING ANY ATTACHMENTS) IS MEANT FOR ONLY THE INTENDED RECIPIENT OF THE TRANSMISSION, MAY CONTAIN CONFIDENTIAL INFORMATION, AND IS PROTECTED BY LAW. IF YOU RECEIVED THIS E-MAIL IN ERROR, PLEASE IMMEDIATELY NOTIFY THE SENDER OF THE ERROR BY RETURN E-MAIL, DELETE THIS COMMUNICATION AND SHRED ANY ATTACHMENTS. UNAUTHORIZED REVIEW, USE, DISSEMINATION, DISTRIBUTION, COPYING OR TAKING OF ANY ACTION BASED ON THIS COMMUNICATION IS STRICTLY PROHIBITED.
"This email and any files transmitted with it contain confidential, proprietary,
privileged information of Symphony Services Corp (India) Pvt. Ltd. and are intended
solely for the use of the recipient/s to whom it is addressed. Any unauthorized
notifying, copying or distributing of this e-mail, directly or indirectly, and the
contents therein in full or part is prohibited by any entity who is not a recipient.
Any email received inadvertently or by mistake should be deleted by the entity who
is not a recipient thereof. You may be pleased to notify the sender immediately by
email and the email should be deleted from your system".