Hi Gar,
I was thinking about this approach:
PRIVATE pIDKey
pIDKey=0
select UniqueIDGenerator(), * from ...
RELEASE pIDKey
Function UniqueIDGenerator
pIDKey=pIDKey+1
return pIDKey
I haven't tested it yet. BTW, I noticed, that your function always return 48495 as 5 first char in unique ID (I just run it alone in a loop).
Regarding Key generation - yes, it's a pretty standard technique. We have a table NextID, which holds last ID for each table in DBCs.
>One more alternative. I'm not familiar with Craig Berstons KB, but it is probably a variation on a pretty standard method of key generation, incrementing a number stored in a separate table. Calling the this same function in your SQL statement might another way of generating the numbers you need.
>
>>Hi again,
>>
>>Ok, I'll try and compare the speed difference.
>>
>>For unique ID generator in our DBCs, we use the same approach, as in Craig Bernston KB.
>>
>>Thanks for your help.
>>
>>>>Hi again Gar,
>>>>
>>>>As far as I recall from VFP Help, UDFs slow down SQL perfomance and work unpredictable. I'm afraid to put this function into long query (which produces ~ 1mln. records)
>>>
>>>Scalar funcitons, which this is are not unpredictable. This should give very solid results. Whether it would slow you down -- again, scalar functions don't neccesarily slow down the results. Give this function a try. I guarantee it will do what you need, and the only way to tell whether it will slow performance is to give it a try.
>>>>
>>>>Not sure, that I understand about shared tables. In from clause, of course, I use shared tables. The resulting table isn't shared and it's name is generated unique name.
>>>
>>>That is what I mean -- it is an unshared result table. Basically what I am saying is you would not want to use this function as the default value of a key in a table in your shared database -- because the results might not be unique. But in the context of an SQL call the results will be unique and what you are looking for.
>>>>
>>>>Thanks for your help. We'll see, what we can do here.
>>>
>>>
>>>Hope th is really does help.
If it's not broken, fix it until it is.
My Blog