Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A company uses Azure SQL Database for its transactional workloads. The database administrator wants to improve the performance of frequently executed queries that involve filtering and sorting on specific columns. Which database object should the administrator create to achieve this goal?
- AView
- BIndex
- CStored Procedure
- DTrigger
Show answer & explanationAnswer & explanation
Correct answer: B. Index
An index is a database object that improves the speed of data retrieval operations on a database table. By creating an index on columns used in WHERE clauses (filtering) or ORDER BY clauses (sorting), the database engine can find and retrieve data much faster, directly addressing the goal of improving query performance.
Why the other options are wrong
- A. A view is a virtual table that simplifies complex queries but doesn't inherently improve the performance of underlying table access.
- C. A stored procedure is a pre-compiled set of SQL statements that can improve execution efficiency but doesn't directly speed up data access for filtering/sorting on specific columns.
- D. A trigger is a special type of stored procedure that automatically executes when a data modification event occurs on a table; it does not directly improve query performance.
Database Index
A database object that provides a quick lookup mechanism for data, improving the speed of data retrieval operations on a table.
- Speeds up data retrieval (SELECT queries)
- Improves performance for WHERE and ORDER BY clauses
- Can slow down data modification operations (INSERT, UPDATE, DELETE)
- Stored separately from the actual data
Memory trick: To speed up queries, index your data smartly.