You are designing a dimension table in an Azure Synapse Analytics dedicated SQL pool. You need to create a surrogate key for the table. The solution must provide the fastest query performance. What should you use for the surrogate key?
C. an IDENTITY column
When designing a dimension table in a data warehouse, it's important to consider the types of queries that will be run against it. IDENTITY columns are generally the best option for surrogate keys in dimension tables because they provide the fastest query performance. IDENTITY columns are auto-incremented and indexed by default, which makes them ideal for use as primary keys. They also require less storage space than GUID columns and are less likely to cause fragmentation in indexes.
Remember that the IDENTITY value doesn’t guarantee order due to the distributed architecture, but it ensures uniqueness within each distribution.The IDENTITY property generates unique values automatically.
It scales out across all distributions in the dedicated SQL pool.
It doesn’t impact load performance.
Each distribution maintains its own set of IDENTITY values.
Ideal for creating surrogate keys without manual intervention.
Surrogate key is substitute key used if there is no natural or business key within the table. It should be always instantiated by IDENTITY(1,1).
upvoted 2 times
...
This section is not available anymore. Please use the main Exam Page.DP-203 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
anks84
Highly Voted 2Â years, 8Â months agonicky87654
Highly Voted 2Â years, 4Â months agoEmnCours
Most Recent 5Â months, 2Â weeks agorocky48
1Â year, 3Â months agokkk5566
1Â year, 8Â months agoDeeksha1234
1Â year, 9Â months agoakhil5432
1Â year, 9Â months agovigilante89
2Â years, 4Â months ago