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
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
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.