multiple serial ID numbers in a table

I have a table in SQL Server 2005 which contains some changelog data for a number of projects. These are stored in one table as there are hundreds of projects and the projectlog table can be queried for things like activity per day or per person.

Eg

projectlogidentity int identity

projectname nvarchar(100)

projectchanged datetime

projectchangedby nvarchar(100)

projectserial int

There are indexes on projectname and projectserial

I want to generate a sequential projectserial per project as rows are added

eg:

0 | coffee shop | 7 Jan 2011 08:13 | derek | 0

1 | disco | 7 Jan 2011 08:18 | emma| 0

2 | coffee shop | 7 Jan 2011 08:19 | peter| 1

3 | disco | 7 Jan 2011 09:11 | alan| 1

4 | coffee shop | 7 Jan 2011 09:42 | tess | 2

So when retrieving the rows for a single project by projectname there is a persistent sequential serial number for each row specific to that projectname.

After inserting the row I currently do:

update projectlog set projectserial=1+
(select isnull(max(projec开发者_如何转开发tserial),0) from projectlog 
where projectname='coffee shop') where (projectlogidentity=4);

but I'm worried about performance when this table will contain hundreds of thousands of rows and hundreds of projects. Is there a better way?

thanks

Derek

i think you can use two table for performance.

projectlog 
----------
projectlogidentity int identity
projectnameid int
projectchanged datetime
projectchangedby nvarchar(100)
projectserial int


projectlog_projectname
----------------------
id int
name nvarchar(100)
serial int

UPDATE projectlog SET projectserial = 1 + (SELECT ISNULL(serial, 0) FROM projectlog_projectname WHERE projectname='coffee shop')
WHERE projectlogidentity = 4;
UPDATE projectlog_projectname SET serial += 1 WHERE projectname='coffee shop'

Use a Trigger it is eaxclty what it is created for

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'Incrementprojectserial' AND type = 'TR')
   DROP TRIGGER IncrementProjectserial
GO
CREATE TRIGGER IncrementProjectserial
ON projectlog 
FOR INSERT   
AS 

DECLARE @projectname varchar(10) --whatever you datatype is
DECLARE @projectlogidentity int  
DECLARE @Maxprojectserial int

-- GET YOUR INSERTED VALUES
SELECT @projectname = projectname FROM INSERTED
SELECT @projectlogidentity = projectlogidentity FROM INSERTED

--GET YOUR PREVIOUS projectserial
SELECT @projectserial = isnull(max(projectserial),0) 
FROM projectlog  
WHERE projectname=@projectname) 

--UPDATE YOUR NEW VALUE
UPDATE projectlog SET projectserial = @Maxprojectserial + 1
WHERE projectlogidentity = @projectlogidentity 

GO

here is the a full msdn documentation