Never Index a BIT?
Never say never, right? For example, you have an orders table. It has an IsShipped bit column which is what marks an order as done to the business. You might have a more complex version of this example so employees can see the outstanding order count in the app:
CREATE TABLE #temp
(
c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
c2 VARCHAR(10) DEFAULT 'bob',
c3 bit
)
INSERT INTO #temp(c3)
VALUES
(1)
GO 5000000
INSERT INTO #temp(c3)
VALUES
(0)
GO 500
--13,000 IO's
SET STATISTICS io ON
SELECT COUNT(*)
FROM #temp
WHERE c3 = 0
CREATE INDEX ix ON #temp(c3) include (c2)
--6 IO's
SET STATISTICS io ON
SELECT COUNT(*)
FROM #temp
WHERE c3 = 0
In SQL 2008, we can get a little better with filtered indexes but really all we are saving is space.
CREATE INDEX ix2 ON #temp(c3) include (c2)
WHERE c3 = 0
--4 IO's
SET STATISTICS io ON
SELECT COUNT(*)
FROM #temp
WHERE c3 = 0
This content is published under the Attribution-Share Alike 3.0 Unported license.
Comments
My RSS
-
My LifeStream
My Presence
