Automate Range Right Partition Management with PowerShell

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Hugh Scott » Automate Range Right Partition Management...

Automate Range Right Partition Management with PowerShell

Rate Topic

Display Mode

Topic Options

Author

Message

Hugh Scott

Posted Monday, December 21, 2009 7:15 AM

Old Hand

Group: General Forum Members
Last Login: Saturday, June 01, 2013 7:58 AM
Points: 398, Visits: 152

Comments posted to this topic are about the item Automate Range Right Partition Management with PowerShell

Post #837159

tfmaloney 52380

Posted Tuesday, August 17, 2010 3:38 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, July 29, 2011 11:01 AM
Points: 14, Visits: 51

In T-SQL, I use this query to get the Partition Schema Number. I'm partitioning on a date column in the partition table.

SELECT
$partition.OrderDetailDatePartitionFunction(o.<date column>)
AS [Partition Number]
FROM dbo.<partition table name> AS o
WHERE o.<table key> = (SELECT MIN(o1.<table Key>)
FROM dbo.<partition table name> AS o1
WHERE o1.<date column> =
(SELECT min(o2.<date column>) FROM dbo.<partition table name> AS o2)) ;

My test partition table looks likwe this:

CREATE TABLE [dbo].[OrderDetail](
[OrderDetailKey] [int] IDENTITY(1,1) NOT NULL,
[OrderDetailDate] [datetime] NOT NULL,
[Quantity] [int] NOT NULL,
CONSTRAINT [OrdersPK] PRIMARY KEY CLUSTERED
(
[OrderDetailDate] ASC,
[OrderDetailKey] ASC
)

and the query looks like this:

SELECT
$partition.OrderDetailDatePartitionFunction(o.OrderDetailDate)
AS [Partition Number]
FROM dbo.OrderDetail AS o
WHERE o.OrderDetailKey = (SELECT MIN(o1.OrderDetailKey)
FROM dbo.OrderDetail AS o1
WHERE o1.OrderDetailDate =
(SELECT min(o2.OrderDetailDate) FROM dbo.OrderDetail AS o2)) ;

Post #970764

« Prev Topic | Next Topic »

Permissions