August 6, 2002 at 9:26 am
We have a table with 100,000 records in it. A few (say 10) of the records will be modified per day for sending to another system.
I want to run a query like:
select records from table where prdchanged (bit field) = 1 where ideally the field would be a bit field.
I want to know that the fact there are 99990 records with bit = 0 and 10 records with bit = 1 makes indexing worthwhile, or any other suggestions to speed the query. eg using a different field type. I had read that indexing was only useful with a good spread of data. The clustered index is already used on better queries.
Does anybody have any experience of this or suggestions ?
For various reasons I dont want to use triggers/other tables.
August 6, 2002 at 10:18 am
One fact that will affect your approach is that bit fields cannot be indexed. See BOL, bit data type, for more information.
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
August 6, 2002 at 12:33 pm
Use tinyint or a datetime instead. Easier to track.
Steve Jones
August 6, 2002 at 1:37 pm
Steve A,
Not sure that is right, dont have my Inside SQL book here. You can index it, as far as SQL allowing you to do it. Whether it uses the index is the next question. I have a small table I working with right now, added a bit column and populated 3 rows with 1, the rest were null - 578 rows total. The query returned the three rows with the query plan showing it did an index scan on my index. So possibly it has to do with the density? Not trying to start an argument, just curious!
Andy
August 6, 2002 at 1:59 pm
If I understand the problem correctly, you could consider an alternative approach: Use a trigger to populate another table for each row that needs to be shipped.
I've actually seen this approach used successfully at other shops, particularly at one Oracle shop I used to work in. If you're not already aware, be advised that triggers present their own set of problems.
August 6, 2002 at 2:01 pm
That's definitely curious. From BOL, bit data type described:
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
Microsoft® SQL Server™ optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.
So creating an index on a bit column doesn't fail? I've never tried because of the remarks above. Perhaps the documentation is wrong - does anyone else have experience with indexing on bit columns?
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
August 7, 2002 at 2:38 am
I went for a tinyint in the end, and have learnt a lot more about bit fields, thanks.
I had indexed a couple of bit fields up to now as I had not thought much of the design issues raised here and did not know that BOL says they cannot be indexed (or maybe not used in the search if indexed) - maybe ignorance is bliss sometimes !
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply