Known problems with bit-columns?

Question

Known problems with bit-columns?

Jetro

Ten Centuries

Points: 1257
More actions
December 7, 2007 at 7:24 am

#179762

Hi all,
I got the recommendation not to use bit-columns in SQL Server 2005 when working with this columns in .NET Software (ASP.NET web pages with database access). Instead I should use tinyint-columns to store boolean values.
I'm very confused about that. Therefore my question: Do you know any problems which can occur when I use bit-columns instead of tinyint-columns to store boolean values?
Thank you 🙂
-----------------------
SQL Server Database Copy Tool at Codeplex

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 1

There should be no problems, although tinyint can store more values (2, 3, 4), so you possibly could have other data in there that your application doesn't deal with. So test explicitly for 1,0, not True/false.

There is no problem with bits other than that your application could change and you need more space. However it's not a hard schema change unless we are talking millions of rows, then it's time.

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 2

The main reason I've received for not using bit fields is because they don't allow null values. You have to put a 0 or 1. That's why you may be forced into using tinyint.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jetro-575728 SSC Journeyman Points: 94 More actions · Answer 3

Thank you for your answers.

I will discuss this again with my boss 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Grant Fritchey (12/7/2007)
The main reason I've received for not using bit fields is because they don't allow null values.

They accept nulls just fine (unless defined not null). At least in 2005.

Create table BitTest (

ID int identity,

testing bit NULL

)

GO

Insert into bitTest (testing) values (0)

Insert into bitTest (testing) values (1)

Insert into bitTest (testing) values (NULL)

SELECT * from bitTest

Personally I usually define them as not null, because if used as booleans, the presence of a third potential value can really mess queries up unless written carefully. (and I've encountered a fair few developers over the years who don't understand 3-state logic)

There are some odd things with bits. Despite seeming to only taking 1 bit, they actually take 1 byte or space within the row if you only have one in the table. If you have multiple bit columns in a table, they get packed together. 2 bit fields and 6 bit fields take the same amount of space to store (1 byte).

It's near pointless indexing bit columns, at least with the bit as the leading column due to the low selectivity.

Unless you are very careful, you can get implicit conversions when filtering on a bit. Only really an issue if you do have an index on the bit column. A query of the form

SELECT testing from bitTest where testing = 0

gets parameterised as follows

(@1 tinyint)SELECT [testing] FROM [bitTest] WHERE [testing]=@1

and the constant is picked up as a tinyint, not a bit, meaning that the query is executed as

SELECT testing from bitTest where convert(tinyint,testing) = 0

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

Agreed... Bit values are, in fact, "tri-state"... off, on, and "don't know" and, depending on your requirements, that may or may not be important. For example, in a "testing" application, did someone answer No or Yes or did they simply skip/not answer the question?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 6

If it's true/false, then don't allow NULLs. confuses things.

I'd still recommend going with tinyint, gives you more options down the road. I've often seen someone define something a boolean, only to change their mind later.

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 7

Ah, I wasn't aware that changed in 2005. Thanks for letting me know. That'll change decisions in the future.

Personally, I kind of don't care, bit or tinyint, but if something is supposed to only be 1 or 0, I'll use bit.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

It didn't change in 2005. I don't know about previous editions, because I started with SQL 2000, but 2000 also had no issues with null bit columns

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 9

Boy. I don't know where I've been. I sure thought it was that way. Maybe I'm channeling some old bit of knowledge from a different system. Damn. I hate being so fundamentally wrong on such a simple topic. Thanks for the help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning