Sql-server – Calculate number for each row

sql serversql-server-2008sql-server-2008-r2

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,
    [Number] [int] NULL,
    [Title] [nvarchar](200) NULL,
    [ConfirmStatusEnumID] [int] NULL,
    [SignedPrice] [money] NOT NULL,
 CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED 
(
    [DocumentID] ASC
)

I want to set Number column value automaticaly increment number started from 1 for each SalesCompanyFinancialPeriodID column. In other word if new record inserted in this table, value of Number column calculated in instead of trigger, such as below:

 ISNULL( (Select Max(A.Number) 
          From Acc.Document A
          Where A.SalesCompanyFinancialPeriodID = Inserted.SalesCompanyFinancialPeriodID),1)

What is the best practices for this problem.

Best Answer

Why do you need to store this value in the Number column? Much better to derive this information at query time than to bother storing redundant information and constantly have to update it as rows are added, changed or deleted.

SELECT DocumentID, SalesCompanyFinancialPeriodID, 
  Number = ROW_NUMBER() OVER (PARTITION BY SalesCompanyFinancialPeriodID
    ORDER BY DocumentID)
FROM ACC.Document;
Related Question