December 21, 2009 at 7:15 am
Comments posted to this topic are about the item Automate Range Right Partition Management with PowerShell
August 17, 2010 at 3:38 pm
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)) ;
May 18, 2016 at 6:56 am
Thanks for the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply