Usage of AutoIncrement fields in table

samantha73samantha73 Member Posts: 68
Hi All
I'm trying to find the best table structure for a user table that stores Employee by shift date and time. This table will be populated via an API and will be used to create system logic and reporting, and searching by employee and datetime is vital. Having looked at base app tables I noticed that some tables like Change Log Entry use AutoIncrement field as a clustered key . And other tables like G/L entry do not have this for entry no. And then there are tables with a CODE field as primary. What are use cases and advantages of having AutoIncrement? One disadvantage I can notice is the table is automatically sorted by entry number irrespective of clustered keys (employee, datetime).
field(50200; EntryNo; BigInteger)
{
AutoIncrement = true;
Caption = 'Entry No.';
}m4i62p8vf41r.png

Best Answers

  • bbrownbbrown MAMember Posts: 3,213
    Answer ✓
    Auto-Increment is useful where you need to insure a unique value but the values do not need to be sequential. The "Auto-Increment" field does not need to be part of the primary key. Although that is often how it is used.

    There are no bugs - only undocumented features.
  • bbrownbbrown MAMember Posts: 3,213
    Answer ✓
    The use of an Auto-Increment field has no impact on the sort order during reads. You can filter\sort and read the table in any sequence you need with SETCURRENTKEY.

    I don't see your requirements ruling out the use of an "Auto-Increment" field. In fact, I'd find it a better choice than a DateTime field. One question is what's the source of "DateTime"? Is it of such that a current record being inserted would never have a value earlier than an existing record? If so, then doesn't the auto-increment field also provide chronological order?

    There are no bugs - only undocumented features.
  • DenSterDenSter Member Posts: 8,276
    Answer ✓
    SQL Server decides which index to use regardless of SetCurrentKey. SetCurrentKey determines the 'ORDER BY' clause. While this definitely plays a part in the SQL query optimizer, it does not force which index is used.

    We can talk for hours about how performance can be enhanced using the right sort order and efficient filters (and selecting which fields to load), but in general forget about using SetCurrentKey for performance. Use it for the sort order.

Answers

  • bbrownbbrown MAMember Posts: 3,213
    Answer ✓
    Auto-Increment is useful where you need to insure a unique value but the values do not need to be sequential. The "Auto-Increment" field does not need to be part of the primary key. Although that is often how it is used.

    There are no bugs - only undocumented features.
  • samantha73samantha73 Member Posts: 68
    bbrown wrote: »
    Auto-Increment is useful where you need to insure a unique value but the values do not need to be sequential. The "Auto-Increment" field does not need to be part of the primary key. Although that is often how it is used.

    Ok Thanks so can I interpret this as in my table if the write function is overnight process and not required to be performant then maybe not have autoincrement key since read functions will need to sort by employee and datetime so these two fields could be primary clustered keys. I know in database questions there is no simple yes /no answer as there are so many variables :)
  • bbrownbbrown MAMember Posts: 3,213
    Answer ✓
    The use of an Auto-Increment field has no impact on the sort order during reads. You can filter\sort and read the table in any sequence you need with SETCURRENTKEY.

    I don't see your requirements ruling out the use of an "Auto-Increment" field. In fact, I'd find it a better choice than a DateTime field. One question is what's the source of "DateTime"? Is it of such that a current record being inserted would never have a value earlier than an existing record? If so, then doesn't the auto-increment field also provide chronological order?

    There are no bugs - only undocumented features.
  • samantha73samantha73 Member Posts: 68
    Thanks heaps ..Yes Auto-increment looks like a better solution as chronological order is important
    Regarding setcurrentkey - I'd still argue if you insert the table in a certain order you don't need to use Setcurrentkey so more performant - That said I have no clue about performance impact when using setcurrent key before record selection
  • DenSterDenSter Member Posts: 8,276
    Answer ✓
    SQL Server decides which index to use regardless of SetCurrentKey. SetCurrentKey determines the 'ORDER BY' clause. While this definitely plays a part in the SQL query optimizer, it does not force which index is used.

    We can talk for hours about how performance can be enhanced using the right sort order and efficient filters (and selecting which fields to load), but in general forget about using SetCurrentKey for performance. Use it for the sort order.
Sign In or Register to comment.