0

In the SQL Server binary type, is it possible to store something less than one byte?

binary [ ( n ) ] Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

For example, if I had 8 columns for user preferences, could I store these as 8 separate columns for a combined data size of one byte? Or would this take up 8 bytes. Do any other databases support a bytes object that may be less than a byte (or can be packed with other byte/binary columns?)

David542
  • 104,438
  • 178
  • 489
  • 842
  • Generally speaking the last thing you would worry about is storage. Correctness of your schema should be the first priority. Bit flags are difficult to use in queries and cannot be directly optimized - assuming you need to write tsql queries based on them. Maybe not so important for user preference columns - but you can't predict future needs. And, of course, what happens when the options are changed or expanded. – SMor Nov 10 '20 at 16:09

1 Answers1

1

In the SQL Server binary type, is it possible to store something less than one byte?
For example, if I had 8 columns for user preferences, could I store these as 8 separate columns for a combined data size of one byte?

Yes and yes.

SQL Server will automatically compact multiple bit columns into a single byte:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver15

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

The string values 'TRUE' and 'FALSE' can be converted to bit values: 'TRUE' is converted to 1 and 'FALSE'is converted to 0.

Converting to bit promotes any nonzero value to 1.

Personally I advise against using implicit conversion of 'TRUE' and 'FALSE' string values to bit column values for reasons I won't go into (and I do lament the lack of a "bit-literal" in T-SQL's syntax, though you can always use 1 and 0).

Do any other databases support a bytes object that may be less than a byte (or can be packed with other byte/binary columns?)

(Note that the SQL language does have a built-in nullabe-boolean type which is used to represent the intermediate results of expressions such as EXISTS and predicates in WHERE clauses, but the SQL specification does not require implementations to support boolean as a column type - this is yet another reason why the SQL spec authors need a slapping).

Oracle, famously, still does not support any boolean column types - which is why you see multi-billion-dollar production databases using char(1) to store 'Y' and 'N'.

PostgreSQL supports boolean columns, but their documentation does not state if it will compact bitfield columns or not.

MySQL since version 5.0.3 supports bit and bit(n). Older versions support boolean columns - though it translates the boolean keyword into tinyint(1). MySQL's documentation does not state if it compacts multiple single-bit columns into multiple bytes, however.

Dai
  • 141,631
  • 28
  • 261
  • 374
  • I see, so it's like a `CEILING` function that rounds up to the next byte if its not a round number (multiple of 8), is that correct? – David542 Nov 10 '20 at 05:35
  • @david542 -- `which is why you see multi-billion-dollar production databases using char(1)` -- lol, I use `TINYINT(1)` in mysql5.7. – David542 Nov 10 '20 at 05:36
  • 1
    @David542 Correct, the storage requirement for `n`-many `bit` columns in a table is `ceil( n / 8 )`. – Dai Nov 10 '20 at 05:36