Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Bit vs Tinyint
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pwcphoto
Yak Posting Veteran

USA
69 Posts

Posted - 08/24/2006 :  22:07:25  Show Profile  Visit pwcphoto's Homepage  Send pwcphoto a Yahoo! Message  Reply with Quote
Is there a speed advantage using the tinyint field to hold a status bit vs using a bit field? The tinyint field can be included in an index where a bit field cannot, does this buy me any speed or just complicate the indexes? I used the bit field to save space, but performance is more critical then space so I will go back to tinyint if that is best. Any help would be appreciated.

Thanks,

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/24/2006 :  22:34:21  Show Profile  Reply with Quote
A bit column can be used in an index. Try it:

create table demo (demo_bit bit not null)

create index demo_bit_index on demo (demo_bit)


If a bit can hold the information, use a bit.



CODO ERGO SUM
Go to Top of Page

pwcphoto
Yak Posting Veteran

USA
69 Posts

Posted - 08/24/2006 :  23:05:12  Show Profile  Visit pwcphoto's Homepage  Send pwcphoto a Yahoo! Message  Reply with Quote
Using the enterprise manager in MSSQL 2000 it does not give you the option to select any of the fields that are type bit, so it wasn't clear if this was allowed. I will try and add one manually using your input via the query analyzer and see what happens.

Thanks,

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/24/2006 :  23:10:38  Show Profile  Reply with Quote
Bit columns are usually poor candidates to include in an index, but there are some situations where it can be useful.




CODO ERGO SUM
Go to Top of Page

pwcphoto
Yak Posting Veteran

USA
69 Posts

Posted - 08/24/2006 :  23:55:20  Show Profile  Visit pwcphoto's Homepage  Send pwcphoto a Yahoo! Message  Reply with Quote
Would it be beneficial to combine the index with the an identity column so that there is a uniqueness to it, or is that just additional overhead and not a good idea?

Basically I have some large tables that have a bit labled Comment_Active which I include as part of a WHERE clause for example: WHERE (Comment_Active = 1) AND ....

Would indexing the Comment_Active bit field help in speeding up the query?

Thanks,

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 08/25/2006 :  13:55:57  Show Profile  Reply with Quote
Phil,

quote:

Would indexing the Comment_Active bit field help in speeding up the query?



Not unless 99.9% of the Comment_Active fields were set to 0, and .1% set to 1.

An index isn't really useful when you have such a small subset of possible values.

quote:

Would it be beneficial to combine the index with the an identity column so that there is a uniqueness to it,



Not really, unless you could also use the identity column as part of the SELECT. Otherwise, there's no real difference.

An index would increase selectivity if the data is spread over a wider range of values. In other words, given the following data an index would help (given a larger set of values, of course - hundred or thousands of rows):


Anderson
Baker
Jones
Smith
Wilson


This data (again hundreds or thousands of rows) doesn't gain any benefit:


0
0
0
0
0
1
1
1
1


Ken
Go to Top of Page

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 08/25/2006 :  14:08:31  Show Profile  Reply with Quote
The only reason why you would really want to index a bit column is if you can put it in a composite index that will cover a query you are doing (which means that the sql engine can retrieve everything out of the index without using a bookmark lookup).

You will need to create your index in query analyzer if you want to do this.
Go to Top of Page

jwnova
Starting Member

1 Posts

Posted - 09/10/2006 :  17:08:07  Show Profile  Reply with Quote
KenW,

I have to say that I've gotten great performance out of indexing a bit field. I had two seperate clients with over 200k records in their ticket trouble shooting system. A lot of searches only search on records where closed = 0. After indexing the closed fields I had bench marked select statements going from 8 seconds to < 1 second.

One client had 23% of their tickets opened (closed = 0) and the other client had 12% of their tickets opened. This is a far cry from 99.9% you mentioned.

I searched the internet high and low and everything had the same thing to say about bit field indexes that you did. I decided to try it, bench mark my results and then roll back if it didn't work. Why would my results differ so greatly? We're using SQL Server 2000 Enterprise edition. I would appreciate your feedback.
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 09/10/2006 :  18:36:24  Show Profile  Reply with Quote
quote:

I searched the internet high and low and everything had the same thing to say about bit field indexes that you did. I decided to try it, bench mark my results and then roll back if it didn't work.

Why would my results differ so greatly?



Because you decided to validate the concept against your situation instead of blindly follow the enormous amount of crap written...

Kudos!

DavidM

Production is just another testing cycle
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000