Hi Gar,

I've just tested my function vs. your function. My function works much faster.

This is my test program:

********************************************************************
*  Description.......: Test program to generate Unique Identifier in the SQL statement
*  Calling Samples...: 
*  Parameter List....: 
*  Created by........: Nadya Nosonovsky 05/09/2000 05:44:54 PM 
*  Modified by.......: 
********************************************************************
local lnSec
lnSec=seconds()
PRIVATE pIDKey
pIDKey=0
set talk on
ssss='ssss'+sys(3)
select UniqueIDGen() as RecNum, * ;
       from PropMstr inner join SiteMstr on PropMstr.PropID=SiteMstr.PropID;
       where SiteMstr.town='CAMB';
       into table &ssss

RELEASE pIDKey
=messagebox( 'Run in '+alltrim(str(seconds()-lnSec,10,5)))

******************************************
Function UniqueIDGen
pIDKey=pIDKey+1

return pIDKey-1

>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.