Trying to avoid a stored procedure, is this possible?

SQL Apprentice Question
I have a single table that has two columns pkID & Notes. For each pkID there
can be several rows, what I would like to accomplish and know if possible.
Is to create a column, that I can number each row for a given pkID
sequentially.

pkID Notes NoteNumber
1 1
2 1
2 2
2 3
3 1
3 2


etc....... for each pkID in the table.


Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

My guess is that you meant something like this, with better names,
constraints, a keyu, etc -- the basic stuff:


CREATE TABLE DocumentNotes
(document_nbr INTEGER NOT NULL,
note_nbr INTEGER NOT NULL
CHECK ( note_nbr > 0),
PRIMARY KEY (document_nbr, note_nbr),
note_txt VARCHAR(250) NOT NULL);



>> know if possible Is to create a column, that I can number each row for a given pkID


sequentially. <<

You can enforce that constraint with:
1) a TRIGGER in T-SQL
2) a CHECK() constraint in SQL-92 and up
3) a VIEW in SQL-89 and up
4) an OLAP function in SQL-99 and up


What behavior do you want? Should the notes re-number when there is a
gao? Etc.