> I read on Google Groups that you can create an index on a BIT column in
> SQL Server 2000.

> I don't have the option to create a BIT column in Enterprise Manager...
>
> Do I have to create BIT column indexs through Query Analyzer?

> SQL Server will not use the index, even if there are just a handful of
> rows with bitcol = 1. (With a 50/50 split, the index would be useless
> anyway.) However, this works:
>
> SELECT * FROM tbl WHERE bitcol = convert(bit, 1)
>
> This is because in the first example there is an implicit conversion
> going in the wrong direction for the index to be available.

"Steve Kass" <skass@drew.edu> wrote in message
news:Oev1BDTmEHA.3872@TK2MSFTNGP09.phx.gbl...
> Erland,
>
> While your suggestion is best, since it leaves less up to the
> processor, another option is to write
>
> WHERE bitcol = 0x01 -- where the literal is any nonzero value
> or
> WHERE bitcol = 0x
>
> depending on which value you seek. Binary has the lowest precedence of
> any data type, so the column will not be converted, and the index will
> be available.
>
> Steve Kass
> Drew University
>
> Erland Sommarskog wrote:
>
> >Lucas Tam (REMOVEnntp@rogers.com) writes:
> >
> >
> >>I read on Google Groups that you can create an index on a BIT column in
> >>SQL Server 2000.
> >>
> >>
> >
> >That's correct.
> >
> >
> >
> >>I don't have the option to create a BIT column in Enterprise Manager...
> >>
> >>Do I have to create BIT column indexs through Query Analyzer?
> >>
> >>
> >
> >I have no idea what EM is able to or not. I only use Query Analyzer for
> >such tasks. And, as an advice, it pays off in the long run.
> >
> >Anyway, to add to David's and Adam's posts, there is a gotcha with
> >index on bit columns. If you say:
> >
> > SELECT * FROM tbl WHERE bitcol = 1
> >
> >SQL Server will not use the index, even if there are just a handful of
> >rows with bitcol = 1. (With a 50/50 split, the index would be useless
> >anyway.) However, this works:
> >
> > SELECT * FROM tbl WHERE bitcol = convert(bit, 1)
> >
> >This is because in the first example there is an implicit conversion
going
> >in the wrong direction for the index to be available.
> >
> >
> >
> >
> >
> >

On Mon, 13 Sep 2004 08:12:37 +0200, Uri Dimant wrote:

>Hi,Steve
>I did this test on my workstation and it is still using table scan in both
>cases or I don't understand something
>select top 20000 o.orderid, cast (1 as bit)as d_bit into table_test from
>orders o
>cross join orders
>go
>select * from table_test
>go
>set rowcount 10000
>update table_test set d_bit=0
>set rowcount 0
>go
>create index dd on table_test (d_bit)
>go
>sp_helpindex table_test
>go
>DBCC DROPCLEANBUFFERS
>go
>select * from table_test where d_bit=convert(bit,1)
>go
>drop table table_test

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:99pak0lvotg4on74c7sdgj4p7qntnobu74@4ax.com...
> On Mon, 13 Sep 2004 08:12:37 +0200, Uri Dimant wrote:
>
> >Hi,Steve
> >I did this test on my workstation and it is still using table scan in
both
> >cases or I don't understand something
> >select top 20000 o.orderid, cast (1 as bit)as d_bit into table_test
from
> >orders o
> >cross join orders
> >go
> >select * from table_test
> >go
> >set rowcount 10000
> >update table_test set d_bit=0
> >set rowcount 0
> >go
> >create index dd on table_test (d_bit)
> >go
> >sp_helpindex table_test
> >go
> >DBCC DROPCLEANBUFFERS
> >go
> >select * from table_test where d_bit=convert(bit,1)
> >go
> >drop table table_test
>
> Hi Uri,
>
> That's because the index statistics indicate (correctly) that
> approximately half the rows in table_test would match - a table scan makes
> sense in this case.
>
> If you change the set rowcount 10000 to set rowcount 19990 (so that only
> ten rows match the where clause), you'll see SQL Server switch to an index
> seek + bookmark lookup.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

> I didn't realize I needed to convert the cast the bit column explictly.
>
> When you said there is an implicit conversation... what can the SQL
> processor convert to besides 1 (or a non-zero value)???