sql server programming: If there is only 1 BIT column, it will occupy one byte per row. If you have
up to 8, it should still only occupy one byte per row.

Of course, you could always confirm this for yourself by running a quick
test:



CREATE DATABASE splunge
GO

USE splunge
GO

CREATE TABLE foo1 -- 1 bit col
(
a BIT NOT NULL DEFAULT 1
)

CREATE TABLE foo2 -- 4 bit cols
(
a BIT NOT NULL DEFAULT 1,
b BIT NOT NULL DEFAULT 1,
c BIT NOT NULL DEFAULT 1,
d BIT NOT NULL DEFAULT 1
)

CREATE TABLE foo3 -- 8 bit cols
(
a BIT NOT NULL DEFAULT 1,
b BIT NOT NULL DEFAULT 1,
c BIT NOT NULL DEFAULT 1,
d BIT NOT NULL DEFAULT 1,
e BIT NOT NULL DEFAULT 1,
f BIT NOT NULL DEFAULT 1,
g BIT NOT NULL DEFAULT 1,
h BIT NOT NULL DEFAULT 1
)

CREATE TABLE foo4 -- 9 bit cols
(
a BIT NOT NULL DEFAULT 1,
b BIT NOT NULL DEFAULT 1,
c BIT NOT NULL DEFAULT 1,
d BIT NOT NULL DEFAULT 1,
e BIT NOT NULL DEFAULT 1,
f BIT NOT NULL DEFAULT 1,
g BIT NOT NULL DEFAULT 1,
h BIT NOT NULL DEFAULT 1,
i BIT NOT NULL DEFAULT 1
)
GO

SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE @i < 10000
BEGIN
INSERT foo1 DEFAULT VALUES
INSERT foo2 DEFAULT VALUES
INSERT foo3 DEFAULT VALUES
INSERT foo4 DEFAULT VALUES
SET @i = @i + 1
END
GO

EXEC sp_spaceused foo1
EXEC sp_spaceused foo2
EXEC sp_spaceused foo3
EXEC sp_spaceused foo4
GO

USE master
GO

DROP DATABASE splunge
GO


--
http://www.aspfaq.com/
(Reverse address to reply.)




[quoted text, click to view]

The value stored for a BIT takes up one bit for each row of the table,
and I believe that another bit is required for the bit column in the
null bitmap for the table, whether or not the column is declared as NULL
or NOT NULL. Each of these two bits is in a separate byte, which can
hold bits from other columns if there are any, but if the row has no
other columns, the other 7 bits of each byte are wasted. Similarly,
even if there are only a few rows in the table, the table will take an
entire data page (8K bytes, not 16K, I believe).

What you are asking for (size of the table) has different
interpretations - number of bits needed to represent the data, disk
allocation required for the table, size of each row - and of course it
depends on the number of rows of the table and whether there are any
indexes.

See the Books Online topics about "Estimating the size of a table" for
more information.

Steve Kass
Drew University

[quoted text, click to view]