Sql-server – Wide clustered index vs multiple narrow nonclustered indexes

clustered-indexindex-tuningnonclustered-indexphysical-designsql server

Say I have a contrived Student table like so:

CREATE TABLE Student (
    Id IDENTITY INT,
    SchoolId INT NOT NULL,
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL
)

Instinctively, I'd make Id the Primary Key (and thus the clustered index). However, I'd find myself searching by SchoolId so I'd make a nonclustered index on SchoolId.

How would this fare against having the Primary Key (and clustered index) to be SchoolId, Id? I will always have the SchoolId if I need to search by Id, so I'll get to use the clustered index anyways, and if I need to search by SchoolId only, the records will be physically next to each other.

If I were to do any type of searching or batch updating, they'd be on SchoolId specific records, e.g. find all kids with name/number/whatever at SchoolId. I'd never do these types of operations across multiple SchoolIds in the same transaction. Does the benefit of having these records physically next to each other make this method much better than simply having a clustered index on Id?

Are there massive downsides to using the latter? I'm still new at this and there's plenty of topics I don't fully comprehend yet (e.g. fragmentation) and how it would factor into a situation like this.

Best Answer

If you will always have SchoolId then you could benefit from making the clustered index a composite key of SchoolId, Id as you won't have to have an additional index on SchoolId to avoid a table scan. Not having the additional index will let Inserts/Updates/Deletes complete faster as the transaction only has to update one index.

You may find as you create other queries that use the other fields in the where clause that an additional index on those might be beneficial so SQL Server can seek right to that index leaf which will help reduce the number of row returned.

Related Solutions

Sql-server – Should a table have a clustered index even if it doesn’t have appropriate fields for it

1) IF PlayerId is assigned with NEWSEQUENTIALID, you could consider that as the clustered index.

2) Otherwise, you can add an IDENTITY and make that clustered (questionable benefit, since all access will be through the PK you have already established).

3) Or you can leave it as a heap - with appropriate non-clustered indexes.

My order of preference would be 1, 3, 2 assuming you can't change the uniqueidentifier to an IDENTITY instead.

Can you explain why you are using uniqueidentifier in the first place? - that may have some bearing on this.

Sql-server – Clustered vs Nonclustered Index

Since we are talking about the clustered index, just because you defined the CI key column as ID, you still have the DeletedDate data in the leaf data pages of the index. That's the nature of the clustered index: It is the table data.

Because you are typically having queries that look like:

select *
from YourTable
where DeletedDate is null;

You will likely benefit from a filtered index.

create nonclustered index IX_YourFilteredNci
on YourTable(<Key Columns Here>)
where DeletedDate is not null;
go

I didn't explicitly put the key columns here (and nonkey columns through the use of the INCLUDE clause) because you didn't publish the DDL of your table.

As in my comment above to your question, the choice of key columns (not just columns, but also the order of the columns) will largely depend on your workload and the typical queries that would be using this index.

If you are looking to cover your query(ies), then you would need to ensure that the index satisfies all of the data required of the query(ies). Not to mention, if you have other WHERE clauses (besides your NULL check on DeletedDate) or joins to consider, then the order of your key columns can be the deciding factor between a scan or a seek. And even though it is filtered, and depending on how much data you have in the index, the penalty could be considerable.

Best Answer

Related Solutions

Sql-server – Should a table have a clustered index even if it doesn’t have appropriate fields for it

Sql-server – Clustered vs Nonclustered Index

Related Question