SQL server interview query on index?

Question

I got one interview question from NET but didn't find the proper solution can anyone tell me what's wrong with this query?

Given this table structure and index, what is the problem with the subsequent query?

CREATE TABLE dbo.IndexQ (
ID int IDENTITY(1, 1) NOT NULL,
TestBit bit NOT NULL
)
GO

CREATE NONCLUSTERED INEX IX_IndexQ_TestBit ON dbo.IndexQ (TestBit)
GO

* Insert some rows where some bits are 0 and some are 1...

SELECT *
FROM dbo.IndexQ
WHERE TestBit = 1
* What's the problem with this query?

Answer 1

I suppose the problem in your NONCLUSTERED index on bit field. If you have a large table, you should not build index on bit field, because then more records it has, them less effective you index will work, but more date you will use on it.

See https://dba.stackexchange.com/questions/12888/should-i-index-a-bit-field-in-sql-server