I have a table that stores notes
create tblNote( Id int identity(1,1), ParentId int , ParentType varchar(32), NoteType varchar(32), Note varchar(max), CreatedBy varchar(25), CreatedDate datetime, . . . <other metadata about the note> )
I have done a lot of reading recently about how MSSS handles indexes (2005 and forward).
I have a clustered index on ID
[ I have considered changing the clustered index to parentId, parentType since that is reasonably narrow and it is static. ]
The overwhelming percentage of queries against this table are going to be along the lines of
select NOTE, createdDate, createdBy from tblNote where parentId = 12 and parentType = 'RFQ'
The question I want to ask today (though any feedback is welcome) is this:
The NC index I could add is:
create index idx_nc_note_parent( parentId , parenttype ) include (createdby, createdDate)
This would be useful in creating little lists of the notes where we might include who and when type info.
I am hesitant to include a
varchar(max) field. It seems like it would really hurt the amount of the index that would be cached (Is this reasonable or unreasonable)
Assuming I dont include the
NOTE field, a RID Lookup will be necessary to actually fetch the note content if it is requested.
While I have read quite a bit about how expensive RID lookups are, it still has to be better to have this index as opposed to doing a table scan, RIGHT?
[apologies for the code block, i have added the 4 spaces, but maybe i did it wrong? ]