September 23, 2009 at 8:37 pm
Each to their own - personally I can't see why being able to store values from zero to 255 is a better way of storing a boolean field than what you can with a bit field (0/1).
September 24, 2009 at 1:55 am
niall.baird (9/23/2009)
Each to their own - personally I can't see why being able to store values from zero to 255 is a better way of storing a boolean field than what you can with a bit field (0/1).
It's worse, because you should then add a constraint to ensure the only allowable values were 0 or 1.
September 24, 2009 at 5:37 am
Ian Scarlett (9/24/2009)
It's worse, because you should then add a constraint to ensure the only allowable values were 0 or 1.
Not only that, but if you have mutliple flags, then storage will be a lot more than it needs to be. The storage engine will store up to 8 bit fields in a single byte, wheras a tinyint will always be a single byte by itself. Have a look at http://msdn.microsoft.com/en-us/library/ms177603.aspx. One thing that topic doesn't touch on is how nullability is handled with respect to bit-packing.
However, I would say that from an efficiency point of view, it can certainly be beneficial to store multiple bit fields in a single integer. Say, for example, you have 32 'flags' that can be applied to an entity, which doesn't really affect that entity, but changes the way that a client application responds, then that client application only has to read one value, rather than 32. An example would be a loyalty application I wrote, where a site (i.e. somewhere someone purchased goods) had several properties ('has bakery', 'local produce', 'coffee shop' etc). The database didn't treat sites any differently based on those properties, but the application would produce different targetted messages. (i.e. no point giving a customer a promotion for a free coffee when you can't get coffee where they are). Doing that meant:
a) The data stream from DB to client app was smaller - as a single int32 value is a lot smaller than 32 individually packaged bits in a data stream
and
b) The app could more efficiently retrieve those values (i.e. one call as opposed to 32).
If your database needs to react differently according to those values, then there would be a strong case for separating the fields, such that integrity checking could be natively applied.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 24, 2009 at 10:26 am
Give this link a read ...
http://msdn.microsoft.com/en-us/library/ms177603.aspx
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply