|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
boolean programmingIs bit the closest to the boolean (true or false) datatype in tsql? Even
though it could be 0,1,null?? TIA BIT is a numeric datatype. There is no assignable boolean datatype in SQL
Server - booleans are only valid for expressions. If BIT doesn't suit then use a CHAR: .... b CHAR(1) NOT NULL CHECK (c IN ('T','F')) -- David Portas SQL Server MVP -- Not really, for a column which can have only two values, the best approach
in SQL is to use a CHAR(1) with a CHECK constraint to limit the values. One could consider the Bit type to be closer to numeric datatypes like INT or TINYINT since they have common values and several operators overlapping among them. -- Anith Hello Anith,
why would a CHAR(1) be the best approach? Thank you! --------------------------------------- Daniel Walzenbach MCP www.walzenbach.net Show quote "Anith Sen" <an***@bizdatasolutions.com> schrieb im Newsbeitrag news:%23y0z1V5kFHA.2860@TK2MSFTNGP15.phx.gbl... > Not really, for a column which can have only two values, the best approach > in SQL is to use a CHAR(1) with a CHECK constraint to limit the values. > > One could consider the Bit type to be closer to numeric datatypes like INT > or TINYINT since they have common values and several operators overlapping > among them. > > -- > Anith > > why would a CHAR(1) be the best approach? Well, I'm not sure of the definition of "best"... but some benefits of CHAR(1) are: - you can store T/F or Y/N instead of 0/1. - you eliminate confusion for VB/Access people, who often expect -1 to mean true, and this does not work with BIT. - you can easier implement indexes, group by, etc. Drawbacks: - it is no longer language-neutral (if this is an issue) since French would expect v for vrai, or o for oui. Same goes for several other languages. - it is subject to the same problems as BIT as far as NULLability goes. - you lose the potential ability to save space in tables where multiple such columns exist. Up to 8 BIT columns can share a single byte, whereas TINYINT/CHAR(1) will always take 1 byte each. The other drawback is that most tools now deal with bit nicely as a boolean
and the char approach requires the UI programmer to handle things differently than they expect. And too often if the UI developer has to go out of their way to do something it starts to look like we db folks are being difficult "again" :) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eZwcO36kFHA.2484@TK2MSFTNGP15.phx.gbl... >> why would a CHAR(1) be the best approach? > > Well, I'm not sure of the definition of "best"... but some benefits of > CHAR(1) are: > > - you can store T/F or Y/N instead of 0/1. > - you eliminate confusion for VB/Access people, who often expect -1 to > mean true, and this does not work with BIT. > - you can easier implement indexes, group by, etc. > > Drawbacks: > > - it is no longer language-neutral (if this is an issue) since French > would expect v for vrai, or o for oui. Same goes for several other > languages. > - it is subject to the same problems as BIT as far as NULLability goes. > - you lose the potential ability to save space in tables where multiple > such columns exist. Up to 8 BIT columns can share a single byte, whereas > TINYINT/CHAR(1) will always take 1 byte each. > Thanks Aaron for your explanation. I understand your point that one would
loose the potential ability to save space in tables but wouldn't bit columns contradict using indexes? How is your feeling on bilcolumns in case of performance and usability? And what would be an ideal approach to implement a bit column? Thank you! --------------------------------------- Daniel Walzenbach MCP www.walzenbach.net Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schrieb im Newsbeitrag news:eZwcO36kFHA.2484@TK2MSFTNGP15.phx.gbl... >> why would a CHAR(1) be the best approach? > > Well, I'm not sure of the definition of "best"... but some benefits of > CHAR(1) are: > > - you can store T/F or Y/N instead of 0/1. > - you eliminate confusion for VB/Access people, who often expect -1 to > mean true, and this does not work with BIT. > - you can easier implement indexes, group by, etc. > > Drawbacks: > > - it is no longer language-neutral (if this is an issue) since French > would expect v for vrai, or o for oui. Same goes for several other > languages. > - it is subject to the same problems as BIT as far as NULLability goes. > - you lose the potential ability to save space in tables where multiple > such columns exist. Up to 8 BIT columns can share a single byte, whereas > TINYINT/CHAR(1) will always take 1 byte each. > > wouldn't bit columns contradict using indexes? Yes, indexes on bit columns are seldom useful, and I have never needed one, but a lot of people complain that Enterprise Manager won't let you do it (see http://www.aspfaq.com/2530). > How is your feeling on bilcolumns in case of performance and usability? I don't have any problems with them, though there are some minor details you should be aware of. Again, see http://www.aspfaq.com/2530 > And what would be an ideal approach to implement a bit column? I don't understand how you would change the approach? You either use BIT or you don't? Aaron,
sorry, my mistake. I should have made myself more explicit. I was refering to your point that one would loose the ability to save space in tables and thought about a field of bits. Question was if a field of bits is advisable and what would be the best approach to use one? Thank you! --------------------------------------- Daniel Walzenbach MCP www.walzenbach.net Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schrieb im Newsbeitrag news:ORq1seDlFHA.708@TK2MSFTNGP10.phx.gbl... >> wouldn't bit columns contradict using indexes? > > Yes, indexes on bit columns are seldom useful, and I have never needed > one, but a lot of people complain that Enterprise Manager won't let you do > it (see http://www.aspfaq.com/2530). > >> How is your feeling on bilcolumns in case of performance and usability? > > I don't have any problems with them, though there are some minor details > you should be aware of. Again, see http://www.aspfaq.com/2530 > >> And what would be an ideal approach to implement a bit column? > > I don't understand how you would change the approach? You either use BIT > or you don't? > I have no idea what a "field of bits" means? What I was talking about is
the fact that if you do this: CREATE TABLE dbo.Table ( col1 BIT, col2 BIT, col3 BIT, ... ) Then the space savings can be realized because, if it can, SQL Server will combine up to 8 those columns into a single byte (not into a single column). I don't recall reading anywhere that it always happens, I vaguely remember it being one of those "if the stars align" kind of things, so I don't add much weight to it, and have never really tried to take advantage of it, just wanted to mention it since we were asked what are all the pros/cons... A Show quote "Daniel Walzenbach" <daniel.walzenbach@newsgroup.nospam> wrote in message news:uFu82rFlFHA.1464@TK2MSFTNGP14.phx.gbl... > Aaron, > > sorry, my mistake. I should have made myself more explicit. I was refering > to your point that one would loose the ability to save space in tables and > thought about a field of bits. Question was if a field of bits is > advisable and what would be the best approach to use one? ;-) I was talking about sth. like the following:
CREATE TABLE dbo.Table ( col1 TINYINT, ... ) whereas col1 could be seen as 8 indipendent bits and bit0 could mean abc and bit1 could be xyz and so on... Does this make sense? --------------------------------------- Daniel Walzenbach MCP www.walzenbach.net Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schrieb im Newsbeitrag news:u6R3bxFlFHA.3544@TK2MSFTNGP15.phx.gbl... >I have no idea what a "field of bits" means? What I was talking about is >the fact that if you do this: > > CREATE TABLE dbo.Table > ( > col1 BIT, > col2 BIT, > col3 BIT, > ... > ) > > Then the space savings can be realized because, if it can, SQL Server will > combine up to 8 those columns into a single byte (not into a single > column). I don't recall reading anywhere that it always happens, I vaguely > remember it being one of those "if the stars align" kind of things, so I > don't add much weight to it, and have never really tried to take advantage > of it, just wanted to mention it since we were asked what are all the > pros/cons... > > A > > > > "Daniel Walzenbach" <daniel.walzenbach@newsgroup.nospam> wrote in message > news:uFu82rFlFHA.1464@TK2MSFTNGP14.phx.gbl... >> Aaron, >> >> sorry, my mistake. I should have made myself more explicit. I was >> refering to your point that one would loose the ability to save space in >> tables and thought about a field of bits. Question was if a field of bits >> is advisable and what would be the best approach to use one? > > Don't do it. I have done it and I regretted it. Normalize and store each fact in its own column.
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Daniel Walzenbach" <daniel.walzenbach@newsgroup.nospam> wrote in message news:e9n3h6FlFHA.3288@TK2MSFTNGP09.phx.gbl... > ;-) I was talking about sth. like the following: > > CREATE TABLE dbo.Table > ( > col1 TINYINT, > ... > ) > > whereas col1 could be seen as 8 indipendent bits and bit0 could mean abc and bit1 could be xyz and > so on... > > Does this make sense? > > --------------------------------------- > Daniel Walzenbach > MCP > > www.walzenbach.net > > > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schrieb im Newsbeitrag > news:u6R3bxFlFHA.3544@TK2MSFTNGP15.phx.gbl... >>I have no idea what a "field of bits" means? What I was talking about is the fact that if you do >>this: >> >> CREATE TABLE dbo.Table >> ( >> col1 BIT, >> col2 BIT, >> col3 BIT, >> ... >> ) >> >> Then the space savings can be realized because, if it can, SQL Server will combine up to 8 those >> columns into a single byte (not into a single column). I don't recall reading anywhere that it >> always happens, I vaguely remember it being one of those "if the stars align" kind of things, so >> I don't add much weight to it, and have never really tried to take advantage of it, just wanted >> to mention it since we were asked what are all the pros/cons... >> >> A >> >> >> >> "Daniel Walzenbach" <daniel.walzenbach@newsgroup.nospam> wrote in message >> news:uFu82rFlFHA.1464@TK2MSFTNGP14.phx.gbl... >>> Aaron, >>> >>> sorry, my mistake. I should have made myself more explicit. I was refering to your point that >>> one would loose the ability to save space in tables and thought about a field of bits. Question >>> was if a field of bits is advisable and what would be the best approach to use one? >> >> > > Tibor,
this is my feeling about it as well but I was just wondering how you guys think about. Thank you! --------------------------------------- Daniel Walzenbach MCP www.walzenbach.net Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> schrieb im Newsbeitrag news:%232ANgUGlFHA.2608@TK2MSFTNGP14.phx.gbl... > Don't do it. I have done it and I regretted it. Normalize and store each > fact in its own column. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Daniel Walzenbach" <daniel.walzenbach@newsgroup.nospam> wrote in message > news:e9n3h6FlFHA.3288@TK2MSFTNGP09.phx.gbl... >> ;-) I was talking about sth. like the following: >> >> CREATE TABLE dbo.Table >> ( >> col1 TINYINT, >> ... >> ) >> >> whereas col1 could be seen as 8 indipendent bits and bit0 could mean abc >> and bit1 could be xyz and so on... >> >> Does this make sense? >> >> --------------------------------------- >> Daniel Walzenbach >> MCP >> >> www.walzenbach.net >> >> >> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schrieb im >> Newsbeitrag news:u6R3bxFlFHA.3544@TK2MSFTNGP15.phx.gbl... >>>I have no idea what a "field of bits" means? What I was talking about is >>>the fact that if you do this: >>> >>> CREATE TABLE dbo.Table >>> ( >>> col1 BIT, >>> col2 BIT, >>> col3 BIT, >>> ... >>> ) >>> >>> Then the space savings can be realized because, if it can, SQL Server >>> will combine up to 8 those columns into a single byte (not into a single >>> column). I don't recall reading anywhere that it always happens, I >>> vaguely remember it being one of those "if the stars align" kind of >>> things, so I don't add much weight to it, and have never really tried to >>> take advantage of it, just wanted to mention it since we were asked what >>> are all the pros/cons... >>> >>> A >>> >>> >>> >>> "Daniel Walzenbach" <daniel.walzenbach@newsgroup.nospam> wrote in >>> message news:uFu82rFlFHA.1464@TK2MSFTNGP14.phx.gbl... >>>> Aaron, >>>> >>>> sorry, my mistake. I should have made myself more explicit. I was >>>> refering to your point that one would loose the ability to save space >>>> in tables and thought about a field of bits. Question was if a field of >>>> bits is advisable and what would be the best approach to use one? >>> >>> >> >> > > whereas col1 could be seen as 8 indipendent bits and bit0 could mean abc Yes, I understand what you are getting at now. And I vehemently agree with > and bit1 could be xyz and so on... > > Does this make sense? Tibor. This is like storing flags in a bit mask or stuffing a bunch of unrelated text together in binary. Each entity should be represented independently in the data model. It is very hard to code something like " AND abc = 1 AND xyz = 0" when you have to start doing bitwise or other complex operators on a single value. I think you will quickly see that whatever advantage you have in terms of space is immediately undone by the PITA you have managing and maintaining the mess. Right, I agree to your answer. As I said to Tibor the question was only for
curiosity. Thank you both again for your oppinion! Greetings from Germany --------------------------------------- Daniel Walzenbach MCP www.walzenbach.net Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schrieb im Newsbeitrag news:exioKrGlFHA.1444@TK2MSFTNGP10.phx.gbl... >> whereas col1 could be seen as 8 indipendent bits and bit0 could mean abc >> and bit1 could be xyz and so on... >> >> Does this make sense? > > Yes, I understand what you are getting at now. And I vehemently agree > with Tibor. This is like storing flags in a bit mask or stuffing a bunch > of unrelated text together in binary. > > Each entity should be represented independently in the data model. It is > very hard to code something like " AND abc = 1 AND xyz = 0" when you have > to start doing bitwise or other complex operators on a single value. I > think you will quickly see that whatever advantage you have in terms of > space is immediately undone by the PITA you have managing and maintaining > the mess. > I agree. Don't combine multiple fields into one column. I've had
colleagues use it several times for custom permissions. For example, they store all values for a group of textboxes into a single field. There are scalability issues with this techique and it follows no normalization rules. blech. These collegues needed to darned to heck with Phil the Prince of
Insufficient Light. Or Celko could go off on them Drill Instructor style for an hour or two :) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP <patrick.spa***@gmail.com> wrote in message news:1123073680.728698.222780@z14g2000cwz.googlegroups.com... >I agree. Don't combine multiple fields into one column. I've had > colleagues use it several times for custom permissions. For example, > they store all values for a group of textboxes into a single field. > There are scalability issues with this techique and it follows no > normalization rules. > Thank you guys ;-)
--------------------------------------- Daniel Walzenbach MCP www.walzenbach.net Show quote "--CELKO--" <jcelko***@earthlink.net> schrieb im Newsbeitrag news:1123107901.982471.171810@g49g2000cwa.googlegroups.com... >A dirty job, but someone has to do it. > Daniel Walzenbach wrote:
> Thanks Aaron for your explanation. I understand your point that one would I don't see much difference in the selectivity of an index on a bit> loose the potential ability to save space in tables but wouldn't bit columns > contradict using indexes? How is your feeling on bilcolumns in case of > performance and usability? And what would be an ideal approach to implement > a bit column? > column compared to the selectivity of a char column limited to two (or three, including NULL) possible values. Just because you can put an index on a column, it doesn't mean the optimiser must (or even will) use it. Damien > Is bit the closest to the boolean (true or false) datatype in tsql? Even It really isn't all that difficult to use a BIT column with two-valued logic > though it could be 0,1,null?? as opposed to three-valued logic. CREATE TABLE dbo.Example ( TrueFalse BIT NOT NULL ) Null problem solved, no? I actually disagree. I, when looking at boolean datatypes, tend to use
the bit datatype. By making it a NOT NULL column and setting a default to 0, it should solve your problems. I know for a fact that vb/vb.net interprets a bit value as boolean. I would recommend explicitly casting the value to boolean, but it will work.  |
|||||||||||||||||||||||
Other interesting topics