Thanks Dragan.
I had considered all this (especially a few years ago when a client wanted to export a database for one company in the overall database - of companies and their staff - so they could update it on-site and re-import to the master dbc. I had nightmares with the newids from the child being out of synch with those in the parent). Can't change to char as the key is used in SO MANY parts of the system, composite keys, searches, etc. - I'd need to undo all the STR()s I've done, etc., and the zones wouldn't be in numerical order, etc. - ... shudder!
I was more interested in how your newid() coped with the "holes" between system-generated IDs and those higher up, imported, like mine. No doubt I'll figure it out but ... reinventing the wheel and all that! Guess the least injurious would be to go to Integer - come to think of it, hardly a problem at all ('cept where I'd have to validate 4 num on input as int. would take anything)
Cheers
Terry
>>>Another reason for this (apart from the imports) may be that the table and the id.dbf may get out of sync. Had that before and it was a pain in down there to fix, so I'd rather lose some speed than lose sleep.
>>
>>Roger that. Thanks for the ideas but could you explain what happpens to the gap between the original highest value (1100) and the new range of imported IDs (e.g. 9013-9045). I could set the new ID.dbf value to the imported 9045 but would lose out on the values 1101-9012. Given that some dumb-cluck left me historically with a Zone ID of n(4), it doesn't leave much room to maneouvre!
>
>Just tell them they have about 950 zones left before the system breaks :).
>
>Once you hit the wall of 9999, you may want to look for unused numbers, but then you can never be sure whether some data from previous years may sneak in and spoil it for you. I.e. you may find that 1402 was not used, and later you discover that it was used two years ago, temporarily vanished, and resurfaced later... said the devil's advocate.
>
>Or you may convert the n(4) to c(4) and treat it as hex. That'd give you 26214 (0xffff-0x9999) more numbers before you have to radically change the table structure. Or you could just change from n(4) to integer - compatible enough with old data, still accepts the same data, only needs more width on exports.
- Whoever said that women are the weaker sex never tried to wrest the bedclothes off one in the middle of the night
- Worry is the interest you pay, in advance, for a loan that you may never need to take out.