Bitwise & NULL

Question

Bitwise & NULL

Viewing 15 posts - 16 through 30 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Hey. Long time no see. How's things going?

Sioban Krzywicki (12/13/2016)
I want to use a bitwise column because

I'm going to recommend against that. I'm busy (trying to) fix a client system that's got bitmaps all over the place and it's a performance nightmare (because Column & 4 > 0) isn't SARGable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 2

GilaMonster (12/14/2016)
Hey. Long time no see. How's things going?
Sioban Krzywicki (12/13/2016)
I want to use a bitwise column because
I'm going to recommend against that. I'm busy (trying to) fix a client system that's got bitmaps all over the place and it's a performance nightmare (because Column & 4 > 0) isn't SARGable.

Things are going well. New job that I like a lot better. How's things with you?

After multiple people have suggested against I'm going to do a lot of performance testing before proceeding, but I guarantee it is better than what they have now. If not a bitwise, then I'll have to figure something else out. Maybe a table listing the bit columns and an interim table indicating state.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Joe Celko SSCertifiable Points: 5749 More actions · Answer 3

This is usually not a good design. For the painful details read this:

https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 4

What if the bitwise column never appears in the WHERE clause? What if all the values in the column are always returned?

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

drew.allen (12/13/2016)
You're already seeing some of the issues, in that you can't have three-value states.

As previously stated, yes you can. 0, 1, or NULL. Yes, I realize that NULL isn't a "value" but it does allow for 3 tri-state conditions when using the BIT datatype.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Sioban Krzywicki (12/14/2016)
What if the bitwise column never appears in the WHERE clause? What if all the values in the column are always returned?

Then they are not needed.

_____________
Code for TallyGenerator

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 7

Sergiy (12/14/2016)
Sioban Krzywicki (12/14/2016)
What if the bitwise column never appears in the WHERE clause? What if all the values in the column are always returned?
Then they are not needed.

Of course they are. All the values IN the column, not all the values, regardless of whether they're 1 or 0 or NULL.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

CELKO (12/14/2016)
This is usually not a good design. For the painful details read this:
https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/

I agree but you really need to stop writing WHERE clauses like this... 😉

WHERE DATEADD (YEAR, 18, Personnel.birth_date) <= CURRENT_TIMESTAMP

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Sioban Krzywicki (12/14/2016)
Sergiy (12/14/2016)
Sioban Krzywicki (12/14/2016)
What if the bitwise column never appears in the WHERE clause? What if all the values in the column are always returned?
Then they are not needed.
Of course they are.

What for?

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

Sioban Krzywicki (12/14/2016)
What if the bitwise column never appears in the WHERE clause? What if all the values in the column are always returned?

Who's going to read the column without knowing the key? No one, of course, unless they have esoteric knowledge and plenty of practice reading the column. And so you'll end up writing display code to bit-slice the column so you can apply a label to each bit for display purposes. Since up to 8 bits of the BIT datatype are stored in a single byte behind the scenes, there's usually no advantage to bit masking compared to having up to 8 separate bit columns that are clearly labeled with a meaningful column name.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Sioban Krzywicki (12/14/2016)
Things are going well. New job that I like a lot better. How's things with you?

It's been an interesting couple of years (in the Chinese curse sense unfortunately), getting better. Boss has been very supportive, which is nice.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 12

Jeff Moden (12/14/2016)
Sioban Krzywicki (12/14/2016)
What if the bitwise column never appears in the WHERE clause? What if all the values in the column are always returned?
Who's going to read the column without knowing the key? No one, of course, unless they have esoteric knowledge and plenty of practice reading the column. And so you'll end up writing display code to bit-slice the column so you can apply a label to each bit for display purposes. Since up to 8 bits of the BIT datatype are stored in a single byte behind the scenes, there's usually no advantage to bit masking compared to having up to 8 separate bit columns that are clearly labeled with a meaningful column name.

Many of these (this happens for a bunch of different tables, some tables aren't like this) are for internal, programmatic use only. As long as the code knows what to do with the result, that's all that matters.

The main advantage is that we can add and remove new value types at will instead of adding and removing columns.

However, you all have me thinking maybe the best solution for this is a many to many relationship with the bits in the interim table.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Sergiy SSC Guru Points: 110208 More actions · Answer 13

Sioban Krzywicki (12/15/2016)
Jeff Moden (12/14/2016)
Sioban Krzywicki (12/14/2016)
What if the bitwise column never appears in the WHERE clause? What if all the values in the column are always returned?
Who's going to read the column without knowing the key? No one, of course, unless they have esoteric knowledge and plenty of practice reading the column. And so you'll end up writing display code to bit-slice the column so you can apply a label to each bit for display purposes. Since up to 8 bits of the BIT datatype are stored in a single byte behind the scenes, there's usually no advantage to bit masking compared to having up to 8 separate bit columns that are clearly labeled with a meaningful column name.
Many of these (this happens for a bunch of different tables, some tables aren't like this) are for internal, programmatic use only. As long as the code knows what to do with the result, that's all that matters.

Ok, let's put a note here:

Bitwise columns are intended to be used only as blobs - some values having no meaning and no use inside of database.

Interpretation, presentation and updating of those bitwise values will be happening exclusively in front end application code.

If that's the case then bitwise will fit the purpose as good as any other foreign (to relational database) data storage format: XML, JSON, etc.

But I can bet whatever you want - it's not what gonna happen.

Pretty soon you'll see in queries "WHERE Status&8 >0".

That would be the day when you better start looking for another job, where this project could not bite your back.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 14

Sioban Krzywicki (12/15/2016)
Jeff Moden (12/14/2016)
Sioban Krzywicki (12/14/2016)
What if the bitwise column never appears in the WHERE clause? What if all the values in the column are always returned?
Who's going to read the column without knowing the key? No one, of course, unless they have esoteric knowledge and plenty of practice reading the column. And so you'll end up writing display code to bit-slice the column so you can apply a label to each bit for display purposes. Since up to 8 bits of the BIT datatype are stored in a single byte behind the scenes, there's usually no advantage to bit masking compared to having up to 8 separate bit columns that are clearly labeled with a meaningful column name.
Many of these (this happens for a bunch of different tables, some tables aren't like this) are for internal, programmatic use only. As long as the code knows what to do with the result, that's all that matters.
The main advantage is that we can add and remove new value types at will instead of adding and removing columns.
However, you all have me thinking maybe the best solution for this is a many to many relationship with the bits in the interim table.

You've hit upon 1 of 2 exceptions in my mind. The other one would be some form of device control code that you'd pass to the device.

Even as in your case, though, I'm with Sergiy in that you're someday (probably not in the too distant future), you find the column with some bit-wise ANDs or ORs in the WHERE clause of queries for reporting purposes. At the very least, it's highly likely that you'll start finding such a thing in your SELECT lists. If the main thing is to avoid having to add columns, make the column a BIGINT so that you don't have to change from INT to BIGINT when they've finally gone over 32 bits.

We just went through that with something "internal". And, yeah... it's in WHERE clauses and the whole gambit of slothfulness designed by users. It's as bad as an XML, JSON, or CSV column for the developers and worse for performance in a lot of cases. Though I loath it, at least XML can be indexed (if you want to call it that).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76997 More actions · Answer 15

Jeff Moden (12/14/2016)
drew.allen (12/13/2016)
You're already seeing some of the issues, in that you can't have three-value states.
As previously stated, yes you can. 0, 1, or NULL. Yes, I realize that NULL isn't a "value" but it does allow for 3 tri-state conditions when using the BIT datatype.

My statement was about a bitwise field that contained multiple bits not about a single bit.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA