Explanation

A surrogate key is a system-generated unique identifier that serves as a substitute for natural keys in a database table. For the retail store table, implementing a surrogate key using an IDENTITY property is the optimal solution for several reasons:

Why Option A is Correct:

• Automatic Uniqueness: The IDENTITY property automatically generates unique, sequential values for each new row inserted into the table, ensuring no duplicate keys exist
• Efficient Joins: Surrogate keys created with IDENTITY provide efficient join operations when linking sales transaction records to retail store information
• Stability: Unlike natural keys (such as store names or addresses), surrogate keys remain constant even when business data changes, which is crucial for maintaining historical integrity in sales transaction datasets
• Performance: IDENTITY columns are optimized for indexing and query performance, making them ideal for high-volume transaction systems
• Simplicity: The implementation is straightforward and requires minimal maintenance overhead

Why Other Options Are Less Suitable:

Option B (System-versioned temporal table): While temporal tables are excellent for tracking historical changes, they don't inherently provide surrogate key functionality. Temporal tables focus on versioning existing data rather than generating unique identifiers.

Option C (User-defined SEQUENCE object): SEQUENCE objects can generate unique values, but they require explicit management and additional code to assign values to table columns. This adds complexity compared to the automatic nature of IDENTITY properties.

Option D (Table with FOREIGN KEY constraint): FOREIGN KEY constraints enforce referential integrity between tables but do not generate surrogate keys. They rely on existing primary keys in related tables rather than creating new unique identifiers.

The IDENTITY property approach aligns with Azure Data Engineering best practices for creating efficient, maintainable surrogate keys that support the performance and integrity requirements of sales transaction datasets.