February 23, 2008 at 9:21 am
Hello, I'm a beginner and would like to know how to create a column in a table with a true or false field, 1 for true and 0 for false.
I understand that a Bit Data type would work, how would i script it?
I have [Column Name] bit as part of the script, can anyone suggest how i continue this?
February 23, 2008 at 10:41 am
What do you mean by "continue?" You add this like any other data type. A bit can only have 1 or 0 values, so there's nothing else you need to do.
you can add a default if you wish or allow it to be null, which essentially gives you 3 values (0, 1, unknown).
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 23, 2008 at 9:45 pm
If you're unsure, use the GUI tools but instead of saving your changes have it save a script for you.
February 24, 2008 at 2:04 pm
Hi,
if you are very sure about the values that you can have for that columns is less than 3 then you can go ahead with bit data type. But its always better to use CHAR(1) to server the same purpose and can provide you more flexibility.
Thanks -- Vj
February 25, 2008 at 8:02 am
Perhaps someone can correct me, but I found the bit datatype a dissapointment for a couple of reasons:
1. Since T-SQL doesn't understand TRUE/FALSE you are still stuck testing for 1 or 0.
2. A bit column does not seem to be allowed to be referenced in a SUM(myBitColumn) phrase. (Are 1's that hard to sum??)
February 25, 2008 at 10:30 am
1. this is true
2. you can't sum directly on a bit field because of the internal representation of that type of field in SQL Server. To accomplish what you want to do, you can instead get the row count for the rows for which this field's value is 1 :
SELECT COUNT(myBitColumn) from myTable WHERE myBitColumn=1
Stanislas Biron
February 25, 2008 at 10:36 am
The "problem" with the SUM is that it's going to preserve the datatype, so you end up with some really "strange" results. Such as - anything summing up even numbers of 1 = 0, odd numbers of 1's = 1(since the bit can hold only 1 or 0). Now - while I could see some usefulness for this kind of function, it's probably not what you wish.
You CAN do something like sum(CAST(bitfield as int)) which would yield what you are expecting to get back.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 25, 2008 at 3:15 pm
i haven't tried this out...but could you use something like the following to sum a bit field:
SELECT COUNT(CASE WHEN bitfield = 1 THEN 1 ELSE NULL END) as totalBits
FROM myTable
February 27, 2008 at 4:50 am
Matt - I'm embarrassed that I didn't realize that at the time; being in a hurry I just cursed and set the field back to an int. Guess I've just been working with too many non-strongly-typed languages.
Ivanna's suggestion:
SELECT COUNT(CASE WHEN bitfield = 1 THEN 1 ELSE NULL END) as totalBits
FROM myTable
raises a couple of questions which I should check:
a. Are both literal "1"s taken to be int, with one being converted to bit for the comparison?
2. If the aggregate function was AVG instead of COUNT would a float be returned, or just a truncated int?
Thanks to both, sorry I'm late replying.
February 27, 2008 at 12:13 pm
Hi Jim
I tested the use of both COUNT and AVG and in both cases the statement returned an INT
COUNT returned the number of records where the bit was set to 'true' - therefore the result is an INT
AVG returned 1, presumably because a bit field can only be true or false, and for each 'true' the CASE returns a 1 - so for example, if there are 20 'trues' CASE will return 20 '1's - an AVG of 1 (in fact AVG will always be 1)
Sorry if I'm stating the obvious, I'm no guru - hope this helps though 🙂
February 27, 2008 at 12:26 pm
Jim Russell (2/27/2008)
Matt - I'm embarrassed that I didn't realize that at the time; being in a hurry I just cursed and set the field back to an int. Guess I've just been working with too many non-strongly-typed languages.Ivanna's suggestion:
SELECT COUNT(CASE WHEN bitfield = 1 THEN 1 ELSE NULL END) as totalBits
FROM myTable
raises a couple of questions which I should check:
a. Are both literal "1"s taken to be int, with one being converted to bit for the comparison?
2. If the aggregate function was AVG instead of COUNT would a float be returned, or just a truncated int?
Thanks to both, sorry I'm late replying.
It's going to truncate to an int.
If you want a float (and something accurate) result you would want to make 2 changes:
SELECT AVG(case when bitfield=1 --here 1 is going to be treated as a bit
then 1.0 --by just putting in something with something after the decimal it can use it as a float
Else 0.0 --if you left the NULL here - the only 2 values possibles for AVG would be 1.0 and NULL
END)
AVG(fieldname) is like count(fieldname) (and all other aggregates for that matter) - the NULLs are disregarded altogether.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 10, 2008 at 5:29 am
Actually you can use literals 'true' and 'false' instead of 1 and 0.
May 12, 2008 at 7:07 am
Vijaya Krishna (2/24/2008)
if you are very sure about the values that you can have for that columns is less than 3 then you can go ahead with bit data type. But its always better to use CHAR(1) to server the same purpose and can provide you more flexibility.
Thanks -- Vj
http://dotnetvj.blogspot.com[/quote%5D
Actually, there are disadvantages to using Char(1) for this. Mainly in terms of space used. You can have 8 bit columns fit into one byte of storage, but 1 Char(1) column uses a full byte all by itself. There are also front-end applications that can recognize a bit field for what it is, and know how to "play the game" with it automatically.
The size difference is the main one. If you ever have more than 1 of this sort of column in the same table (or indexed view), then it takes up less space and transfers from disk to RAM faster.
Another disadvantage is that you sometimes end up with some developers using "T/F" and other ones using "Y/N", and then you'll get someone who'll use "1/0". All in your Char(1) columns. Makes coding more complex.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply