[t-sql] Can I create an index on a BIT column?

Try this. In Enterprise Manager, right-click the Tables view, and select New Table. Create a BIT column named "MyBitColumn", and then click on the "Manage Indexes / Keys..." button to create a new index:


When you do this, you are offered a "New" button. Click it, and under "Column name" try to select the "MyBitColumn" column. It's not there! Why? Because in this interface, Enterprise Manager does not offer you the ability to create an index on a BIT column. When you try to type the name yourself, you are rewarded with an annoying barrage of error dialogs. Each time you click "No" the first dialog reappears, and every time you try to click OK on the second, the same thing happens.


So, can you create an index on a BIT column? Of course. You can even still do it in Enterprise Manager. Save your table, and then right-click it in the Tables view and choose All Tasks > Manage Indexes... give your index a name, check the "MyBitColumn" column, and hit OK. Voila!


Another way is to create the index using T-SQL, which is the preferred way of generating DDL anyway:

    CREATE TABLE dbo.blat
    (
        MyBitColumn BIT
    )
    GO

    CREATE INDEX MyBitIndex ON dbo.blat(MyBitColumn)
    GO

Now, the question is, do you really WANT an index on a BIT column? We're going to run some experiments, but in general, it is highly unlikely that you will get much use out of such an index. The exception is when the data is heavily weighted towards, say, 1 (e.g. 95-99% of the table), and you are searching for 0. Or vice-versa.

What I'd like to test is the effect on execution time and the execution plan if you run different queries against large tables with a BIT column whose values are evenly distributed, or weighted heavily (in this case 97% - 3%), and in both cases, compare clustered to nonclustered to no index.

So, let's create six different tables in their own database:

    CREATE DATABASE Splunge
    GO

    USE Splunge
    GO

    -- 50/50, no index
    CREATE TABLE dbo.Test1
    (
      myBit BIT NOT NULL
    )

    -- 50/50, nonclustered index
    CREATE TABLE dbo.Test2
    (
      myBit BIT NOT NULL
    )

    CREATE INDEX bitIndex ON dbo.Test2(myBit)

    -- 50/50, clustered index
    CREATE TABLE dbo.Test3
    (
      myBit BIT NOT NULL
    )

    CREATE CLUSTERED INDEX bitIndex ON dbo.Test3(myBit)

    -- 97/3, no index
    CREATE TABLE dbo.Test4
    (
      myBit BIT NOT NULL
    )

    -- 97/3, nonclustered index
    CREATE TABLE dbo.Test5
    (
      myBit BIT NOT NULL
    )

    CREATE INDEX bitIndex ON dbo.Test5(myBit)

    -- 97/3, clustered index
    CREATE TABLE dbo.Test6
    (
      myBit BIT NOT NULL
    )

    CREATE CLUSTERED INDEX bitIndex ON dbo.Test6(myBit

And let's populate each with 100,000 rows, the first three will have 50,000 of each value (0,1), and the second three will have 97,000 0's and 3,000 1's.

    DECLARE 
      @i INT, 
      @ff BIT, -- 50/50 flag 
      @nf BIT -- 97/3 flag 
     
    SELECT 
      @i = 1, 
      @ff = 0, 
      @nf = 0 
   
    WHILE @i <= 100000 
    BEGIN 
      IF @i > 50000 
        SET @ff = 1 
     
      IF @i > 97000 
        SET @nf = 1 
   
      INSERT dbo.Test1(myBit) SELECT @ff 
      INSERT dbo.Test2(myBit) SELECT @ff 
      INSERT dbo.Test3(myBit) SELECT @ff 
   
      INSERT dbo.Test4(myBit) SELECT @nf 
      INSERT dbo.Test5(myBit) SELECT @nf 
      INSERT dbo.Test6(myBit) SELECT @nf 
   
      SET @i = @i + 1 
    END

On my system, this took roughly seven minutes. Your mileage may vary.

So now that we have the data in there, let's run the following sets of queries.

    SELECT COUNT(*) FROM dbo.Test1 
    SELECT COUNT(*) FROM dbo.Test2 
    SELECT COUNT(*) FROM dbo.Test3 
    SELECT COUNT(*) FROM dbo.Test4 
    SELECT COUNT(*) FROM dbo.Test5 
    SELECT COUNT(*) FROM dbo.Test6 

    SELECT COUNT(*) FROM dbo.Test1 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test1 WHERE MyBit=1 
    SELECT COUNT(*) FROM dbo.Test2 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test2 WHERE MyBit=1 
    SELECT COUNT(*) FROM dbo.Test3 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test3 WHERE MyBit=1 
    SELECT COUNT(*) FROM dbo.Test4 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test4 WHERE MyBit=1 
    SELECT COUNT(*) FROM dbo.Test5 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test5 WHERE MyBit=1 
    SELECT COUNT(*) FROM dbo.Test6 WHERE MyBit=0 
    SELECT COUNT(*) FROM dbo.Test6 WHERE MyBit=1 

If you observe the execution plan and statistics, you will see that those with the table scan (Test1 and Test4) require the least amount of reads and percentage of work.

However, these results change ever so slightly if you are performing grouping and aggregates in the same query:

    SELECT MyBit, COUNT(*) FROM dbo.Test1 GROUP BY MyBit 
    SELECT MyBit, COUNT(*) FROM dbo.Test2 GROUP BY MyBit 
    SELECT MyBit, COUNT(*) FROM dbo.Test3 GROUP BY MyBit 
    SELECT MyBit, COUNT(*) FROM dbo.Test4 GROUP BY MyBit 
    SELECT MyBit, COUNT(*) FROM dbo.Test5 GROUP BY MyBit 
    SELECT MyBit, COUNT(*) FROM dbo.Test6 GROUP BY MyBit 

Here we see that the clustered index has a slight edge in query cost, but slightly higher I/O cost:

And in SQL Server 2005, the clustered index scan has an even greater advantage:

So, the answer to this one is yes, you can create a clustered index on a BIT column.

However, as for whether you SHOULD—as with so many other choices—it depends.

Jeff Gray correctly points out that the optimizer will do a little bit better if you explicitly tell it that you are dealing with a BIT (since the engine assumes INT), e.g.:

    WHERE MyBit = CONVERT(BIT, 0) 
    --or 
    WHERE MyBit = CAST(0 AS BIT)

Though that can come with a trade-off as well; namely, remembering to explicitly convert values on every statement. Whereas, if you use a CHAR(1) constrained to 'T'/'F', for example, no explicit conversion is necessary for the proper index to be used.

---

Now clean up, because you probably don't want this data to hang around:

    DROP TABLE 
      dbo.Test1, dbo.Test2, dbo.Test3, 
      dbo.Test4, dbo.Test5, dbo.Test6 

Or, just:

    DROP DATABASE Splunge