You can't index a bit field in SQL Server 2000, as was indicated in the Books Online at the time:
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot
have indexes on them.
Yes, if you have only a handful of rows, out of millions, an index will help. But if you want to do it in this case you need to make the column a tinyint
.
Note: Enterprise Manager will not let you create an index on a bit column. If you wish you can still manually create an index on a bit column:
CREATE INDEX IX_Users_IsActiveUsername ON Users
(
IsActive,
Username
)
But SQL Server 2000 will not actually use such an index - running a query where the index would be a perfect candidate, e.g.:
SELECT TOP 1 Username
FROM Users
WHERE IsActive = 0
SQL Server 2000 will do a table scan instead, acting as though the index doesn't even exist. If you change the column to a tinyint SQL Server 2000 will do an index seek. Also, the following non-covered query:
SELECT TOP 1 *
FROM Users
WHERE IsActive = 0
It will perform an index seek, followed by a bookmark lookup.
SQL Server 2005 does have limited support for indexes on bit columns. For example:
SELECT TOP 1 Username
FROM Users
WHERE IsActive = 0
will cause an index seek through the covering index. But the non-covered case:
SELECT TOP 1 *
FROM Users
WHERE IsActive = 0
will not cause an index seek followed by a bookmark lookup, it will perform a table scan (or clustered index scan), rather than performing the index seek followed by a bookmark lookup.
Verified by experimentation and direct observation.