How can I create different sets of sequential values based on another column in a table?

I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table.

CREATE TABLE Notes (
   Id int IDENTITY(1,1) NOT NULL,
   Type int  NOT NULL,
   CustomerId int NOT NULL,
   -- etc
)

I am migrating data from a legacy system into this table and there is a requirement that Collection and Delivery notes have their own sequential numbers.

I had previously implemented two sequence tables as

CREATE TABLE CollectionNoteSequence (
    Id int IDENTITY(1,1) NOT NULL,
    NoteId int NOT NULL
)

where the Id column is the unique, sequential Id for collection notes, and then the NoteId foreign key's to Notes.Id.

It's getting towards time to do the final (real) data migration and this setup seems hard to work with.

Is there a way I could bin the two sequence tables and add a NoteNo field to the Notes table such that NoteNo would be sequential depending on the Note.Type ? Is this a composite key or something?

The new table might look like

CREATE TABLE Notes (
   Id int IDENTITY(1,1) NOT NULL,
   NoteNo int NOT NULL,
   Type int  NOT NULL,
   CustomerId int NOT NULL,
   -- etc
)

and the data would look like:

Id    NoteNo    Type    CustomerId
1     4000      1       123
2     4001      1       456
3     15123     2       789
4     4002      1       753
5     15124     2       741

I'm using MS SQL Server 2008.

You could use partition by on the note type, which allows a calculation to be reset by groups. For example:

create table OldNotes (
       OldNoteRef           varchar (20) not null
      ,OldNoteType          varchar (20)
      -- Other data goes here
)
go

insert OldNotes (OldNoteRef, OldNoteType) values ('CABC123', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('CABC456', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('CABC789', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('DXYZ001', 'D')
insert OldNotes (OldNoteRef, OldNoteType) values ('DXYZ034', 'D')
insert OldNotes (OldNoteRef, OldNoteType) values ('XZYZ100', 'D')
go

select OldNoteRef
      ,OldNoteType
      ,row_number() over (
          partition by OldNoteType
          order by OldNoteRef
      ) as NewNoteID
  from OldNotes
go

Would produce the following results, which will give you a sequence within the note type.

╔════════════╦═════════════╦═══════════╗
║ OldNoteRef ║ OldNoteType ║ NewNoteID ║
╠════════════╬═════════════╬═══════════╣
║ CABC123    ║ C           ║         1 ║
║ CABC456    ║ C           ║         2 ║
║ CABC789    ║ C           ║         3 ║
║ DXYZ001    ║ D           ║         1 ║
║ DXYZ034    ║ D           ║         2 ║
║ XZYZ100    ║ D           ║         3 ║
╚════════════╩═════════════╩═══════════╝

BTW, table produced by http://www.sensefulsolutions.com/2010/10/format-text-as-table.html

StackExchange Database Administrators Q#15506, answer score: 5