Hard Difficulty Questions

Active geo-replication

Why wrong: Incorrect. Active geo-replication provides readable secondary replicas in different regions for disaster recovery, but it is primarily for high availability and can incur higher cost. It is not the simplest solution for offloading reads within the same region.

Azure SQL Database elastic pool

Why wrong: Incorrect. An elastic pool is used to share resources among multiple databases, not to create read replicas for a single database.

Query Performance Insight

Why wrong: Incorrect. Query Performance Insight is a monitoring and diagnostic tool that helps identify performance issues, but it does not offload read traffic.

Create a non-clustered index on the TransactionDate column.

Why wrong: A non-clustered index speeds up searches for specific date ranges but does not help with large full-table scans for monthly aggregations, nor does it simplify archiving.

Create a materialized view for the current month's data.

Why wrong: A materialized view can pre-aggregate data for faster queries, but it does not reduce the scan on the base table and does not provide an easy archiving mechanism for old data.

Convert the table to use a clustered columnstore index.

Why wrong: A columnstore index improves performance for analytic scans but still requires scanning all partitions or the entire table. It does not simplify archiving of old data.

Sharding across multiple Azure SQL Managed Instances

Why wrong: Sharding is a horizontal scaling pattern that distributes data across multiple databases, not the same as table partitioning within a single database.

Index partitioning only

Why wrong: Index partitioning is a part of table partitioning but not sufficient on its own; you need full table partitioning to maintain the existing design.

Federated tables

Why wrong: Federated tables are not a native feature of Azure SQL Managed Instance; they are used in SQL Server with linked servers and are not supported for partitioning.

Read Scale-out

Why wrong: Read Scale-out creates read-only replicas to offload read queries but does not reduce write contention on the primary.

Elastic Database Query

Why wrong: Elastic Database Query allows querying across multiple databases but does not address contention within a single database.

Transparent Data Encryption (TDE)

Why wrong: TDE encrypts data at rest for security purposes and has no impact on write contention or transaction performance.

Create a nonclustered index on (CustomerID, OrderDate)

Why wrong: The leading column (CustomerID) does not help filter by OrderDate, so the query cannot perform a seek on OrderDate; it would scan or require sorting.

Change the clustered index to (OrderDate)

Why wrong: Altering the clustered index could slow down inserts and queries that rely on OrderID; indexing strategy should not negatively impact primary key lookups.

Create a filtered index on OrderDate WHERE CustomerID IS NOT NULL

Why wrong: A filtered index is useful for specific subsets, but it still does not provide the sort order needed and may not be used if many queries do not include the filter predicate.

Increase the number of compute nodes

Why wrong: Adding compute nodes increases parallelism but the overhead of reading many tiny files remains, limiting the improvement.

Use columnstore indexes on external tables

Why wrong: Columnstore indexes apply to tables in the database, not to external files. External tables read data directly from the file format (Parquet) which already uses columnar storage.

Change the partition column to a different date granularity

Why wrong: Changing partition granularity does not address the small file problem; it may even create more or fewer partitions but the files themselves remain small.

ACID compliance

Why wrong: Incorrect. ACID compliance applies to individual databases but cannot be enforced across unrelated stores without custom coordination.

Idempotency

Why wrong: Incorrect. Idempotency ensures repeated operations produce the same result but does not address cross-store transactional consistency.

Schema flexibility

Why wrong: Incorrect. Schema flexibility refers to the ability to handle varying data structures, not transactional consistency.

Azure Databricks with interactive clusters

Why wrong: Azure Databricks requires a running cluster (even if auto-terminating, it still provisions compute) and charges per cluster hour, not per query. It is not a pay-per-query model.

Azure Stream Analytics

Why wrong: Stream Analytics is designed for real-time stream processing, not for ad-hoc SQL queries on historical data stored in files.

Azure HDInsight with Spark

Why wrong: HDInsight requires provisioning and managing clusters, and costs are incurred for the cluster uptime, not per query. It does not offer a serverless pay-per-query option.

Use external tables in the dedicated SQL pool to query the data lake directly.

Why wrong: External tables in a dedicated SQL pool allow querying data in the lake, but the dedicated pool's compute resources are always running (unless paused), incurring costs even for sporadic ad-hoc queries.

Load the raw data into the dedicated SQL pool before querying.

Why wrong: Loading raw data into the dedicated pool would increase storage and compute costs for data that is only queried occasionally. This approach is inefficient for ad-hoc analysis.

Use Azure Data Explorer to query the data lake.

Why wrong: Azure Data Explorer is optimized for interactive analytics on large volumes of streaming and time-series data, but it uses a Kusto query language (KQL), not T-SQL, and is not the best fit for general T-SQL ad-hoc queries.

A. Partition key: PlayerID; Consistency: Eventual

Why wrong: PlayerID is a good partition key for point lookups, but eventual consistency does not guarantee that a write is immediately visible to reads, which violates the requirement.

C. Partition key: Achievements; Consistency: Strong

Why wrong: Achievements is an array property; using it as a partition key would create many partitions with unpredictable cardinality and cannot be used for point lookups by PlayerID.

D. Partition key: Email; Consistency: Bounded staleness

Why wrong: Email might be a candidate but could lead to hot partitions if many players share email domains. Bounded staleness is not the strongest consistency level and may not meet the immediate visibility requirement.

SQL API for both profiles and logs

Why wrong: While the SQL API can store both, it is not optimized for simple key-value lookups with the same performance as the Table API. Using the SQL API for profiles would still work but would be less efficient and more costly for simple reads.

