SQL Server nvarchar列索引最佳实践咨询：大表查询性能优化

Great question—let's break this down step by step based on your table structure, query pattern, and SQL Server 2017 environment.

First: Your Current Index Plans Are Solid #

Let's start by validating what you've already got right:

• Table C's clustered index: UNIQUE CLUSTERED INDEX (Date, ArticleId) is perfect for your query. It aligns with the Date grouping and the ArticleId join to Table B, allowing SQL Server to quickly scan and aggregate sales data in order.
• Table B's planned clustered index: UNIQUE CLUSTERED INDEX (UploadId, ArticleId) is also a strong choice. It lets the query instantly narrow down to all rows for your target UploadId, and the included ArticleId makes joining to Table C efficient without extra lookups.

GroupingA/GroupingB: Should You Index Them? #

You’re right to hesitate about using character columns for clustered indexes—they’re poor choices for clustered keys because they’re wider (wasting storage) and more expensive to maintain as data grows. Here’s what to do instead:

Non-Clustered Covering Index for Table B #

Since you need GroupingA and GroupingB for grouping, create a non-clustered covering index on Table B that includes these columns. This avoids expensive "bookmark lookups" back to the clustered index:

CREATE NONCLUSTERED INDEX IX_B_UploadId_ArticleId_Groupings
ON B (UploadId, ArticleId)
INCLUDE (GroupingA, GroupingB);

This index works perfectly with your query:

1. It filters by UploadId first (matching your WHERE clause)
2. It has ArticleId for joining to Table C
3. It includes GroupingA/GroupingB directly, so the query doesn’t need to fetch any other data from Table B.

Should You Convert GroupingA/GroupingB to Lookup Tables? #

This depends on how unique your grouping values are:

• If grouping values are highly repetitive: Definitely create lookup tables. For example:

  -- GroupingA lookup table
  CREATE TABLE GroupingsA (
      GroupingAId INT IDENTITY(1,1) PRIMARY KEY,
      GroupingAValue NVARCHAR(255) UNIQUE NOT NULL
  );
  
  -- Update Table B to use the ID instead of the string
  ALTER TABLE B ADD GroupingAId INT FOREIGN KEY REFERENCES GroupingsA(GroupingAId);
  

  Do the same for GroupingB (and future GroupingC). The benefits here are:
  • Smaller Table B storage (int vs. nvarchar(255) saves significant space as K grows)
  • Faster grouping operations (integer comparisons are cheaper than string comparisons)
  • Better scalability for future grouping columns (just add new lookup tables and foreign key columns)
• If grouping values are nearly unique: Skip the lookup tables. The extra join overhead won’t be worth the minimal storage savings, and you’ll complicate your schema unnecessarily.

Planning for Future GroupingC #

• If you use lookup tables: Adding GroupingC is trivial—create a new lookup table, add a foreign key column to Table B, and update your covering index to include the new GroupingCId.
• If you stick with string columns: Just update your non-clustered covering index on Table B to include GroupingC (no need to change the index key, which keeps maintenance costs low).

Extra Optimization Tips #

• Update statistics regularly: Tables B and C grow quickly, so outdated statistics can lead to bad execution plans. Run UPDATE STATISTICS B; and UPDATE STATISTICS C; periodically, or ensure auto-update statistics is enabled (it’s on by default, but double-check).
• Test a non-clustered index on Table C (optional): If your queries often filter on small date ranges, consider adding this index to speed up joins:

  CREATE NONCLUSTERED INDEX IX_C_ArticleId_Date_Sales
  ON C (ArticleId, Date)
  INCLUDE (Sales);
  

  This lets SQL Server quickly fetch sales data for specific ArticleIds without scanning the entire clustered index. Test it against your workload to see if it helps—for large date ranges, your existing clustered index may still be more efficient.

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