SQL Server Performance Forum – Threads Archive

Difference between bit and tinyint

I’m having a bit of a discussion with my colleagues on this.
Does anyone know of any benefit over using a Bit datatype over a Tinyint?
They both seem to take up 1 byte of sql servers storage and from BOL it says that the bit optimises storage by adding all bit columns together (in a table) and less than eight would take up only a byte, but if you’ve only got one bit column (which then takes up a byte) is there a difference between using a bit and tinyint besides the values they can hold? World Domination Through Superior Software

As you say, if you have 8 or fewer bit columns they will fit on 1 byte. If you have 1 bit it will still take up 1 byte. Except from that I don’t know any other benefits. But some programming languages like ASP will sometimes convert a bit as text (False, True) and not numbers (0,1) when working with them. A bit anoying when doing compares and stuff. I think the behaviour is different with different versions of vbscript.dll and mdac as well. And sometimes requirement changes in applications. For example when you first designed it, unitX could only have the status "on" or "off". Then two months later the client wants to change it and add the status "paused". Then you have to change all your code refering to bit to tinyint instead. I would only use bits if you use a lot of them in the same table and you are really picky about space issues. /Argyle

I agree with argyles points. Not a great deal of difference unless you have lots of them in a single table, but the main differences are its often more awkward to develop with. Im sure I seem to remember another issue in sql 7.0 (or was it 6.5), where either:
bit fields could not be NULL or perhaps it was that bit fields could not form part of an index (not really much of an issue). I checked this on sql2k and neither is now true. Perhaps Im mistaken for earlier versions too.

Thanks guys, consolidated my argument, I don’t see much use of the bit data type over the tinyint either, and I find it a little more scalable for those time where a simple yes/no or 1/0 need to include other possibilities as well. Thanks
Shaun World Domination Through Superior Software

]]>