I have a situation where a decimal figure isn't truly a decimal, it is a sub-count of sorts. For example, when an prescription for medication is filled it is given an Rx number (lets say 345673). That number will stay with the prescription throughout any refills and the refills append a .1, .2 etc. So, over the life of that Rx number you could end up with 345673, 345673.1, 345673.2... ongoing. The problem is when you hit .10, .20, .30 etc. In decimal form those records are the same as .1, .2, .3.
Is there any way to track these numbers and support the trailing zeros without having use VARCHAR etc? This is the primary key column and I'm not crazy about using varchar on a Pk (is that old fashioned?)
Any and all suggestions/help is appreciated.
Edit to add: I should have explained why we can't use separate columns for this. This data originates elsewhere and is merged into our database from a bulk import operation. The merge is perpetual since much of the data is altered at the origin and combined on the next bulk import. The Rx number has to match exactly to perform the bulk import / merge.
I would recommend using a composite primary key. Add a second column, perhaps called refill, and use that for your incremental values. That way both columns would be integers and no need to use varchar for your primary key.
You may need to use a trigger to maintain the value as identity fields don't work with groupings.