What column to use for clustered index?
Posted on 2013-06-29
I am struggling with the question which column to use for my clustered index? So far, if I have the table:
id, firstname, lastname
I always automatically gave identity(1,1) and clustered index (primary key) on that one. But - is this always correct? I practically never seek by id. There are always some other columns that I usually search on. In above scenario: if I search by firstname - does clustered index on id has any impact on my query? Or it would be the same if I just left whole table as a heap? Same goes if I use my search by lastname ?
What about this table:
id, ssid, firstname, lastname
Is there a point to have id column at all in the table? Or is it better to put id out and have clustered index on ssid?