Can you have a partial Identity column in SQL Server?

Question

I have a table with an Identity column which provides my ticketNumber.

I want another table to provide a ticketStepNumber. A ticket may have 0 or more steps, so I won't always be creating a ticketStepNumber.

The ticketStepNumber value is a combination of the ticketNumber column (int) and the stepNumber column (int). I'd like to define the stepNumber column like an identity but starting at 1 for each value of the ticketNumber column, so all I have to do in my stored procedure is pass in a ticketNumber and I'll get the new stepNumber. E.g.

ticketNumber  stepNumber
1             1
2             1
2             2
2             3
4             1
7             1
7             2

Is there a way to define this in the table definition or do I have to do it in the SP?

Answer 1

No, there's no such thing in SQL Server, sorry. An IDENTITY column is a single, ever-increasing column - there's no basing that number on the values of another column.

If you really must have a sequential number for each step, then you'll have to handle the assignments (and make sure it's truly unique) yourself.

UPDATE: if you have some other column in your table that could serve as a sort expression, you could use the ROW_NUMBER function to achieve what you want; e.g. if you had a TicketDate (DATETIME) column, you could do something like:

SELECT 
   TicketNUmber, 
   ROW_NUMBER() OVER (PARTITION BY TicketNumber ORDER BY TicketDate) AS StepNumber
FROM YourTable

This would "partition" your data by TicketNumber (e.g. start counting at 1 for each of the values in the partition) and then use the ROW_NUMBER to sequentially number the rows, based on the TicketDate for ordering.

Marc

Answer 2

This is an easy step to add to your SP:

SELECT (MAX(stepNumber) + 1) AS @StepNumber FROM [TableName] WHERE ticketNumber = @ticketNumber

Naturally you will need to add all of the other declarations, and the some type of concurrency checking if more than one person can operate on the same ticketNumber at the same time.