Indexes, indexes, indexes…

How table indexes are looking in the SQL layer for F&O?


– data structure that improves the speed of data retrieval
– are used to quickly locate data without having to search every row in a database table every time a database table is accessed
– is the way to get an unordered table into an order that will maximize the query’s efficiency while searching

Unique index
 – is the index that helps maintain data integrity by ensuring that no two rows of data in a table have identical key values

Clustered index
– is an index that defines the physical order in which table records are stored in a database
– by default a clustered index is created on a primary key column
– since there can be only one way in which records are physically stored in a database table, there can be only one clustered index per table

Primary index
– is an index on a set of fields that includes the unique primary key for the field and is guaranteed not to contain duplicates
– primary key is not necessarily clustered index (although probably in 95% of scenarios it is), while Clustered index is not necessarily is primary key

Surrogate key
– a surrogate key is any column or set of columns that can be declared as the primary key
– a surrogate is a candidate key
– the most common type of surrogate key is an incrementing integer, such as RecId

Alternate key
– a table can have several alternate keys
– any one alternate key can switch to being the primary key
– in practice each alternate key relies on a unique index for its implementation and enforcement

Replacement key
– a replacement key is an alternate key that the system can display on forms instead of a meaningless numeric primary key value
– each table can have a maximum of one replacement key


•Ax table: DRVCarsTable
•Table fields: Brand, Model, CarId, PollutionStandard, Color, …
•Unique indexes
•ReplacementKey: Brand, Model
•CarIdx: CarId
•Non unique index
•PollutionStandardIdx: PollutionStandard

Special property: Create RecId index: NO

Non-Unique indexes

SQL change tracking

Leave a Reply

Your email address will not be published. Required fields are marked *