| Author |
Topic |
|
pwcphoto
Yak Posting Veteran
69 Posts |
 Posted - 2006-08-24 : 22:07:25
|
| 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)
7020 Posts |
Posted - 2006-08-24 : 22:34:21
|
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 |
 |
|
|
pwcphoto
Yak Posting Veteran
69 Posts |
Posted - 2006-08-24 : 23:05:12
|
| 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... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-24 : 23:10:38
|
| Bit columns are usually poor candidates to include in an index, but there are some situations where it can be useful.CODO ERGO SUM |
|
|
|
pwcphoto
Yak Posting Veteran
69 Posts |
Posted - 2006-08-24 : 23:55:20
|
| 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... |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-08-25 : 13:55:57
|
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):AndersonBakerJonesSmithWilson This data (again hundreds or thousands of rows) doesn't gain any benefit:000001111 Ken |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-08-25 : 14:08:31
|
| 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. |
|
|
|
jwnova
Starting Member
1 Post |
Posted - 2006-09-10 : 17:08:07
|
| 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. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-09-10 : 18:36:24
|
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!DavidMProduction is just another testing cycle |
|
|
|
|
Bit vs Tinyint