May 19, 2003 at 10:53 pm
We have a field that can hold either a Boolean "True/False" value or an integer "0/1" value (we haven't decided yet which way to go).
When making a comparison using t-sql is it faster to use the Boolean approach or the integer approach? (Obviously a loaded question)
TIA,
Bill
May 19, 2003 at 11:34 pm
When you say Boolean, you mean BIT? (1 / 0)
There is bot true / false field as such.
I would imagine a BIT would be faster than a INT field.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
May 20, 2003 at 12:58 am
Hi,
the boolean true/false values are different representation of 1 or 0. I don't think there are significant performance differences between an integer and a bit. The bit field might be a tick faster. And takes less disk space, if that's an issue.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 20, 2003 at 4:11 am
Hi,
I think the BIT option would be faster, not sure by how much though.
When comparing two BITs you are literally comparing at a Bit level - value A (0 or 1) compared against value B (0 or 1). When comparing two INTs you have to pattern match the 32 bits (as an INT is represented by 4 Bytes) that represent an ineteger value of 0 or 1.
There would also be a fair amount of space saved as an INT needs 4 Bytes whereas a BIT needs 1 Byte (taken up by your first BIT, but the next seven BITS used within the containg data table would use the seven free slots remaing).
Cheers,
Alan.
May 20, 2003 at 4:34 am
Overall the performance should be the same but you are wasting 3 bytes per row of data so always use the simplest value. If you think you may expand the value beyond ture and false then consider using tinyint (can be 0-255) but otherwise use simplest whichs is bit. Also, keep in mind, do not bother with an index on the column, that is an additional waste.
May 20, 2003 at 6:29 am
Bear in mind on SQL7 you 'Cannot group by a bit column', don't know about 2000 though. You would have convert it or use case to do it.
Far away is close at hand in the images of elsewhere.
Anon.
May 20, 2003 at 9:32 am
We always use TINYINT. Probably a result of version 6.5 handling of the BIT datatype which had problems allowing further changes to the data structure. I agree with Antares - we too often find that what was True/False yesterday has a "maybe" value today.
I've never run a performance test and am not qualified to tell you which is faster.
Guarddata-
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply