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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy