Usage of AutoIncrement fields in table

samantha73

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.';
}

bbrown

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.

bbrown

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?

DenSter

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.

bbrown

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.

samantha73

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

bbrown

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?

samantha73

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

DenSter

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.