[BC Idea][TableInformation] : Enhance Indexes List Part with SIFT details, visual indicators, and improved index management

[duiliotacconi]

BC Idea Link

https://experience.dynamics.com/ideas/idea/?ideaid=0dce5f31-6512-f111-83da-6045bd81940a

Description

GitHub Issue ÔÇö Body
BC Idea Link

Description
­ƒÄ» Goal
Improve the "Indexes List Part" page (8704) to provide a comprehensive, at-a-glance view of SQL index structure, SIFT maintenance, usage patterns, and health ÔÇö while adding safeguards against accidental SIFT index disruption.

­ƒæñ User Stories
Index analysis: As a database administrator, I want to see the fields composing each index and its included columns directly on the page, so I can assess index coverage without querying the system tables separately.

SIFT visibility: As a consultant, I want to see which indexes maintain SIFT and which fields are aggregated, so I can evaluate FlowField performance impact at a glance.
Health monitoring: As an administrator, I want disabled indexes highlighted in red and high-fragmentation indexes visually flagged, so I can quickly spot indexes that need attention.
Usage metrics: As a performance analyst, I want a Reads/Writes ratio column, so I can identify write-heavy indexes that may be candidates for removal or restructuring.
Data accuracy: As a user, I do not want to see misleading default timestamps (e.g., 01/01/0001) for Last Seek/Scan/Lookup/Update when no activity has occurred ÔÇö these should be blank.
SIFT protection: As an administrator, I want to be prevented from accidentally disabling a SIFT index, with a clear error message explaining why, instead of a confusing platform error.

­ƒøá Technical Concept

1. New Data Columns on the Repeater
   Index Fields (Rec."Column Names"): Shows the fields composing the index (width 40).
   Maintain SIFT (Boolean variable MaintainVSIFT): Populated from the Key virtual table's MaintainSIFTIndex field via a lookup in OnAfterGetRecord.
   SIFT Fields (Text variable VSIFTFields): Populated from the Key virtual table's SumIndexFields field.
   Included Columns (Rec."Included Fields"): Shows non-key columns included in the index for covering queries (width 40).
   Reads/Writes (Decimal variable ReadWriteRatio): Computed as (User seeks + User scans + User lookups) / User updates (denominator defaults to 1 when updates = 0).
2. Column Reordering & FreezeColumn
   Reorder columns: Enabled  AL Defined  Unique  Index Name  Index Fields  Maintain SIFT  SIFT Fields  Included Columns  Fragmentation  Index Size  Seeks  Scans  Lookups  Updates  Reads/Writes  Last Seek  Last Scan  Last Lookup  Last Update  Statistics updated at.
   Set FreezeColumn = "Index Fields" so identification columns remain fixed during horizontal scroll.
3. Visual Styling (StyleExpr)
   IndexStyleExpr: 'Unfavorable' (red) when Rec.Enabled = false, otherwise 'Standard'. Applied to all fields.
   FragmentationStyleExpr: 'Unfavorable' when Rec."Fragmentation %" > 30, otherwise inherits IndexStyleExpr.
4. Blank Timestamps for Zero Counters
   Convert Last Seek/Scan/Lookup/Update from record fields to Text variables (LastSeekText, LastScanText, LastLookupText, LastUpdateText).
   In OnAfterGetRecord, set to Format(Rec."Last seek") only when the corresponding counter (User seeks, etc.) is non-zero; otherwise leave blank.
5. SIFT Disable Guard
   In both TurnIndexOff and TurnIndexOffInAllCompanies action triggers, before proceeding:
   Call FindKeyFromDatabaseIndex(Rec, KeyRec) to locate the matching Key record.
   If KeyRec.MaintainSIFTIndex is true, raise Error(CannotDisableSIFTIndexErr, Rec."Index Name").
   New label: CannotDisableSIFTIndexErr: Label 'Cannot enable or disable SIFT indexes ''%1''.'
6. Label Cleanup
   TurnOnIndexQueueInfoMsg: Remove "(over the night local time)" ÔÇö maintenance window timing is environment-specific.

I will provide the implementation for this BC Idea

I will provide the implementation for this BC Idea

• I will provide the implementation for this BC Idea
  Internal work item: AB#631340

[duiliotacconi]

This is how it will look like

[JesperSchulz]

🤖 AI triage completed. Product group, please validate the triage report (Microsoft internal access only, use Ctrl+Click to open in new tab).

---

_{Was this triage helpful? React with 👍 or 👎 on this comment to provide feedback.}