Wide clustered index vs multiple narrow nonclustered indexes?

Question

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.

Answer 1

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.

Answer 2

You have received good advice for keeping the index as narrow as possible, both for space use and for controlling the amount of I/O. But you have maneuvering room in your decisions.

If you create an index on SchoolID and ID is the clustered index, then your index on SchoolID will include the clustered index as well. Depending on the distribution of your SchoolID it could be that the SchoolID index could be better used alone in some contexts.

This could be when you are searching some data set where the SchoolID is the important join to some data in another table. This other table will likely have its own clustered index.

Disclaimer: As long as we are only talking about 2 small ID columns, 1 of which is the cluster key, the impact will be pretty much zero (0).

Thinking Ahead: But do now think about the future and how you want to be designing more complex indexes to support a more complex system.

If you assume that you will eventually be creating a larger database, with many tables, and many indexes, you should begin thinking about these issues now, so that you are developing a strategy that will stand up well for the future.

For What it is Worth: I have seem code that tried to include pretty much everything in the index. (I am sure that you are not planning to do that.) But the big fat index that was generated was, let us say, not optimal.