Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A data analyst is querying a large table in Azure SQL Database that stores historical sales data. Queries frequently filter by a `SaleDate` column, but performance is slow. The `SaleDate` column is currently not indexed. Which basic management task should the analyst recommend to improve query performance on this column?
- AUpdate statistics on the `SaleDate` column
- BCreate a non-clustered index on the `SaleDate` column
- CRebuild the table containing `SaleDate`
- DChange the `SaleDate` column data type to `DATE`
Show answer & explanationAnswer & explanation
Correct answer: B. Create a non-clustered index on the `SaleDate` column
Creating an index on a frequently filtered column like `SaleDate` significantly speeds up data retrieval by allowing the database engine to quickly locate relevant rows without scanning the entire table.
Why the other options are wrong
- A. Updating statistics helps the query optimizer but is less impactful than an index for filtering on an unindexed column.
- C. Rebuilding the table might reorganize data but won't fundamentally improve filter performance without an index.
- D. Changing the data type might have minor storage benefits but will not resolve the performance issue related to filtering on an unindexed column.
Database Index
A database object that provides fast lookup of data in a table, similar to an index in a book. It speeds up data retrieval operations on a table at the cost of additional storage and slower data modification operations.
- Improves query performance, especially for WHERE clauses and JOINs.
- Can be clustered (sorts and stores data rows) or non-clustered (separate structure).
- Should be created on frequently queried or joined columns.
Memory trick: Index: The fast lane for your data.