Bits of Indexes RFC

Seppuku · #1 (**permalink**) 06-15-04, 14:45

I suppose this is more an RFC than a true question, but I've come to realize "bit" data types do not optimize a query when they are used in a WHERE clause. Since they cannot be indexed, it forces the analyzer to query an available index for rows based upon the non-bit constraints. The result is then scanned to match the values of the bit constraints of the query. So you're potentially doing a full table scan, or an index scan.

My original thought was that I'd improve performance by using bit fields since they are a smaller datatype, but as development progressed, we began using those bits in our queries. At this point, it may be beneficial to convert those bit types to smallint.

Am I wrong in my conclusion?

Brett Kaiser · #2 (**permalink**) 06-15-04, 14:50

Nope....

Code:

USE Northwind
GO

CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
GO

CREATE INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
CREATE INDEX myTable99_IX3 ON myTable99(Col3)
GO

--[CTRL]+k

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

DROP TABLE myTable99
GO

grahamt · #3 (**permalink**) 06-15-04, 15:22

WOW! Learn something new every day! Now I've got to go and start changing all my bit fields to something else. The execution plan in QA showed the bit field about three times slower for a simple select!

Thanks Brett... Keep the info coming..

rdjabarov · #4 (**permalink**) 06-15-04, 15:34

...It also produced table scan. I noticed though that it does not behave the same way if bit field is part of a composit index, even if it is the first field in the index field list.

Brett Kaiser · #5 (**permalink**) 06-15-04, 15:44

I get Index Scans....I have no idea why it would decide to use the new indexes and do the scan...could be because there's no data..

Hell the last 2 indexes are the same (if not larger) in....

But why would a 3 selects use each new index as I add them?

Code:

USE Northwind
GO

CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
GO

CREATE INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
CREATE INDEX myTable99_IX3 ON myTable99(Col3)
GO
--[CTRL]+k

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

CREATE INDEX myTable99_IX4 ON myTable99(Col2,Col1,Col3)
GO

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

CREATE INDEX myTable99_IX5 ON myTable99(Col1,Col3,Col2)
GO

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

DROP TABLE myTable99
GO

Seppuku · #6 (**permalink**) 06-15-04, 16:57

Right.. I suppose then that bits should be relegated to read/write only.. not query constraints...

So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.

In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)

Brett Kaiser · #7 (**permalink**) 06-16-04, 08:49

Quote:

Originally Posted by Seppuku

So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.

It's probably due to the cardinality...bit is 0 or 1...anything with a low number of different values...will not make a good index...it will always cause a scan..

Quote:

In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)

In a table..no...in an index, it's imperative....at least that's what I've always believed...

For example...an index with Col3, Col2, Col1

And a predeicate of Col1 = something

Is a table scan....ever read up on index intersection?

Still don't know why my example (besides probably be a bad one) shows the index scan using IX4 and IX5....got a very nice seek using IX1 thru IX3

Wonder why the optimizer chose them...

I'm (as usual, so it's not disconcerting) perplexed....

Internet Services

Web Development

Digital Marketing

Consumer Tech