4

I am building a compound index, and I'm not 100% sure which way to include a bit field that is part of the table. The index will be for something similar to a list of forum topics, where I want the single index to be by ForumId (int), IsSticky (bit), then DatePosted (desc) (datetime2(0))

But I want to make sure I handle the IsSticky bit field properly; Do I index it as if true is 1 or -1? If it's 1, I would index IsSticky descending; if -1, I would index it ascending.

Also, I'm asking for SQL 2008, but I'd be interested in knowing if it's different in 2005.

Improve this question

1 Answer 1

Reset to default
18

A SQL Server bit field can be 0, 1 or NULL, so I'm unsure where the -1 part of your question features.

Skipping past that, the most appropriate solution for "sticky threads" in a forum database is to use a separate filtered index. Flag a sticky thread as IsSticky=1 and create a filtered index along the lines of:

CREATE NONCLUSTERED INDEX IX_Posts_StickyThread
    ON dbo.Posts (ForumId, PostId)
WHERE IsSticky = 1;

Filtered indexes were new to SQL2008 so this wouldn't be an option for SQL2005. The SQLCat article Using a Low-Selectivity BIT Column First Can Be the Best Strategy would be applicable reading for an approach to this in 2005 (edit: article has been archived to the eBook SQLCAT's Guide to Relational Engine).

And just to address the title question bit fields in an index are ordered same as per ORDER BY bit_field i.e. NULL, 0, 1 (credit @MartinSmith).

Improve this answer
6
  • Mark, question for you. The only benefit of using a filtered index is purely to save space, right? I mean, performance wise this would be similar to create nonclustered index IX_indexname on dbo.Posts (ForumId, PostId). Is that a correct statement?
    – Thomas Stringer
    Dec 18, 2011 at 3:02
  • 4
    Yes and no. Sure, you're only saving a few bytes in a case like this and the performance benefit is going to be difficult to distinguish from a regular non-filtered index. Transpose the situation to something StackOverflow scale however and that benefit might matter. The feature is there, might as well make use of it.
    – Mark Storey-Smith
    Dec 18, 2011 at 3:13
  • Great explanation, and great answer. +1 for both your answer and your comment.
    – Thomas Stringer
    Dec 18, 2011 at 3:37
  • 2
    @Shark - filtered indexes can be a lot faster as well. If the engine recognizes that the filter matches the filter in a query, you skip an evaluation.
    – JNK
    Dec 18, 2011 at 13:34
  • @JNK I see your point. Instead of having to evaluate non-inclusive records, the filtered index already has done the grunt work. Great information, JNK, thank you!!
    – Thomas Stringer
    Dec 18, 2011 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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