Tell me more ×
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.
up vote 3 down vote favorite

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.

share|improve this question

1 Answer

up vote 13 down vote accepted

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.

share|improve this answer
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 '11 at 3:02
2  
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 '11 at 3:13
Great explanation, and great answer. +1 for both your answer and your comment. – Thomas Stringer Dec 18 '11 at 3:37
1  
@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 '11 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 '11 at 13:54
show 1 more 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.