How table indexes are looking in the SQL layer for F&O?
Definitions:
Index
– 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
Scenario:
•Ax table: DRVCarsTable
•Table fields: Brand, Model, CarId, PollutionStandard, Color, …
•Indexes:
•Unique indexes
•ReplacementKey: Brand, Model
•CarIdx: CarId
•Non unique index
•PollutionStandardIdx: PollutionStandard
•SurrogateKey
Special property: Create RecId index: NO
Non-Unique indexes
SQL change tracking
