CODEWITHSUNDEEP
sqlsql-serverbitwise-operators
Julien
Julien

Reputation: 23

SQL Server : bitwise or separate fields

I am not a DB expert, and not sure what approach would be the best when it comes to query my table.

I have a table Availability which contains the days of week a specific user is available to work.

I have defined my model like this

     monday: {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
    },
    tuesday: {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
    },
    wednesday: {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
    },
    thursday: {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
    },
    friday: {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
    },
    saturday: {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
    },
    sunday: {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
    },

Now, I am wondering, wouldn't it be faster, when it comes to query for all my user available to work e.g. on Monday, to have a single column which contains a bitwise integer so I can insert it by adding all the values and query on it using the OR bitwise operator in SQL Server?

Thanks for your help :)

Julien.

Upvotes: 2

Views: 1304

Answers (2)

Paul Williams
Paul Williams

Reputation: 17020

Internally, SQL Server implements bit columns as a block of bytes in SQL Server.

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less 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.

BOL Source

I would recommend using bit columns. The database performs all of the bit-wise manipulations for you, so you can write convenient and easy-to-read filter criteria like where [wednesday] = 1.

Also, the schema itself prevents an any attempt to set an invalid value in integer columns like tinyint and smallint. For example, say you setup a tinyint column that allows 7 bits. This limits the values from 0 to 127. A check constraint would be required to prevent someone setting the topmost bit and prevent values between 128 through 255 (inclusive).

Upvotes: 0

thomas
thomas

Reputation: 2642

Are you asking if it is possible or if it is a good approach?

I have done extensive work on a job scheduling tool and my team used the SQL Agent job scheduler in SQL Server as a basis to begin. Under the covers the SQL Agent scheduler is storing the schedule in a table and some of the columns are coded to use bitwise operations against them.

Since there are only 7 days in a week you can use a tinyint data type (1 byte = 8 bits) to store this availability data.

Ob01000000 could be Monday = 64
0b00100000 could be Tuesday = 32
etc.

Then when you pull the data out in a query...

WHERE Availability.AvailableDays | 64 = 64

will give you anyone who works on Monday.

Personally, I like this approach for these types of problems.

Upvotes: 2

Related Questions