Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate Range Right Partition Management with PowerShell Expand / Collapse
Author
Message
Posted Monday, December 21, 2009 7:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 7:35 PM
Points: 401, Visits: 166
Comments posted to this topic are about the item Automate Range Right Partition Management with PowerShell


Post #837159
Posted Tuesday, August 17, 2010 3:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse