Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: quick table partitioning question, checking for existence of data within part.

    I have a job scheduled to run nightly to purge 1 day's worth of data via the SWITCH statement. What is a cleaner alternative to my code below? I think I want to check to see if there is any data available in that partition prior to attempting the switch. I have some tables with gaps and needed to use try/catch so that the job would not fail:

    Code:
    	begin try		
    		ALTER TABLE Metric
    		SWITCH PARTITION @BasePart
    		TO Metric_Archive PARTITION @ArchivePart;
    	end try
    	begin catch
    		print 'Metric Switch failed'
    	end catch

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    I think I figured it out:

    Code:
    IF EXISTS
    (
    SELECT TOP 1 $PARTITION.pfMetric(Date_ID) AS Partition
    FROM Metric
    WHERE $PARTITION.pfMetric(Date_ID) = @BasePart
    )
    	ALTER TABLE Metric
    	SWITCH PARTITION @BasePart
    	TO Metric_Archive PARTITION @ArchivePart;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •