SQL Server Performance Forum – Threads Archive

Bookmark lookup – Bit column

Hello! We have a table with a bitcolumn (verifiedProfile). We want get the ID of the first unverified user of the table. It can look like this: SELECT TOP 1
users.id
FROM
users WITH (nolock)
WHERE
verifiedProfile = 0
AND registerDate >= ‘2005-01-01’
ORDER BY
registerDate The problem is that I cannot create an index on all three needed columns (id, registerDate, verifiedProfile), since verifiedProfile is a bit column. Because of that the SQL-Engine needs to do a bookmark lookup, and for this question that is quite heavy. Any suggestions?

Why do you think, you cannot create an index on a BIT column?
create table nottrue
( id int identity primary key
, registerdate smalldatetime
, verifiedprofile bit)
create unique nonclustered index ix_nottrue on nottrue(id, registerdate, verifiedprofile)
go
exec sp_help ‘nottrue’
drop table nottrue —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Yep, that is what I did. I thought a scan would give less performance than an seek, but then again. The bookmark lookup is bad in this case. Thank you!

Better index for that query would be on verifiedProfile and registerDate if you already have clustered index/pk on ID. If not then verifiedProfile, RegisterDate and ID with ID beeing last index column.

Would you really put the BIT column at first position? AFAIK, are statistics only stored for the first column of a composite index. And statistics on such a column shouldn’t be meaningfull at all. Wouldn’t it be better to index registerDate, verifiedProfile instead? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

I had in mind forcing index to be used. Also I was not sure, so I just said that ID should be last column in the index, but didn’t want to say that first two columns have to appear in index in that order. I think query should be tested with both indexes and then decided. * If query optimizer can be forced to use range scan on index starting with bit column it should be the fastest solution. * Statistics on bit column may be meaningfull for this query if verifiedProfile = 0 is true for < 20% rows and date range in where clause is not too selective (keep in mind that we are talking about covering index here). * To summarize I’m not sure but there are valid arguments that either index may be optimal.

Thanks for explanation. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I would also think that playing around with possible indexes here might be worth considering. There aren’t so many possible permutations here anyway. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>