Post reply

identity column with values based on another column

  • December 1, 2005 at 8:12 am

    #166173

    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?

  • December 1, 2005 at 9:34 am

    #607106

    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 ?

  • December 1, 2005 at 9:54 am

    #607118

    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

  • December 1, 2005 at 10:46 am

    #607135

    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.

     

  • December 1, 2005 at 11:02 am

    #607144

    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