Bit field index

I have a table with 5 bit fields on the end. Do you can any performace by
indxing a bit field, even though its not used in any caculations or where
clauses?
--
JP
.NET Software DeveloperWhy do you think an index would be useful?
"JP" <JP@.discussions.microsoft.com> wrote in message
news:456DE2B8-EFAD-42FE-8FA8-0C4CB336BDF5@.microsoft.com...
>I have a table with 5 bit fields on the end. Do you can any performace by
> indxing a bit field, even though its not used in any caculations or where
> clauses?
> --
> JP
> .NET Software Developer|||Well I miss typed my message. The bit fild is used in the where of a delete
statement but was thinking since its either 0 or 1 I would think a index o
n
a bit wouldnt do any good in terms of SP performace. Since Im dealing with
over 40 million records, I wanted to make sure though as I dont want to make
changes to this table unless I have to.
--
JP
.NET Software Developer
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why do you think an index would be useful?
>
> "JP" <JP@.discussions.microsoft.com> wrote in message
> news:456DE2B8-EFAD-42FE-8FA8-0C4CB336BDF5@.microsoft.com...
>
>|||> Well I miss typed my message. The bit fild is used in the where of a
> delete
> statement but was thinking since its either 0 or 1 I would think a index
> on
> a bit wouldnt do any good in terms of SP performace.
You're right. Unless your table is 99% one value or the other, it is very
unlikely to use the index.
http://www.aspfaq.com/2530
A|||In fact query optimizer might decide on doing a table scan anyway.
ML
http://milambda.blogspot.com/|||JP (JP@.discussions.microsoft.com) writes:
> I have a table with 5 bit fields on the end. Do you can any performace by
> indxing a bit field, even though its not used in any caculations or where
> clauses?
Indexing bit columns is meaningful if you have a column like isprocessed,
and only new rows has this value. For bit columns with 50/50 split, an
index is only useful if you need to do:
SELECT bitcol, COUNT(*) FROM tbl GROUP BY bitcol
There is a gotcha with bit columns. Say that you have an index on
isprocessed:
SELECT ... FROM tbl WHERE isprocessed = 0
This query will use the index (assuming that only a few rows has this
value), but it will scan the index, not s it. This is because 0 is
integer, and integer is higher in SQL Server data-type precedence. This
query will be perform a s and be real fast:
SELECT ... FROM tbl WHERE isprocessed = convert(bit, 0)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx