About Next Key definitions

Next Keys functionality allows long-running transactions to get a next value for a column entry, with or without a prefix, and without blocking other processes that are doing the same thing. These kinds of stored procedure calls are set up using the Application Schema Columns Metadata form, in the Generator(s) field.

How Next Key definitions work

The root stored procedure for Next Key calls looks like this:

EXEC @Severity = dbo.Next2SubKeySp 
   @TableName      = @TableName
 , @ColumnName   = @ColumnName
 , @Prefix                   = @Prefix
 , @KeyLength         = @KeyLength
 , @SubKey              = @SubKey
 , @Increment          = @Increment
 , @KeyVal               = @KeyVal OUTPUT
 , @Infobar                = @Infobar OUTPUT 

For example: Application code might have a Customer Order Number column for example that might or might not use a prefix. So, a customer order number with a prefix of TG might be TG00000109, and a customer order number without any prefix might be 0000000547.

If two processes, then, call the Next2SubKeySp code for the CustomerOrder table and CoNum column at the same time, requesting the next key for prefix TG, they would get the next two values from the NextKeys table (TG00000110, and TG00000111). There would be no blocking and both values would be stored in the NextKeys table.

Synchronizing Next Key values requires the system to go back to the base tables to figure out what the current high values are for each prefix. This is where the Next Key definitions come into play. These definitions contain the metadata that indicate what tables and columns have Next Key associations.

About Primary and Secondary specifications

In some cases, you might have an archival table that includes the same Next Key associations as another table. For example, you might have a table, named CustomerOrderHistory, that holds historical data that has been archived from the CustomerOrder table. The CustomerOrder table, then, would be considered the "primary" table. The CustomerOrderHistory table would be considered a "secondary" table that also needs to be checked when synchronizing Next Keys, since either table could contain the current maximum key values.

About Sub Keys

Sub Key columns allow a Next Key to be grouped based on the value of another column.

For example, an InteractionID might be stored in a table called DailyInteraction, based on the DayOfInteraction sub key column. The desire in this case is to have InteractionID start at 1 and increment with each additional entry, but only for a given DayOfInteraction value. To view the current maximum value for a given day, then, you would change the Sub Key value in the Next Key request.

Next Key synchronization in this case would look at the DailyInteraction table and figure out the current maximum InteractionID for each DayOfInteraction value. In this example, there would be two sets of values: 07/04/1776, 3 and 07/05/1776, 2. These values would be inserted into the NextKeys table so that Next Key processing could proceed correctly for that table and column.

About the Master Table and Master Column

The Master Table and Master Column options are used only for a specialized integer-only case of sub key processing. Next Keys are generally character-based, but in certain cases, they are integer-based with a Sub Key column defined in a separate table.

For example, suppose that a Journal table has a BatchID column and a Sequence column, and the Sequence column is integer-based. In this case, a separate table containing the list of BatchID values, which are sub key values, must be defined. Non-Master sub key data is always character-based and Master sub key data is always integer-based.