Automate Range Right Partition Management with PowerShell

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

  • 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)) ;

  • 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