Timeline for Design concerns for using 3x bit or one char(1) or one integer in table for holding status of item
Current License: CC BY-SA 4.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 8, 2020 at 3:57 | history | edited | Aaron Bertrand | CC BY-SA 4.0 | deleted 33 characters in body |
| Aug 15, 2016 at 17:41 | comment | added | LukeP | Thank you! It did indeed seemed cool and clever to use BITWISE operators, until I had to serialize the field to human readable output and write test cases for it. Came here for confirmation that I made a mistake and I'm reworking the project to use 4 bit fields instead. | |
| Sep 5, 2013 at 19:47 | comment | added | mustaccio | Not sure what you mean by that. Nothing in this world is free. You will spend extra cycles validating your data, whether in the database or in the application, sure. On the other hand, the query optimizer can make use of a check constraint if it exists. | |
| Sep 5, 2013 at 19:43 | comment | added | Aaron Bertrand | @mustaccio Do you think check constraints are free? You have to remember to add them, they have to be included in your source, you have to account for those violations in your error handling, and SQL Server has to actively check each one of them on an insert/update. I'd rather take advantage of the automatic validation that comes with the data type than have to scaffold my own on top of it. All in case I forget that 1 means on and 0 means off? shrug We're just going to have to agree to disagree. | |
| Sep 5, 2013 at 19:42 | comment | added | mustaccio | I'm not really trying make a case for CHAR columns; just want to understand advantages of the alternative. And what's wrong with check constraints? | |
| Sep 5, 2013 at 19:36 | comment | added | Aaron Bertrand | @mustaccio well I think that's a very subjective thing and very hand-wavy. The constraints alone point me the other way. It is also not crazy to change a BIT to TINYINT and use an enum with a foreign key, if you anticipate those kinds of changes. Not every piece of data in a database needs to be understood by the business analyst, especially when those columns are not exposed to them directly, but rather through checkboxes, drop-down lists, etc. I still don't feel you've made a strong case that CHAR(1) has any significant advantage over BIT in this case. | |
| Sep 5, 2013 at 19:31 | comment | added | mustaccio | May be it's not much but maintainability. E.g. after some time you will want allow_returns to indicate not only "1" or "0", but also "yes, if in the original packaging". After some more time you will want to expose this column to the client, be that a human or an XML-based API call. Also, given the current state of education, it's not guaranteed that a future business analyst will understand "1" and "0" as easily as "Y" and "N" when trying to reverse engineer metadata for the then-legacy system... | |
| Sep 5, 2013 at 19:03 | comment | added | Aaron Bertrand | @mustaccio still not clear I understand your insistence on using CHAR(1). As I already pointed out, the OP does not expose these columns to users. And unlike BIT, you have to add check constraints, which are not free. So what does it gain you? | |
| Sep 5, 2013 at 18:59 | comment | added | mustaccio | +1 for informative answer. I'd be interested to see though if in real life all this bit collapsing, compression etc. actually have any performance benefits over good ol' char(1)'s. I would imagine that if I were to index any of those 1-bit columns, or even all three of them, the index key will actually have 1 byte for each column anyway. | |
| Sep 5, 2013 at 17:17 | history | answered | Aaron Bertrand | CC BY-SA 3.0 |