How Can I increment a numeric field depending on a particular client ?

[snr]

Hi,
I want a field similar to auto number . I want to set different starting number for different client. So whenever I add a record to table , the number appropriate to the client should increment. I don't want to do that manually , i.e I don't want to increment that field by adding 1 , each time I add record , and set that field and client as primary key. Can I use "Formula" column instead ? If yes , HOW ????????

Please Help.

Thanks

 

[clapag22]

I don't believe you could do it if the field is part of the PK because PK's cannot be nullable. If you could make it a null field, then you could use an insert trigger and have a maintenance table with your clients and the next id # that's available and update your table with that and then increment the value for that client in your maintenance table by 1. It's a bit clunky I admit.

Plus, doing it this way you may come to a point where the id's begin to overlap...

 

[bperry]

SQL Server does have Computed Columns (what you are calling Formula Column). However, a Computer Column refers to other columns within the table, and I'm pretty sure that it cannot be a subquery. Since it sounds like you would need a subquery to dig out the next available number for that client (either with a MAX function or with a lookup to a reference table), then I'm guessing a Computed Column is no good for what you have in mind.

If you are doing all your Inserts with a stored procedure (which is widely recognized to be a good idea), then the SP could easily dig out a client's next available number as a @local variable, and then the Insert could use that variable to provide a value for your column.

Hope this helps a bit. Probably others can provide some more advice.
brian perry