SQL Server 2008: tinyint vs bit

Question

I need to add a new column to an existing table at work, and I'm wondering whether to create it as a tinyint or bit datatype. Our company recently upgraded from SQL Server 2000 to 2008, and IIRC my boss said there were issues in SQL Server 2000 when attempting to use bits. I think he said they were related to indexes and/or grouping -- I forget.

What factors should I consider when deciding between tinyint and bit, if the only values I plan on storing are true/1 or false/0? Are there any rules from SQL Server 2000 that I can/should unlearn?

Answer 1

bit is generally used for boolean values. If all you are storing in the db are boolean values, then I think a tinyint would be overkill.

Answer 2

The problem in 2000 was a bit couldn't be part of an index. Which is logical since a bit only has two states. It is a very poor field to have on an index. In 2005 you can create covered indexes, which allows you to specify extra pieces of data to store with the index. This allows you to avoid a bookmark lookup.

I would go with the bit.

Answer 3

if you only have one bit column in a table, it will still take a byte to store it.

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

bit (Transact-SQL)

SQL Server 2005 does the same