前言
我看过不少对
Bit字段能否建立索引，以及
建立索引后性能如何的讨论，还有朋友建议
用Tinyint代替Bit，我在这里深入研究一下：
研究方法：
一、建立六张表，具体说明见SQL语句中的注释部分：
建表Sql语句
CREATE DATABASE IndexTest
GO
USE IndexTest
GO
-- bit无索引，0、1相等
CREATE TABLE dbo.TestBitNoIndex
(
TestID INT NOT NULL,
ColumnBit BIT NOT NULL
)
ALTER TABLE [dbo].[TestBitNoIndex] WITH NOCHECK ADD CONSTRAINT [PK_TestBitNoIndex] PRIMARY KEY CLUSTERED ( [TestID] )
-- bit有非聚集索引，0、1相等
CREATE TABLE dbo.TestBitIndexed
(
TestID INT NOT NULL,
ColumnBit BIT NOT NULL
)
ALTER TABLE [dbo].[TestBitIndexed] WITH NOCHECK ADD CONSTRAINT [PK_TestBitIndexed] PRIMARY KEY CLUSTERED ( [TestID] )
CREATE INDEX IX_TestBitIndexed_ColumnBit ON dbo.TestBitIndexed(ColumnBit)
-- bit有非聚集索引，0、1相差悬殊
CREATE TABLE dbo.TestBitIndexedWideGap
(
TestID INT NOT NULL,
ColumnBit BIT NOT NULL
)
ALTER TABLE [dbo].[TestBitIndexedWideGap] WITH NOCHECK ADD CONSTRAINT [PK_TestBitIndexedWideGap] PRIMARY KEY CLUSTERED ( [TestID] )
CREATE INDEX IX_TestBitIndexedWideGap_ColumnBit ON dbo.TestBitIndexedWideGap(ColumnBit)
-- tinyint无索引，0、1相等
CREATE TABLE dbo.TestTinyintNoIndex
(
TestID INT NOT NULL,
ColumnTinyint TINYINT NOT NULL
)
ALTER TABLE [dbo].[TestTinyintNoIndex] WITH NOCHECK ADD CONSTRAINT [PK_TestTinyintNoIndex] PRIMARY KEY CLUSTERED ( [TestID] )
-- tinyint有非聚集索引，0、1相等
CREATE TABLE dbo.TestTinyintIndexed
(
TestID INT NOT NULL,
ColumnTinyint TINYINT NOT NULL
)
ALTER TABLE [dbo].[TestTinyintIndexed] WITH NOCHECK ADD CONSTRAINT [PK_TestTinyintIndexed] PRIMARY KEY CLUSTERED ( [TestID] )
CREATE INDEX IX_TestTinyintIndexed_ColumnTinyint ON dbo.TestTinyintIndexed(ColumnTinyint)
-- tinyint有非聚集索引，0、1相差悬殊
CREATE TABLE dbo.TestTinyintIndexedWideGap
(
TestID INT NOT NULL,
ColumnTinyint TINYINT NOT NULL
)
ALTER TABLE [dbo].[TestTinyintIndexedWideGap] WITH NOCHECK ADD CONSTRAINT [PK_TestTinyintIndexedWideGap] PRIMARY KEY CLUSTERED ( [TestID] )
CREATE INDEX IX_TestTinyintIndexedWideGap_ColumnTinyint ON dbo.TestTinyintIndexedWideGap(ColumnTinyint)
二、分别填充10万条数据，其中Bit与Tinyint各有一张表的0、1比例悬殊（97:3）
插入测试数据的Sql语句
DECLARE
@i INT,
@SameBitFlag BIT, -- 0、1 50比50
@WideGapBitFlag BIT, -- 0、1 97比3
@SameTinyintFlag BIT, -- 0、1 50比50
@WideGapTinyintFlag BIT -- 0、1 97比3
SELECT
@i = 1,
@SameBitFlag = 0,
@SameTinyintFlag = 0,
@WideGapBitFlag = 0,
@WideGapTinyintFlag = 0
WHILE @i <= 100000
BEGIN
IF @i > 50000
SET @SameBitFlag = 1
SET @SameTinyintFlag = 1
IF @i > 97000
SET @WideGapBitFlag = 1
SET @WideGapTinyintFlag = 1
INSERT INTO [dbo].[TestBitNoIndex](TestID, ColumnBit) VALUES (@i, @SameBitFlag)
INSERT INTO [dbo].[TestBitIndexed](TestID, ColumnBit) VALUES (@i, @SameBitFlag)
INSERT INTO [dbo].[TestTinyintNoIndex](TestID, ColumnTinyint) VALUES (@i, @SameBitFlag)
INSERT INTO [dbo].[TestTinyintIndexed](TestID, ColumnTinyint) VALUES (@i, @SameBitFlag)
INSERT INTO [dbo].[TestBitIndexedWideGap](TestID, ColumnBit) VALUES (@i, @WideGapBitFlag)
INSERT INTO [dbo].[TestTinyintIndexedWideGap](TestID, ColumnTinyint) VALUES (@i, @WideGapTinyintFlag)
SET @i = @i + 1
END
三、分别搜索六张表中ColumnBit = 0 或者 ColumnTinyint = 0的数据，并在执行计划中查看
I/0成本、
CPU成本与
成本
查询SQL语句
--Test1
SELECT TestID FROM [dbo].[TestBitNoIndex] WHERE ColumnBit = 0
--Test2
SELECT TestID FROM [dbo].[TestBitIndexed] WHERE ColumnBit = 0
--Test3
SELECT TestID FROM [dbo].[TestBitIndexedWideGap] WHERE ColumnBit = 0
--Test4
SELECT TestID FROM [dbo].[TestTinyintNoIndex] WHERE ColumnTinyint = 0
--Test5
SELECT TestID FROM [dbo].[TestTinyintIndexed] WHERE ColumnTinyint = 0
--Test6
SELECT TestID FROM [dbo].[TestTinyintIndexedWideGap] WHERE ColumnTinyint = 0
四、比较执行计划中的成本：
|
Test1：Bit无索引，0、1数据量相等
|
Test4：Tinyint无索引，0、1数据量相等
|
|
Test2：Bit有索引，0、1数据量相等
|
Test5：Tinyint有索引，0、1数据量相等
|
|
Test3：Bit有索引，0、1数据量悬殊
|
Test6：Tinyint有索引，0、1数据量悬殊
|
数据收集如下表：
|
|
I/O成本
|
CPU成本
|
成本
|
|
Bit无索引，0、1数据量相等
|
0.164
|
0.109
|
0.274653
|
|
Bit有索引，0、1数据量相等
|
0.110
|
0.109
|
0.220691
|
|
Bit有索引，0、1数据量悬殊
|
0.110
|
0.110
|
0.220990
|
|
Tinyint无索引，0、1数据量相等
|
0.164
|
0.109
|
0.274653
|
|
Tinyint有索引，0、1数据量相等
|
0.0426
|
0.0549
|
0.097554
|
|
Tinyint有索引，0、1数据量悬殊
|
0.0774
|
0.106
|
0.184218
|
数据分析：
1、对于Bit，有索引与无索引、01数据量相等与数据量悬殊的各项成本相差都不大；
2、对于Tinyint，01数据量相等时，有索引比无索引降低I/O成本接近5倍、降低CPU成本近2倍、降低成本近3倍；
3、对于Tinyint，01数据量悬殊时，比数据量相等要增加各项成本近2倍；
4、无索引、数据量相同时，Bit与Tinyint成本完全相同；
5、有索引、数据量相同时，Bit比Tinyint增加成本接近3倍；
6、有索引、数据量悬殊时，Bit比Tinyint增加成本近2倍。
结论：
1、毋庸置疑，Bit可以建立索引；
2、在10万条数据时，Bit是否建立索引成本消耗相差不大；
3、强烈推荐用Tinyint代替Bit，10万条数据时，成本降低近3倍，相信随着数据量的增加，与Bit相比成本降低会更多，有兴趣的朋友可以再深入测试一下。
参考：
http://sqlserver2000.databases.aspfaq.com/can-i-create-an-index-on-a-bit-column.html