Sql-server – How are bit fields sorted for an index

indexsql serversql-server-2008

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.

Best Answer

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).

Related Question