>I am trying to decide whether to use sys(2015) to generate a unique primary key value for a table in one of my applications. In this particular case there would be less overhead, and it would be quicker and more simple than getting a 'next number' from a table that stores the next key value.
>
>In Visual Accountmate, a VFP accounting package I work with, in some cases they use sys(2015) to generate unique numbers in some of their line item tables. I haven't ever run into any problems with reliability with this, or heard of anyone else running into a problem. I've looked for dupes in Accountmate tables with hundreds of thousands of rows, and never encountered any.
>
>I've seen threads on here that talk about the reliability of using sys(2015) for temp file names - on fast computers etc. But I haven't seen any threads that address the issue of using it as a primary key.
>
>When I proposed that we use it for a new application, one of the other developers I work with questioned me on whether I could guarantee that it would be unique, every time it was called, and what algorithm it used to guarantee uniqueness. I couldn't give him an answer. I think that it is based in part on the date and time. Beyond that I don't know.
>
>Does anyone know the algorithm used to generate the sys(2015) string?
>
>TIA
David,
Search for threads with "sys(2015) primary key". I can assure you such threads exist in the last 2 years :)
sys(2015) is reliable as long as it's on single box. In multiuser environments it's duplicable (and believe me easily can be duplicated).
You could make it reliable if you preceed it with something like a stationID.
FWIW IMHO GUID is a better alternative. It's guaranteed to be globally unique (GloballyUniqueIDentifier) provided generated on a box with a network card. If generated on a standalone box than it's reliable for that box + still highly unique when taken to a networked environment.
sys(2015) uses datetime (on single box unique even if generated within the same millisecond).
GUID uses datetime too but also uses NIC address to generate a 128bits integer (with win2K and later it doesn't include NIC address to prevent backtrace to box where it was created - but still it's guaranteed to be globally unique).
When a GUID is converted to human readable and widely used standard format it occupies 38 bytes ( {XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX} ). I'd use it as is only stripping out the curly braces (and maybe -).
function UniqueID
Local pGUID,rGUID,lcFail
lcFail = ""
Declare Integer UuidCreate In 'RPCRT4.dll' String @pguid
Declare Integer CoCreateGuid In 'Ole32.dll' ;
string @pguid
Declare Integer StringFromGUID2 In 'Ole32.dll' ;
string rguid, String @lpsz, Integer cchMax
pGUID=Replicate(Chr(0),16)
rGUID=Replicate(Chr(0),80)
If Inlist(UuidCreate(@pGUID),0,1824)
Return Iif(StringFromGUID2(pGUID,@rGUID,40) # 0, ;
Strconv(Left(rGUID,76),6), lcFail)
Endif
Return lcFail
endfunc If you want to make it smaller it's a 16bytes integer. You might directly use pGUID generated with UuidCreate and store in a 16bytes char field.
Cetin
