Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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? ]

share|improve this question
    
How many different ParentTypes do you have? If they are not in the billions, that column could be made much narrower. –  ypercube Apr 4 '13 at 16:31
    
parentType could easily be scaled down to 10 characters, but i dont think that really affects the question materially. What are your thoughts? –  greg Apr 4 '13 at 20:28
    
How many rows do you expect to get back for an average (parentId, parentType) combination? –  Jon Seigel Apr 4 '13 at 21:43
    
Its usually a small number. <10. A great example of this table are the very comments we are using now. –  greg Apr 5 '13 at 12:51
1  
I think you mean key lookup, not RID lookup. RID lookups occur on heaps, precisely because there is no clustered index (and no valid index that satisfies the query). –  Aaron Bertrand May 12 '13 at 15:47
add comment

migrated from stackoverflow.com Apr 4 '13 at 18:11

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 4 down vote accepted

Since you said that most queries would generally return few rows, letting the query use a RID lookup (key lookup in this case as the table has a clustered index) is perfectly fine for retrieving a potentially large field. For a highly-available system, I couldn't recommend putting a LOB type in an index anyway as this prevents online rebuilds (for versions of SQL Server before 2012). Also, you need to be very careful that the query plan always sticks to a seek-type plan and not tip into a table scan which could be very expensive. This is a case where I might use a table hint (or a plan guide if the query can't be modified) even if it isn't absolutely necessary.

Another option is to recreate the clustered index on the combination of parentId and parentType if this combination of values is static and generally increasing over time. It would be better if parentType was an integral type, though, and you may want to look into changing that anyway to save storage space if the base table is, or will become, large. Considering this change also involves looking at how it might affect indexing for the other classes of queries that run against this table.

If either of those two methods aren't fast enough for the workload, look into implementing a data caching solution using something like AppFabric, which scales much more readily than running a SQL query every time you need data. This can be a huge payoff; the cost is added complexity.

share|improve this answer
    
As of SQL-Server 2012, ONLINE rebuild is now supported for VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) and XML. –  Calgary Coder May 12 '13 at 14:50
    
@CalgaryCoder: Thanks. Updated. –  Jon Seigel May 12 '13 at 14:57
    
thank you for the suggestion. ParentId,ParentType,SEQ is static but is not ever increasing. –  greg May 13 '13 at 18:29
add comment

Can you try this out?

Create an index on parentID and parentType to look up applicable IDs...

Create  NonClustered Index idx_nc_note_parent On tblNote (parentID, parentType)

Join the IDs back to the base table to pull the desired information using the clustered index...

Select  NOTE, createdDate, createdBy
From   (Select  ID
        From    tblNote
        Where   parentID = 12
        And     parentType = 'RFQ') n
Join    tblNote tn
        On  n.ID = tn.ID
share|improve this answer
    
I will try that and see what the query plan does. The more I think about this, the more I think that maybe I should change the Clustered Index to ParentId, ParentType, NoteId. (Adding NoteId will guarantee uniqueness) –  greg Apr 4 '13 at 20:30
    
@greg That would be a really bad idea. If you have an identity column I can't think of any scenario where you would not want to use it as your clustered index. If you use parentID you open yourself to all sorts of horrible fragmentation issues unless you insert parentIDs sequentially. If it is static unchanging data then I suppose it is fine as well. If you do this and you regularly insert into the table consider using a fillfactor lower than 100 to leave room for new inserts. –  Love2Learn Apr 4 '13 at 20:49
1  
This answer suggests that using a join is going to be faster than a key/bookmark lookup? Really? –  Jon Seigel Apr 4 '13 at 21:45
    
@Love2Learn - I think you are right. Different parent tables will have different #s of rows and thus parent Id will not always be everincreasing. It would require maintenance as fragmentation would set in. Thanks for helping me think this through. On your original suggestion to use a join... this was an interesting idea and I gave it quite a bit of thought. The conclusion I came to is that this may not hurt, but it probably wouldnt help. The leaf levels of the NC index already include a key into the clustered index. That is essentially what the RID Lookup is, if I am understanding things. –  greg Apr 5 '13 at 12:53
    
Thanks for all the input. I had hoped to set up a test and run it over the weekend but was not able to make the time available. –  greg Apr 8 '13 at 15:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.