Wednesday, April 23, 2008

Faster GUID Keys

In the Advantage 7.x days, there was no built in mechanism to generate a GUID. Many users ended up writing INSTEAD OF INSERT triggers as DLL's or .NET assemblies in order to automatically add a GUID key to a record.

While that method works well, and was the only option back then, there is a lot of extra cost for a relatively simple task. The server has to populate the __new and __old records, call into the trigger (if it is an assembly that call is marshalled, adding more overhead), cleanup, etc. Plus you now have to maintain and distribute external DLL or assembly files.

Advantage 8.1 introduced a new scalar function and expression engine function called NEWIDSTRING. This function can be used to generate a GUID using a variety of formatting options. Because it is not only an SQL scalar function, but also an expression engine function, it can be used as a default field value as shown in the following image.

In this example I am using the "file" encoding, which results in a smaller key value (22 bytes). Note this is a case sensitive character field as opposed to a cicharacter field. As mentioned in the help file, this particular type of encoding (Base64) is case sensitive. A variety of encoding options are available and described in the help file.

Not only is the default field value much faster, but it also provides the default field value immediately upon record insertion, as opposed to waiting until the new record is posted. To see this behavior in action, configure a default field value for a table, then open that table in ARC and add a new record. Before you even post you will see the GUID value populated in the pending record buffer.