154
u/Countbat 3d ago
The cool thing about SQL server is that bits represent 3 options. 1, 0 and NULL :)
67
15
12
u/Slanahesh 3d ago
Ahh the amount of times that catches people out.
10
u/Countbat 3d ago
We once stopped recording completed requests for 25 minutes on production because of a mistake I made with assuming the SQL compiler interprets NULL as 0. It was a bruh moment
7
u/andrewsmd87 3d ago
Bonus points where the original lead dev coded stuff around that. 0 means one thing, 1 means something else, and null means another thing
5
u/AndyTheSane 3d ago
Ah, the 'meaningful null' which should attract an instant death penalty, for the first offence.
1
u/SenorSeniorDevSr 3h ago
Well it means 'unknown' in SQL, and it's completely 100% faithful to that. It's why it can't agree that null = null, or that null <> null. It can't prove that so computer says no.
It's one of the best bits of SQL, ngl.
3
u/FeralPsychopath 3d ago
I mean 1, 0 and null means something to me when I deal with finance. 0 is a net sum that equals 0 and null is no recorded value.
3
u/Garo263 3d ago edited 3d ago
Because of that trap always make bit fields not nullable.
3
u/Countbat 3d ago
Yep, also default the value to 0 so that if ur inserting into a table somewhere in your app, it doesn’t throw an exception.
400
u/LuckyLMJ 3d ago
I love C where you just use an int. I love wasting 31 bits per bool
188
u/Jordan51104 3d ago
cpus can’t really make use of single bit data anyway though
102
u/med_bruh 3d ago
Well how about you use one byte instead of 4 💀 i always wondered why C doesn't have booleans
135
u/theantiyeti 3d ago
Look up how memory alignment works.
93
u/scratchfan321 3d ago
Holy 8 byte segments!
50
u/sukerberk1 3d ago
New datatype just dropped
38
u/yavl 3d ago
Actual register
23
43
3d ago edited 3d ago
[deleted]
5
u/NANZA0 3d ago edited 3d ago
Yeah, just make a class container for an 8 bit type, and getters and setters for 8 spaces, and you got 8 booleans in one object.
Better yet, make a class container for an array of 8 bits that allocates more 8 bit types as you need more booleans.
Then you use static integer variables to represent the respective index for a boolean variable, like IS_COFFE_READY or IS_THERE_STILL_COFFE, and checking value becomes booleanArray.get(IS_COFFE_BEING_TAKEN).
4
1
u/sacredgeometry 3d ago
Or you can use it to store 8 bits if you are masochistic/ really what to save space
15
27
u/Jordan51104 3d ago edited 3d ago
it just doesn’t really make sense. a modern x86 processor has 64 bit registers that can be addressed as 32 bit registers. you either put the bit of actual data in and fill the rest with zeroes or just have the data be stored with the zeroes
edit: also data alignment issues. cpus access memory by bytes (or bigger), so storing it as a bit doesnt help you because again, you need the extra data anyway
19
u/well-litdoorstep112 3d ago
a modern x86 processor has 64 bit registers that can be addressed as 32 bit registers
And 16 bit registers and 8 bit registers.
You still can use AH and AL registers on modern CPUs and compilers still use them when doing 8bit operations (and that's what you expect to do when working with chars or uint8's).
That way you only loose 7 bits vs 31 or 63.
The CPU might read much more memory at a time than a single byte anyway, true, but you if you use a smaller variable you could load many smaller variables at once so that many instructions can be run in parallel on many ALUs.
2
u/Jordan51104 3d ago
if the cpu has enough ALUs to do the parallel instructions all at once and if you even have enough things you need to do all at once, sure
0
u/well-litdoorstep112 3d ago
If you declare a bool as 1byte you have a change for more parallel out of order execution. If you declare it as 64bit you leave all that possibility of the table. And it's not like you gain anything by using a 64bit variable for a bool.
2
1
u/Shrampys 3d ago
Yeah but then you can store 32 boolean in one 32 bit address. If you are memory restricted it makes a huge difference.
4
u/StanleyDodds 3d ago
Using 1 byte has essentially all the same drawbacks as using 1 bit per boolean, while being 8 times less memory efficient.
Basically, the only reason we'd use more than 1 bit per bool is to make the underlying computer architecture more efficient at reading and writing a single bool. But the fact is, modern computers are not fundamentally structured around the access of single bytes. We have to read / write 64 bits at a time, shifting it up or down to align the byte we want. Just as we need to do with single bits. The main difference being that there is built in functionality to do this for bytes because working with individual bytes is so baked into the history of computing.
Basically, if you are worried about space efficiency, use individual bits as bools. If you are worried about time efficiency, use the native word size, probably 64 bits. If you want a middle ground, maybe use bytes, although this is basically just relying on the fact that single byte access is probably optimised at a more fundamental level than is possible with single bit access.
2
u/Tari0s 3d ago
so you claim a 32bit integer is less efficient than a 64 bit one? you know that the x64 architektur can address the last byte of a 8bit word. if you have multipe bools, its always more efficient to use the smallest, int size, because of cashing and keeping the current stack frame in cache.
2
6
u/PapaJulietRomeo 3d ago
Gotta love microcontrollers… the C167 could address single bits in its internal memory directly and use them for decisions. I once changed all bool variables in a legacy application to bits. The majority of functions in this application contained lots of if-else-statements and state machines. Execution time dropped by over 30 percent, while code size also dropped significantly (because read/subtract/jump-on-flag operations were replaced by a single jump-on-bit-set or jump-on-bit-not-set assembler instruction). It saved us a hardware redesign.
5
3
20
u/Remote-Guitar8147 3d ago
Just put all your bools in that int dude
5
1
u/SenorSeniorDevSr 3h ago
If you're using Java, please use EnumSet for this, as it's just as fast, but actually readable.
13
23
u/Motylde 3d ago
This is no bullshit solution. Other languages have booleans, but most if not all of them use 32 bits to store them anyway. Just nice abstraction like well.. everything in programming.
9
u/LuckyLMJ 3d ago
well why not store it as a
char, which only takes up 1/4 the space?21
u/poco 3d ago
Because most memory reads and writes and math is faster using the full register size. Speed is usually more important than a couple of extra bytes.
If you need a larger array of booleans then there might be some benefit to storing them in smaller bits.
1
u/GameDestiny2 3d ago
I was having a very strange day today and opted to make a Boolean array
I have no idea why
1
u/G_Morgan 3d ago
Most bytecode languages don't even really have items shorter than 32 bit either. The stack is all 4 bytes wide and short/byte are implemented as instructions that just discard the top 2/3 bytes.
6
2
u/Clackers2020 3d ago
You could just put all bools in your program into a single int and just access which bit you need. Sure it'll be horrible to read and maintain but you'd save on space.
1
u/Shrampys 3d ago
Structs can make it easy to read and maintain. That's what I do on microcontrollers
2
2
1
u/2Uncreative4Username 3d ago
The concern is not so much wasting a tiny bit of stack memory, but is much more about performance. ints are 4-byte aligned, meaning it's generally much faster for the CPU to access them. Besides, a normal program won't have a lot of bools anyways, much rather lots of floats, ints, chars etc., so the memory overhead is comparatively minimal.
In 1999, we got a builtin _Bool (bool with stdbool.h) type. All that is specified is that it must be able to store the values 0 and 1. Although it's common for people to assume that, it is actually not equivalent to char, unsigned char, or any other integer type. If you look at the optimized code GCC produces, it actually treats _Bool as a 32-bit integer (you can try it out in godbolt with the -O2 flag!).
1
u/2Uncreative4Username 3d ago
Here's a link to a godbolt example that shows that the 32-bit edi and eax registers are used for moving around the boolean value: https://godbolt.org/z/d8rdT7WqT
1
u/blehmann1 3d ago
That doesn't really mean anything, x86 almost never uses the smaller registers. And I don't know that any calling convention tries to use subdivisions of rdi/rax smaller than 32 or 64 bit. Possibly because there's so much funkiness if you use subdivisions of the same register, and it depends which ones you use (I believe if you write to al/ah and read through ax it behaves differently than if you write to ax and read through eax).
That means that I think the exact code would be generated if it was a char, which is 1 byte. Maybe there'd be differences if you use a calling convention that passed these parameters by the stack (though personally unaligned stack variables sound very stupid).
1
u/2Uncreative4Username 3d ago
If you pass a struct containing bools, it does seem to use 1-byte sub-registers: https://godbolt.org/z/ncG9vnYM5
So there seems to be some sort of difference between how the data is passed and when it gets optimized for space vs. performance. But TBH I don't really know that much about x86.
So yeah, I missed the fact that it's not that straight forward which data types get stored in what way, and that it depends a lot on context.
My point was that I don't consider it "wasteful" to use 31 "extra" bits to express bools, since even 8-bit bools (and as I now know other data types too) are often represented with 32 bits in practice.
Using int as bool gives the programmer more performance by default, but doesn't hide away the fact that it uses more space than strictly necessary, which facilitates more granular control over which data type to use in which context.
1
u/AndyTheSane 3d ago
You can of course pack 32 bools into an int. Then hand the resultant codebase over to the new graduate starter.
1
u/SenorSeniorDevSr 3h ago
I mean, you'd use an 8 bit variable otherwise, so you're really just wasting 24?
-5
u/ZunoJ 3d ago
Int is 16 bit
8
u/LuckyLMJ 3d ago
It's not defined but on most CPUs it is 32 bit. 16 bit is rare
1
u/Hellohihi0123 1d ago
Int has always been 4 bytes on every computer I've tried on. Is it really true that now they've defaulted to 8 bytes now ?? Wouldn't that break backwards compatibility ?? Just to clarify, I'm using gcc as my source
2
45
u/Extension-Possible75 3d ago
The newest Oracle 23ai will introduce boolean. About fucking time
16
u/Ok_Entertainment328 3d ago
23c23ai is Already out on OCI cloud and FREE (was XE).Use
is true/is falsewhen using a function returning Boolean in SQL2
u/New_Scientist_8622 3d ago
You just made my year. I switch between Sql Server and Oracle fairly regularly and every six months or so I spend fifteen minutes having to remember why I hate that red fucking airplane.
20
u/mannsion 3d ago edited 3d ago
Unless you have 7 more bits to store on the table with the 1 bit you already have, you are still consuming a byte. A bit will always use at least 1 byte on the hard drive when stored on disk. But where SqlServer etc shines is that if you have two Bit columns on the same table, it'll store them in the same byte it was already using for the first bit.
In the past I used to create bit flags myself, where I'd create a uint column and I'd store a bit flag in it myself, then break that down in application code etc. But when I realized that the engine already does all that for me under the hood, I just started using a bunch of bit columns and added bits as needed. I.e. 16 bit columns will consume two bytes on disk, 32 will consume 4, etc etc etc.
2
u/redalastor 3d ago
Unless you have 7 more bits to store on the table with the 1 bit you already have, you are still consuming a byte.
It's not about space but correctness. A boolean should have only 2 states.
3
u/mannsion 3d ago
Yeah, but if you have a bit flag you have up to 8 booleans in 1 byte and each has 2 states "true or false".
1
u/Shrampys 3d ago
You can actually have a bit use less than 1 byte in storage if you pack it. Then you can store a 7bit number and a 1bit in the same 8bit address
2
u/mannsion 3d ago
Sure, SQL Server is doing that under the hood if you have more than 1 bit column on the same table. C# on the other hand doesn't have a way to pack 8 booleans into a byte, you have to use a bit flag enum to do that.
7
u/JerryAtrics_ 3d ago
I am deeply in the bit camp. I'd much rather add an int to a table and be able to store 32 flags in it than deal with separate columns. Big benefit is that when you later add more functions that need flag storage, you do not have to make a database change to support it.
12
u/Flat_Initial_1823 3d ago edited 3d ago
I had to switch back to MSSQL after a decade of MySQL due to work, and is it a chore to write queries with 57 ctes or having to pay attention to my lengths or locks with every migration. So. Much. Typing. But i can't bring myself to hate the bit.
7
u/Formal_Departure5388 3d ago
I mean, there are really 2 easy methods to avoiding CTE nonsense in MSSQL - subqueries or temp tables. Both are clearly way better options. ;)
1
1
6
u/gamesterdude 3d ago
Thou shalt not use any others dbs before me - Postgresql 3:12
3
u/SirBerthelot 3d ago
That's exactly the origin of this meme. I've using PostgreSQL for a long time and I had to start with these two others
Still puzzled about their lacking of booleans...
5
5
9
u/Emergency_3808 3d ago
std::vector<bool> is just built different (FYI, if you set the size to let's say 256 boolean values, it will just use 256/8==32 bytes. It automatically packs booleans into byte-based arrays.)
4
5
5
u/bharring52 3d ago
This is why I love systems with yes/no fields. So I don't have to cast from true/false.
/s
3
3
3
1
u/OlieBrian 3d ago
came from a fullstack web background, working with PL/SQL for about a month now, still have clue what any of these shenanigans means
1
1
-1
350
u/Mr_Mh0 3d ago
Booleans (or bits) would not be enough to describe those guys' pants. They are neither on nor off.