TSQL - Use bitwise to simplify the design

This quick post is because recently one of my colleagues asked for a design suggestion.

We know that we need to follow relational database design principle, that is, normalise the object to its 3rd normalisation form.

But in real world, it is a common practise to have a lookup table to store small amount of records in a relational database design scenario, and it is a common practise to have a junk dimension to merge miscellaneous values in a dimension modelling scenario.

However instead of creating tables, we can use bitwise function to make the design even simpler, like below codes demostrated

use tempdb

--to demostrate how to use bitwise function
declare @Days table (WeekdayName varchar(50), WeekdayNbr int, WeekdayValue int, Selected bit)
declare @Selected int

insert into @Days
values
('Mon', 1, POWER(2, 1), 0),
('Tue', 2, POWER(2, 2), 0),
('Wed', 3, POWER(2, 3), 0),
('Thu', 4, POWER(2, 4), 0),
('Fri', 5, POWER(2, 5), 0),
('Sat', 6, POWER(2, 6), 0),
('Sun', 7, POWER(2, 7), 0)



--assume we want to run the job on Wed and Sun
update @Days set Selected = 1 where WeekdayName in ('wed', 'sun')


select * from @Days

--then the saved weekday value is sum of selected weekday value
select @Selected =  SUM(WeekdayValue) from @Days where Selected = 1
print @selected

--to get bitwise map, & to the weekday value
select @Selected & t.WeekdayValue, WeekdayName
from @Days t


This is not to say we do not need to follow relational database design principle. But when use it properly, the function could be much faster and easier for maintenance.