SQL及SQL Server表索引选择咨询：基于给定表结构与查询需求

Hey there, let's walk through this clearly—since you're gearing up for a paper exam, we'll stick to proven, exam-focused logic here.

First, recap your setup: Table R has columns (ID, name, salary, zipcode), with ID as the primary key (so it's the clustered index). You need two additional non-clustered indexes to optimize the three queries you listed. Let's break down each query and the index choices:

Query 1: select * from R where salary < 4000 and name = "john" #

This query uses two filters: an equality match on name and a range match on salary. For composite indexes, the best practice is to put equality columns first—they narrow down the result set quickly, then the range column can filter within that subset.

Recommended index: CREATE NONCLUSTERED INDEX IX_R_Name_Salary ON R (name, salary)

• Why? The index lets SQL Server jump straight to all rows where name = 'john', then efficiently scan only those rows to find salary < 4000. Even though the query uses select * (so it'll do a key lookup back to the clustered index for the rest of the columns), this index still avoids a full table scan, which is the biggest win here.

Query 2: select * from R where id = 100 and zipcode = '76200' #

You don't need an extra index for this one! Since ID is the primary key (clustered index), id = 100 already uniquely identifies a single row. The zipcode condition is redundant here—once the database finds the row via ID, it just checks the zipcode value on that one row. The clustered index fully covers this query.

Query 3: select * from R where salary > 10000 #

This is a single range filter on salary. A single-column non-clustered index on salary will let the database quickly locate all rows where salary > 10000 without scanning the entire table.

Recommended index: CREATE NONCLUSTERED INDEX IX_R_Salary ON R (salary)

• Why? This index creates a sorted structure of salary values, so SQL Server can start at the first value greater than 10000 and read sequentially from there, which is way faster than a full table scan.

Final Index Choices #

The two additional indexes you need are:

• (name, salary) (optimizes Query 1)
• (salary) (optimizes Query 3)

To lock this in for your exam, remember these core rules:

• Always place equality-matching columns first in composite indexes
• Single-column indexes work perfectly for queries filtering on just that column
• Primary key/clustered indexes automatically optimize any query filtering on the primary key alone

内容的提问来源于stack exchange，提问作者SrihariRaghu