identity column with values based on another column
-
I am trying to make an identity column with awareness of vlaues in another column. the table contains 2 columns clientID (int) and jobID(int) where jobID has scope of that client only. The identity column would be jobID and the PK would be clientID,JobID
clientID JobID
1 1 (first job for client 1)
1 2 (second job for client 1)
2 1 (first job for client 2)
the only way i can do this presently is either progrmatically, using a cursor or joining onto a query like "select max(isnull(jobID,0))+1 as JobID, ClientID from tbl group by ClientID". Is there a way of minimising the overhead and defining an identity value over 2 columns in ms sql 2005 or 2000?
-
Correct design depends on your requirements.
Why do you *need* the JobID to represent the exact sequence of jobs for that client ?
Is it for presentation purposes only, eg sorting/displaying on the front end ? If so, see the new ranking functions in Sql2005 T-SQL enhancements.
What is supposed to happen to all the JobID's for a client if 1 gets deleted in the middle of the sequence ? Do the others have to get "shuffled up" to fill the gap ?
-
I as well as PW believe this is more a presentation issue than anything else. you can make the column a normal Identity and the numbering can be handled in the client side or ( again like PW
) if you enjoy 2k5 have a look at rownumber() OVER... Cheers,
* Noel -
thanks both of you. the jobID is used for presentation as you guessed but more importnalty for comparing job n (or series starting at job n) with another job (or series starting at) n- @x for a given client. The example in online BOL (i dont have 2005 just yet) http://msdn2.microsoft.com/en-us/library/ms176102.aspx doesnt seem to work as an inline function or on insert (unless i use an instead of trigger).
I was hoping to use the jobID (which could also be thought of as a recency ranking) in a self join like this. tbl1.clientID = tbl2.clientID and tbl1.JobID = tbl2.JobID - @x
The table is ~10 million and grows ~200k a month. There is also quite a lot of code that does aggregations and reports on the differences identified which i would prefer to leave. I could use the rank() function in a view but i was hoping to use a column containing JobID which is indexed. I hope that clarifies the question enough.
-
if you really want to control that behaviour you need to know what is going to happen when a delete comes along which you haven't replied yet, also what about historical ( or previous) reports i.e. if the number is shuffled today's job2 could have been yesterday's job 3
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply