Index on bit type column

Can we create an index in SQL Server 7.0 on Bit data type
column.

>--Original Message--
>Hi,
>Yes, You can create index on a bit data type column.
>Thanks
>Hari
>MCDBA
>"Lalit" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
>
>.
>Hi Lalit,
As Tiber mentioned earlier. No, we cant create an index on bit data type
column in SQL 7 or earlier versions.
Only from sql 2000 we can create index on bit column.
Thanks
Hari
MCDBA
"Lalit" <anonymous@.discussions.microsoft.com> wrote in message
news:4d4e01c42c46$95e0b140$a101280a@.phx.gbl...[vbcol=seagreen]
> Can we create an index in SQL Server 7.0 on Bit data type
> column.
>
> message

Index on bit type column

can we create an index on a column of Bit Data type?Hi,
Yes, You can create index on a bit data type column.
Thanks
Hari
MCDBA
"Lalit" <anonymous@.discussions.microsoft.com> wrote in message
news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
> can we create an index on a column of Bit Data type?|||... in SQL2K, not in prior versions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:%23%23JvhBELEHA.3292@.TK2MSFTNGP11.p
hx.gbl...
> Hi,
> Yes, You can create index on a bit data type column.
> Thanks
> Hari
> MCDBA
> "Lalit" <anonymous@.discussions.microsoft.com> wrote in message
> news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
>|||Thanks both of you for the quick reply

>--Original Message--
>... in SQL2K, not in prior versions.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:%
23%23JvhBELEHA.3292@.TK2MSFTNGP11.phx.gbl...
message[vbcol=seagreen]
>
>.
>

Index on bit type column

Can we create an index in SQL Server 7.0 on Bit data type
column.

>--Original Message--
>Hi,
>Yes, You can create index on a bit data type column.
>Thanks
>Hari
>MCDBA
>"Lalit" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
>
>.
>
Hi Lalit,
As Tiber mentioned earlier. No, we cant create an index on bit data type
column in SQL 7 or earlier versions.
Only from sql 2000 we can create index on bit column.
Thanks
Hari
MCDBA
"Lalit" <anonymous@.discussions.microsoft.com> wrote in message
news:4d4e01c42c46$95e0b140$a101280a@.phx.gbl...[vbcol=seagreen]
> Can we create an index in SQL Server 7.0 on Bit data type
> column.
> message

Index on bit type column

can we create an index on a column of Bit Data type?
Hi,
Yes, You can create index on a bit data type column.
Thanks
Hari
MCDBA
"Lalit" <anonymous@.discussions.microsoft.com> wrote in message
news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
> can we create an index on a column of Bit Data type?
|||... in SQL2K, not in prior versions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:%23%23JvhBELEHA.3292@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Yes, You can create index on a bit data type column.
> Thanks
> Hari
> MCDBA
> "Lalit" <anonymous@.discussions.microsoft.com> wrote in message
> news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
>
|||Thanks both of you for the quick reply

>--Original Message--
>... in SQL2K, not in prior versions.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:%
23%23JvhBELEHA.3292@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
message
>
>.
>

Index on bit type column

can we create an index on a column of Bit Data type?Hi,
Yes, You can create index on a bit data type column.
Thanks
Hari
MCDBA
"Lalit" <anonymous@.discussions.microsoft.com> wrote in message
news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
> can we create an index on a column of Bit Data type?|||... in SQL2K, not in prior versions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:%23%23JvhBELEHA.3292@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Yes, You can create index on a bit data type column.
> Thanks
> Hari
> MCDBA
> "Lalit" <anonymous@.discussions.microsoft.com> wrote in message
> news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
> > can we create an index on a column of Bit Data type?
>|||Can we create an index in SQL Server 7.0 on Bit data type
column.
>--Original Message--
>Hi,
>Yes, You can create index on a bit data type column.
>Thanks
>Hari
>MCDBA
>"Lalit" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
>> can we create an index on a column of Bit Data type?
>
>.
>|||Thanks both of you for the quick reply
>--Original Message--
>... in SQL2K, not in prior versions.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:%
23%23JvhBELEHA.3292@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> Yes, You can create index on a bit data type column.
>> Thanks
>> Hari
>> MCDBA
>> "Lalit" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
>> > can we create an index on a column of Bit Data type?
>>
>
>.
>|||Hi Lalit,
As Tiber mentioned earlier. No, we cant create an index on bit data type
column in SQL 7 or earlier versions.
Only from sql 2000 we can create index on bit column.
Thanks
Hari
MCDBA
"Lalit" <anonymous@.discussions.microsoft.com> wrote in message
news:4d4e01c42c46$95e0b140$a101280a@.phx.gbl...
> Can we create an index in SQL Server 7.0 on Bit data type
> column.
> >--Original Message--
> >Hi,
> >
> >Yes, You can create index on a bit data type column.
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >"Lalit" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:4ada01c42c3f$8acb0820$a401280a@.phx.gbl...
> >> can we create an index on a column of Bit Data type?
> >
> >
> >.
> >

Index on bit fields in SQL Server Management Studio

I noticed that I can put a bit field in an index in a SQL Server 2000 database, while using the SQL Server Management Studio (shipped with SQL Server 2005). In SQL Enterprise Manager this wasn't possible.

Does SQL Server 2000 support indexes on bit fields and doesn't Enterprise Manager support it, or doesn't SQL Server 2000 support indexes on bit fields and is it a 'bug' of the SQL Server Management Studio?

Thanks.
an index on a bit field gives absolutely no benefit due to the way that sql server handles bit fields.|||My experience tells me that this is not true. When the SQL Server only has to access an index instead of the real table, this can increase performance. In the past we've converted Bit fields to TinyInt, so they could be added to an index (while the only values where 0 and 1). This sometimes resulted in queries that executed more than 10 times as fast as without that field in the index.
|||

but thats not a bit field. . . that is a tinyint.

read as to how bit fields are managed. if you have one bit field it might help. . . but if you have more than one in a table it won't.

I contend, a need for an index on a bit field 'smells' of an unnormalized schema (not in all cases, but 99%)

|||Maybe your right about the unnormalized schema, but creating an extra table for each bit field just for the sake of normalizing makes reading the schema a lot more complicated.

I've been searching, but can't find any information on managing bit fields in SQL Server. Have you got a reference for me were I can find more information?

Thanks for your help.
|||

http://msdn2.microsoft.com/en-us/library/ms177603(SQL.90).aspx

see how bit fields are groupd together as bytes. . . bit fields 1 - 8 are in one byte, bit fields 9 - 16 are in another. . . . and so on? It was the same in SQL 2000.

as far as the 'smell', and I don't mean that as disrespectful, often when there is a bit field there is some other piece of data that can be used or should be tracked.

for example, instead of an 'IsSubscribed' bit field, have a nullable field SubscriptionDate, then selecting 'IsSubscribed' = -1 equates to

select p.id from person where not SubscriptionDate is null

In this case SubscriptionDate contains much more information.

And often times you need to track Subscription information and that should be in another table. Then selecting IsSubscribed = -1 transforms to:

select p.id from Person p inner join Subscription s on p.id = s.personId

|||

Well you actually can create indexes on bit fields in SQL Server 2000, but you can't do it through the Design Table Interface. You could either use T-SQL to do it or if you want to do it Visually, then you could also right-click on the table choose All Tasks->Manage Indexes and you can select bit fields here to create the index.

Now Microsoft discloses the storage implementation of bit field data types, but that doesn't mean that they don't store bit field data types differently for Indexes. Maybe if a bit field is Indexed they store it in the B-Trees in their own byte field instead of in the concatenated fashion that they store the data itself. Doubtful, but possible. Your best bet would be to use Query Analyzer with Show Execution Plan enabled and look at the difference between querying with a Indexed Bit field and without it. If it works I would imagine there are situations where it could be helpful. There are definitely appropriate times when you can/should utilize bit fields (e.g Male/Female), etc...

If you had a very large database and a bit field and you wanted to search your data then an index on that field would work. The question is "Does Microsoft do some unpublished Magic" to take advantage of it, as in store the Bit Field Data differently for Indexes as opposed to data, but the best way to test it would be to setup an appropriate scenario and evaluate the Execution Plan and Performance.

Sam

index on bit fields

Hello everyone, I'm new with SQLServer (ver. 7.0) I'm desingning some table for a VB app I'm developing. I've noticed that I can't set up bit fields (some useful flags) into the definition of indexes. I'm I right or I turned some wrong way on
I'm using Enterprise manager to design tables
Thanks in advance, Giovanni.Hi Giovanni,
You can't create indexes on bit columns in SQL Server 7, although you can do
it in SQL Server 2000.
--
Jacco Schalkwijk
SQL Server MVP
"giovanni" <anonymous@.discussions.microsoft.com> wrote in message
news:5090C463-4AAE-49B3-A4DD-E4A2AB2BAF60@.microsoft.com...
> Hello everyone, I'm new with SQLServer (ver. 7.0) I'm desingning some
table for a VB app I'm developing. I've noticed that I can't set up bit
fields (some useful flags) into the definition of indexes. I'm I right or I
turned some wrong way on?
> I'm using Enterprise manager to design tables.
> Thanks in advance, Giovanni.|||hi giovanni,
you are right. you can not create index on BIT datatype in SQL Server 7.0 This is supported in
SQL 2000. If possible change datatype to TINYINT.
--
Vishal Parkar
vgparkar@.yahoo.co.in|||Hi Vishal, I can change but do You think I'll have to cast datas on my VB App to convert it into a Boolean type
thanks giovann
-- Vishal Parkar wrote: --
hi giovanni
you are right. you can not create index on BIT datatype in SQL Server 7.0 This is supported i
SQL 2000. If possible change datatype to TINYINT
--
Vishal Parka
vgparkar@.yahoo.co.i|||Hi Giovanni,
Keep this article for your reference
SQL Server 7.0 Datatypes By Sergey Vartanyan
http://databasejournal.com/features/mssql/article.php/14423
61
Regards
Thirumal Reddy M
Sys Admin
www.sstil.com
>--Original Message--
>hi giovanni,
>you are right. you can not create index on BIT datatype
in SQL Server 7.0 This is supported in
>SQL 2000. If possible change datatype to TINYINT.
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in
>
>.
>|||hi giovanni,
Im not sure how do you handle this data at application level but i dont think you should face
any problem with it, because BIT datatype also belongs to INTEGER family
--
Vishal Parkar
vgparkar@.yahoo.co.in|||an index on a bit column (Or tinyInt Column with very few different values)
will be basically worthless.
Probably not a good candidate for an index.
Greg Jackson
PDX, Oregon

index on bit fields

Hello everyone, I'm new with SQLServer (ver. 7.0) I'm desingning some table
for a VB app I'm developing. I've noticed that I can't set up bit fields (so
me useful flags) into the definition of indexes. I'm I right or I turned som
e wrong way on?
I'm using Enterprise manager to design tables.
Thanks in advance, Giovanni.Hi Giovanni,
You can't create indexes on bit columns in SQL Server 7, although you can do
it in SQL Server 2000.
Jacco Schalkwijk
SQL Server MVP
"giovanni" <anonymous@.discussions.microsoft.com> wrote in message
news:5090C463-4AAE-49B3-A4DD-E4A2AB2BAF60@.microsoft.com...
> Hello everyone, I'm new with SQLServer (ver. 7.0) I'm desingning some
table for a VB app I'm developing. I've noticed that I can't set up bit
fields (some useful flags) into the definition of indexes. I'm I right or I
turned some wrong way on?
> I'm using Enterprise manager to design tables.
> Thanks in advance, Giovanni.|||hi giovanni,
you are right. you can not create index on BIT datatype in SQL Server 7.0 Th
is is supported in
SQL 2000. If possible change datatype to TINYINT.
Vishal Parkar
vgparkar@.yahoo.co.in|||Hi Vishal, I can change but do You think I'll have to cast datas on my VB Ap
p to convert it into a Boolean type?
thanks giovanni
-- Vishal Parkar wrote: --
hi giovanni,
you are right. you can not create index on BIT datatype in SQL Server 7.0 Th
is is supported in
SQL 2000. If possible change datatype to TINYINT.
Vishal Parkar
vgparkar@.yahoo.co.in|||Hi Giovanni,
Keep this article for your reference
SQL Server 7.0 Datatypes By Sergey Vartanyan
http://databasejournal.com/features...ticle.php/14423
61
Regards
Thirumal Reddy M
Sys Admin
www.sstil.com
>--Original Message--
>hi giovanni,
>you are right. you can not create index on BIT datatype
in SQL Server 7.0 This is supported in
>SQL 2000. If possible change datatype to TINYINT.
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in
>
>.
>|||hi giovanni,
Im not sure how do you handle this data at application level but i dont thin
k you should face
any problem with it, because BIT datatype also belongs to INTEGER family
Vishal Parkar
vgparkar@.yahoo.co.in|||an index on a bit column (Or tinyInt Column with very few different values)
will be basically worthless.
Probably not a good candidate for an index.
Greg Jackson
PDX, Oregon

Index on bit column

Hi,

MSSql2000: According to docs:
"Columns of type bit cannot have indexes on them"

Its impossible to define index on bit column using EM but create index command in QA is working and the index is created.
I understand why not to create index like this but its valid or invalid operation to create index on BIT column ?

ThanksWhile it does not make sense to create an index on a bit column, it is a valid operation. EM filters out the bit columns when it shows you the columns available to create indexes.

Bible also says:
(Under the topic "Create Index")
Columns consisting of the ntext, text, or image data types cannot be specified as columns for an index.

Not that Bit is not mentioned in this list.|||Hi sbaru, thanks for the reply.

I would like to know why EM filters out the bit columns for index creation.

I found the limitation about bit as index field under the topic "bit data type, described"

Index a bit field

Is there any way to index a bit field in a composite index?Yes, that is, if you are using SQL-Server 2000. Older versions do not
support indexing bit columns.
Gert-Jan
Mark DeWaard wrote:
> Is there any way to index a bit field in a composite index?|||I was able to successfully create an index on a bit field using query
analyzer.
What confused me was that bit fields don't show up in Enterprise manager's
drop down field for indexes. Also many websites still say that bit fields
cannot be indexed
Thank you for your response.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F020E10.B19B4B4E@.toomuchspamalready.nl...
> Yes, that is, if you are using SQL-Server 2000. Older versions do not
> support indexing bit columns.
> Gert-Jan
>
> Mark DeWaard wrote:
> >
> > Is there any way to index a bit field in a composite index?