automated sliding window (times 3)

SQL v2008, horizontally partitioned order message database, basically five tables.  The data is never purged, partitions necessary to ease maintenance, among other things.  Each table has its own function/scheme. All defined the same, RANGE RIGHT on datetime2 key, the partitions are quarterly -- to align with the fiscal business quarters:    
Qtr1  1/1 - 3/31,   Qtr2  4/1 - 6/30,   Qtr3  7/1 - 9/30,   Qtr4 10/1 - 12/31

These are my partitions:    OLD, Qtr4, Qtr3, Qtr2, Qtr1, Current Week
(OLD = anything outside of current business year, the current week is the hottest, I want to isolate to its own partition)

Everything has come together very well, i am near ready, but I'm having a heck of a time automating my sliding windowS.  I've got two, possibly three cases to account for:

1.  At the end of every week, merge the previous week into the current quarter, then split out a
new partition for the new week.  Thus, moving data from the previous week to current quarter
filegroup, and create the new week on the current week filegroup.

2. When the current week is also the current quarter boundary, I need to be sure it's on the correct filegroup, so i don’t have to move data more than once.  The first week of the quarter I'd create the current week partition on the new quarter filegroup, rather than the week filegroup.  The following week would be the similar to other weeks where I split out the new week, but I would then need to skip the previous week MERGE operation so I'm back to 6 partitions.

3. I'm unsure, but I am wondering if for the normal change of quarters, aside from #2, need I also perform a merge/split as I am doing at the end of each week, in item #1?

I've put the procedure below together for my weekly job, but I was just targeting one table.  Now that it's there, I'm trying to figure out how to pass Scheme AND Function in there, such that I could use the two as parms, and use one procedure, to handle all tables.

That's question number 1.  If anyone has time and expertise, I've got some more.....  ;-)

Thank you in advance.
/****** Object:  StoredProcedure [dbo].[usp_MergeSplitWeekly]    Script Date: 08/12/2010 14:30:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[usp_MergeSplitWeekly] 
AS
SET NOCOUNT ON;
/* Managing the RANGE RIGHT partition.
At the end of every week, we merge the previous week into the current quarter, then split out a 
new partition for the new week.  This will move the data from the previous week onto the current quarter 
filegroup and create the new week on the current week filegroup. 
EXEC dbo.usp_MergeSplitWeekly
*/
BEGIN
	DECLARE @week DATETIME2,
		@next DATETIME2

		--get current partition date boundary
		SELECT @week =   CAST(MAX(prv.value) AS datetime2)
		FROM sys.partition_functions AS pf
		JOIN sys.partition_range_values AS prv ON
		 prv.function_id = pf.function_id
		WHERE pf.name = 'tablenamePF';

		--get new partition range for next week
		SET @next = DATEADD(day,7,@week)

		ALTER PARTITION SCHEME tablenamePS
		NEXT USED [Current_Week];
		
		ALTER PARTITION FUNCTION tablenamePF()
		MERGE RANGE (@week);
		
		ALTER PARTITION FUNCTION tablenamePF()
		SPLIT RANGE (@next);	
		
		IF @@ERROR <> 0
		BEGIN
			RAISERROR('Failure to perform the weekly merge/split.',16,-1)
			RETURN;
		END

END

SET NOCOUNT OFF;



GO

Open in new window

LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLAuthor Commented:
Actually, in the post above, the only parm I need is for the function (tablenamePF).  The 'Current_Week' will always be the NEXT USED in the weekly merge/split.

Any advice?
0
dbaSQLAuthor Commented:
I got the weekly thing together.  Probably lacking a bit, but it works.  I'll figure out the rest of it.
/****** Object:  StoredProcedure [dbo].[usp_MergeSplitWeekly]    Script Date: 08/12/2010 14:30:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[usp_MergeSplitWeekly] (
 @pfName VARCHAR(25),
 @psName VARCHAR(25),
 @debug bit = 0
)
AS
SET NOCOUNT ON;
/* Managing the RANGE RIGHT partition.
At the end of every week, we merge the previous week into the current quarter, then split out a 
new partition for the new week.  This will move the data from the previous week onto the current quarter 
filegroup and create the new week on the current week filegroup. 
EXEC dbo.usp_MergeSplitWeekly @pfName = 'tablenamePF',@psName = 'tablenamePS',@debug = 1
*/
BEGIN
      DECLARE @sql1 NVARCHAR(2000),
              @sql2 NVARCHAR(200),
              @sql3 NVARCHAR(4000)

	--get current partition date boundary
	SET @sql1 = N'
	DECLARE @week DATETIME2
	DECLARE @next DATETIME2

	SELECT @week = 
	    CAST(MAX(prv.value) AS datetime2)
            FROM sys.partition_functions AS pf
            JOIN sys.partition_range_values AS prv 
		ON prv.function_id = pf.function_id
            WHERE pf.name = '''+@pfName+''';
            '
            
      --get new partition range for next week
      SET @sql2 = N'
      SET @next = DATEADD(day,7,@week);
      
      '
      
      SET @sql3 = N'
	    ALTER PARTITION SCHEME '+@psName+'
            NEXT USED [Current_Week];
            
            ALTER PARTITION FUNCTION '+@pfName+'()
            MERGE RANGE (@week);
            
            ALTER PARTITION FUNCTION '+@pfName+'()
            SPLIT RANGE (@next);    '

	IF(@debug = 1)
	BEGIN
		PRINT (@sql1 + @sql2 + @sql3)
	END
	ELSE
		EXEC (@sql1 + @sql2 + @sql3)

END

SET NOCOUNT OFF;



GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.