March 31, 2009 at 5:38 pm
A vendor has requested that I change all BIT fields to TINYINT because, as they say, the MSSQL Query Optimizer does not include BIT fields in its optimizations. Has anyone ever heard of this, or can you give me an idea of how I can irrefutably prove them wrong?
I supposed I could just create a table with bit fields, add a proc and get a snapshot of the query plan, but I'd be interested in hearing the thoughts of others on this.
Thanks!
March 31, 2009 at 7:18 pm
I don't have any categoric proof that it does or doesn't, but it's what I would expect for a non-indexed field that can only have two values (plus null of course). If it is converted to a tinyint but still only has two possible values then the optimiser will continue to ignore it because it won't add appreciably to the selectivity.
The question piqued my interest though (we've had similar definitive statements made by vendors, which have turned out to be incorrect). A quick test I just did shows what I expected. I created two three-field tables:
create table #Table1
(Int1 int identity(1,1) primary key clustered,
Bit1 bit,
Char1 char(1)
)
create table #Table2
(Int2 int identity(1,1) primary key clustered,
Tiny2 tinyint,
Char2 char(1)
)
go
insert into #Table1 (Bit1, Char1)
select top 1000000 CAST(a.object_id % 2 as bit), char(a.object_id % 26 + 64)
from sys.columns a, sys.columns b
go
insert into #Table2 (Tiny2, Char2)
select top 1000000 CAST(a.object_id % 2 as tinyint), char(a.object_id % 26 + 64)
from sys.columns a, sys.columns b
go
create nonclustered index T1 on #Table1 (Bit1)
create nonclustered index T2 on #Table2 (Tiny2)
go
I then ran the following against both tables:
select Char1 from #Table1 where Bit1 = 0
select Char2 from #Table2 where Tiny2 = 0
In both cases it did a clustered index scan and ignored the non-clustered index.
Where your vendor is getting confused is if the full range of values for a tinyint field is possible. Then it might choose to use an index on that field because the selectivity would be sufficient to warrant it (the total size of the table would also matter: when I tried it on the toy table above it still did a table scan because the table only consists of a few pages even though it's got a million rows).
April 1, 2009 at 1:16 am
ABurton (3/31/2009)
A vendor has requested that I change all BIT fields to TINYINT because, as they say, the MSSQL Query Optimizer does not include BIT fields in its optimizations.
They're wrong.
It's a belief I've seen around before and (I believe) it comes from SQL 2000 because Enterprise manager wouldn't allow an index creation on a bit column. It wasn't a SQL limitation, it was Enterprise manager.
As for proof, easy. (using Glenn's example with two small changes)
create table #Table1
(Int1 int identity(1,1) primary key clustered,
Bit1 bit,
Char1 char(1)
)
insert into #Table1 (Bit1, Char1)
select top 1000000 0, char(a.object_id % 26 + 64)
from sys.columns a, sys.columns b
go
Update TOP (5) #Table1
SET Bit1 = 1
GO
create nonclustered index T1 on #Table1 (Bit1)
SELECT * from #Table1
WHERE Bit1 = 1
The reason bits aren't good candidates for indexes generally is that they're not selective enough for SQL to pick a seek/key lookup over a clustered index scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2009 at 2:35 am
Not necessarily. With SQL 2008 comes the ability of Filtered Indexes.
You can create an index on Bit1 = 1 only.
N 56°04'39.16"
E 12°55'05.25"
April 1, 2009 at 8:15 am
Peso (4/1/2009)
Not necessarily. With SQL 2008 comes the ability of Filtered Indexes.You can create an index on Bit1 = 1 only.
Sure you can, but that doesn't change the selectivity rules. If 10% of the table has the bit column with a value of 1, there's a non-covering index on only the bit column and the query filters WHERE bit1 = 1, the index won't be used. It's too high a percentage of the table to do the bookmark lookups on, so SQL will rather pick a clustered index scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply