Is it possible to that primary column value independent in each schema partitioning

My table structure is below:

CREATE TABLE [ACC].[Document](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL,
    [SalesCompanyFinancialPeriodID] [int] NOT NULL,
    [DocumentTypeID] [int] NULL,
 CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED 
(
    [DocumentID] ASC
)

I want to partitioning my table on SalesCompanyFinancialPeriodID column. is it possible to DocumentID column value reset on each partition. in other word DocumentID in each partition wat independent by DocumentID in other partition.

Answers


Depending on your exact criteria you could create a view if the reference numbers are only needed on the fly:

CREATE VIEW Acc.PartitionedDocument
AS
    SELECT  DocumentID,
            Date,
            SalesCompanyFinancialPeriod,
            DocumentTypeID,
            ROW_NUMBER() OVER(PARTITION BY SalesCompanyFinancialPeriod ORDER BY DocumentID) [PartitionDocumentID]
    FROM    Acc.Document

However, this will change if documents are deleted. Otherwise your only alternative as far as I am aware with SQL-Server 2008 is to maintain a sequence either with a trigger or within your application layer. Below is how I would achieve this with a trigger.

-- ADD ADDITIONAL COLUMN
ALTER TABLE Acc.Document ADD ParitionDocumentID INT NULL
GO
-- UPDATE EXISTING ROWS
;WITH Doc AS
(   SELECT  *, ROW_NUMBER() OVER(PARTITION BY SalesCompanyFinancialPeriod ORDER BY DocumentID) [RowNumber]
    FROM    Acc.Document
)
UPDATE  Doc
SET     PartitionDocumentID = RowNumber

GO
-- ADD CONSTRAINT TO ENSURE NO DUPLICATES
ALTER TABLE Acc.Document ADD CONSTRAINT UQ_Acc_Document UNIQUE NONCLUSTERED (PartitionDocumentID, SalesCompanyFinancialPeriodID)
GO
-- ADD TRIGGER TO MAINTAIN NEW COLUMN
CREATE TRIGGER acc.InsertDocument
ON Acc.Document
FOR INSERT 
AS
    IF EXISTS(SELECT 1 FROM inserted WHERE PartitionDocumentID IS NULL)
        BEGIN

            UPDATE  Acc.Document
            SET     PartitionDocumentID = COALESCE(NextDocumentID, 0) + RowNumber
            FROM    Acc.Document d
                    INNER JOIN 
                    (   SELECT  DocumentID, ROW_NUMBER() OVER(PARTITION BY SalesCompanyFinancialPeriodID ORDER BY DocumentID) [RowNumber]
                        FROM    inserted 
                        WHERE   PartitionDocumentID IS NULL
                    ) i
                        ON i.DocumentID = d.DocumentID
                    LEFT JOIN
                    (   SELECT  SalesCompanyFinancialPeriodID, MAX(PartitionDocumentID)  [NextDocumentID]
                        FROM    Acc.Document
                        GROUP BY SalesCompanyFinancialPeriodID
                    ) NextID
                        ON NextID.SalesCompanyFinancialPeriodID = d.SalesCompanyFinancialPeriodID
        END

Need Your Help

WebLogic and OSGi

deployment weblogic osgi bnd

Is there a way to run an OSGi container in the Weblogic Application Server? I know that it works with JBoss, Glassfish and there are possibilities to add an Equinox servlet bridge to your OSGi proj...