OK, I got something that is basically working.
My table has a little over ten million integers in the iValue column and a zero in the iDel column. For my "unique ID web service", in the Global.ASA at application startup I get the first single value that is not deleted (with iDel=1) and put that in a memory variable; I use the min/max SQL shown earlier.
Now whenever someone hits the nextid.asp page, I can do this in my SQL (which I am wrapping in the ADO GetRows method in my ASP page):
Select iValue from UniqInt where iValue Between (iXXX+1) and (iXXX+100) and iDel=0 Order by iValue Top 10
And the value at array location 0,0 is what I return to the user. Then I can do an update right after that. It looks like this gives Rushmore a very small set of values to find so it's fast.
The reason I cannot use an auto-incrementing columns is because each VFP EXE is actually a web service passing a unique number (wrapped in XML) to any client in a pure web envronment. Each web server is load-balanced, but the functions are compartmentalized. If I used auto-incementing counters on each server, there is a chance that the next ID from one server will be the same as the one on another server. But if I give each web server its own set of numbers to use ahead of time, there is no way that anyone will get the same ID. This is not a classic VFP EXE where multiple users are on a LAN hitting a central file server table, if it was I would use an auto-incementing column.
