Tinyint vs Bit?

When you add a bit column to your table it will occupy a whole byte in each record, not just a single bit. When you add a second bit column it will be stored in the same byte. The ninth bit column will require a second byte of storage. Tables with 1 bit column will not gain any storage benefit.

Tinyint and bit can both be made to work, I have used both successfully and have no strong preference.

Bit...unless you're of the "true / false / file not found" clan

In case you didn't get the reference...

And in the case of Linq2SQL, bit works with true/false which makes it easier to program for. There's advantages to both.

And there's also programming maintenance to consider. What happens if you (or a junior intern programmer) uses a 2, 3, 25, 41, 167, 200 etc? Where is that documented? Bits are self-documenting and pretty universal.

I use bits when appropriate. Aside from it being semantically the correct type (semantics count!), multiple bit fields (up to 8) in a single row (on SQL Server, anyway) can be consolidated into a single byte of storage. After the eighth, an additional byte is needed for the next 8, and so on.

References:

• SQL 2000
• SQL 2005
• SQL 2008