MongoDB API for profiles and Cassandra API for logs

Why wrong: The MongoDB API is document-based and can handle JSON, but it is not as simple as the Table API for key-value. The Cassandra API is column-family and does not natively support SQL-like querying on JSON documents; it is designed for wide-column workloads.

Table API for both profiles and logs

Why wrong: The Table API only supports simple key-value lookups and does not support querying nested JSON properties or SQL-like syntax. It cannot handle the querying requirements for session logs.

Azure Stream Analytics

Why wrong: Incorrect. Azure Stream Analytics is designed for real-time stream processing only. It cannot handle batch processing of daily inventory files or query data lake files directly with SQL.

Azure Data Lake Analytics

Why wrong: Incorrect. Azure Data Lake Analytics is a batch analytics service that uses U-SQL. It does not support real-time stream processing and is being replaced by Azure Synapse Analytics.

Azure HDInsight

Why wrong: Incorrect. Azure HDInsight is a managed Hadoop/Spark cluster that can handle both batch and stream processing, but it is not a single unified platform for SQL querying of data lake files; it requires more manual setup and management compared to Synapse.

Partition key: PostID; Index: range on Timestamp

Why wrong: Incorrect - Partitioning by PostID distributes posts randomly across partitions. Queries for a specific UserID will hit every partition (cross-partition query), increasing RU.

Partition key: Timestamp; Index: range on UserID

Why wrong: Incorrect - Partitioning by Timestamp scatters posts for the same user across many partitions. Filtering by UserID then becomes a costly cross-partition query.

Partition key: UserID; Index: composite on (UserID, PostID)

Why wrong: Incorrect - While UserID is a good partition key, the composite index on (UserID, PostID) does not support ordering by Timestamp, requiring a sort after retrieval, which consumes more RU.

Create a nonclustered index on OrderDate only.

Why wrong: An index on OrderDate alone would help with filtering by OrderDate and sorting, but it would not help with the additional filter on OrderStatus. The database would still need to filter by OrderStatus after the seek, which may be less efficient.

Create a clustered columnstore index on the table.

Why wrong: Clustered columnstore indexes are designed for large analytical workloads that scan many rows, not for OLTP queries that filter on specific columns and sort. They may degrade performance for point queries and range scans with sort operations.

Create a nonclustered index on OrderStatus only.

Why wrong: An index on OrderStatus alone helps with filtering on OrderStatus, but does not help with the OrderDate filter or sorting by OrderDate. The database would still need to sort the results or scan many rows.

Azure SQL Database (single database)

Why wrong: Using individual single databases for each tenant would be more expensive and less efficient because resources are allocated per database without sharing.

Azure SQL Managed Instance

Why wrong: Managed Instance is designed for larger databases and lift-and-shift migrations, not for managing thousands of small databases in a shared model.

SQL Server on Azure Virtual Machine

Why wrong: IaaS offers full control but higher management overhead and cost when hosting many small databases, and it lacks built-in elastic pooling features.

Read scale-out

Why wrong: Incorrect. Read scale-out allows read-only replicas for reporting queries, but it does not increase compute capacity for write-heavy transactions.

Elastic Pool

Why wrong: Incorrect. Elastic Pools share resources among multiple databases, not designed to scale a single database rapidly under load.

Geo-replication

Why wrong: Incorrect. Geo-replication provides data redundancy and failover across regions, not dynamic scaling of compute resources.

Increase the RU/s

Why wrong: Increasing RU/s improves throughput but does not resolve the logical partition size limit.

Split the container into multiple containers by userId range

Why wrong: Azure Cosmos DB handles physical partition splitting automatically. Manually splitting containers is not supported and would complicate the application.

Use a different API like MongoDB

Why wrong: Changing the API does not affect the logical partition size limit, which is a core constraint of Cosmos DB.

Azure SQL Database (single database)

Why wrong: Azure SQL Database does not support three-part cross-database queries or SQL Server Agent, requiring significant application changes.

Azure SQL Database (elastic pool)

Why wrong: Elastic pools are a resource management model for multiple Azure SQL Databases; they share the same limitations as single databases regarding cross-database queries and SQL Agent.

Azure Synapse Analytics dedicated SQL pool

Why wrong: Azure Synapse Analytics is a data warehousing service not designed for transactional workloads; it lacks SQL Server Agent and does not support three-part cross-database queries in the same manner.

Rebuild the clustered index on (OrderDate, CustomerID).

Why wrong: Changing the clustered index would rearrange the entire table, potentially harming other queries that rely on OrderID ordering. It is a major change with broader impact and may not be the best first approach.

Create a non-clustered index on OrderDate.

Why wrong: This index would require filtering on CustomerID separately, likely leading to many key lookups and still high reads. It is not as efficient as a composite covering index.

Create a filtered index on OrderDate for recent dates.

Why wrong: A filtered index would only benefit queries restricted to the filtered condition (e.g., recent dates) and would not help for historical range queries. It is too narrow in scope.

Azure SQL Database (single database)

Why wrong: Single database is a PaaS offering but does not support instance-level features like CLR or database mail. It is designed for a single logical database.

Azure SQL Database elastic pool

Why wrong: Elastic pools are a deployment option for multiple single databases that share resources. They do not provide instance-level features like CLR or database mail.

Azure SQL Database Hyperscale

Why wrong: Hyperscale is a service tier for single databases, designed for large databases but does not support instance-level features such as CLR or database mail.