Working with a Bit Mapped Data Column

I am working with a column used by the application that is bit mapped. It is defined as VARBINARY and can be up to 288 bits long (though in reality never will be).
- Each bit represents a five-minute interval from a given start time.
- There are exactly enough bits to represent each five-minute interval from the defined start time to a defined end time.
- The default value is all ones. Thus a period of 8:00 AM to 10:00 AM would be represented by '111111111111111111111111'
- If the time-slice of 8:00 AM to 8:15 Am is allocated, the column would be updated to the value of '000111111111111111111111'
- If the time-slice of 9:30 AM to 10:00 AM were also allocated, the column would be updated to the value of '000111111111111111000000'
- If a time-slice is unallocated, the appropriate bits need to be reset back to zeroes.

I would like to create a function that allows me to pass the start time, end time, the current bit-map, a starting time for the time slice, length of time and a bit to indicate I am setting or clearing the value and have it return the appropriate mask.

In the above example, if I wanted to set the 8:00 to 8:15 time-slice I would pass:
SELECT dbo.SetBitMap(0800, 1000, 0800, currentbitmap, 15, 1) -- bitmap defines the time from 0800 (first parameter) to 1000 (second parameter); the current value is currentbitmap (third parameter); I want to deal with a slice that begins at 0800 (fourth parameter); and set a 15 minute block (fifth parameter); the sixth parameter (1) indicates I want to set the bitmap.

Error checking would:
1) make sure the currentbitmap value is sufficient to hold values for start time through end time (e.g. 12 bits for each hour).
2) make sure timeslice start time (fourth parameter) and length (5th parameter) are valid for the bitmap (e.g. I cannot set a block from 9:45 for 20 minutes because that extends past 10:00, the end time defined for the bitmap).
3) if I am trying to set a map, make sure that no bits within the range are already set (e.g. if 9:00 to 9:20 are set and I try to set 8:50 to 9:10, that would be an error--overlapping times).

I am not so concerned about clearing a time-slice and checking to make sure the bits are set prior to clearing them, as I cannot see that having negative ramifications.

I am also not certain how to return an error condition since the return value can be anything from a 1 to 288 bit value, but if someone knows how to do it I would appreciate knowing how. Perhaps I just return the same value that was passed and if they are equal I know an error most likely occurred.

Thanks for your assistance.
LVL 17
Doug BishopDatabase DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Doug BishopDatabase DeveloperAuthor Commented:
p.s. using MS SQL Server 2008 R2.
David ToddSenior Database AdministratorCommented:

Have you considered writing a CLR function to do this?

That is, is strikes me as something that SQL would do rather badly, and C#, Visual Basic, etc could do significantly better ...

Doug BishopDatabase DeveloperAuthor Commented:
Well, sounds great and I'd love to but not sure I've got the experience even with VB to do it. I've coded in VB in the past, but nothing along this level of detail, and never created a CLR function (though I am more than willing to learn). I've never used C# at all.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

David ToddSenior Database AdministratorCommented:

In generally terms, start with the appropriate version and edition of Visual Studio - My guess for SQL 2008 R2 is Visual Studio 2010 at least professional edition.

Start Visual Studio
New Project
Database | SQL Server
Visual Basic SQL CLR Database Project

This post seems to have a good step-by-step approach that explains how to create a simple CLR function.

FWIW I created a CLR function that took the name of the database, a unc path to search, and returned the full unc path and name of the latest backup file. Then used SQL to restore over the network (for dev and test instances). Otherwise standard process was to use an executable to locate and copy the file to the server, then restore from the local copy. Less wasted space on the dev and test servers, good restore performance since backup compression was on, no having to parse a directory listing in SQL that is subject to location and other changes ...

David ToddSenior Database AdministratorCommented:
Hi aikmark,

I suggest adding the MS SQL Server 2008 zone

Doug BishopDatabase DeveloperAuthor Commented:
dtodd, I can probably mangle my way through making the code a CLR function, once I have the code. However, I am basically an application programmer, and this is a bit outside what I am used to doing (actually, quite a bit outside the box). Assistance in creating a VB function to accomplish what I am looking for would be appreciated.
David ToddSenior Database AdministratorCommented:
Hi dbbishop,

From the version of Visual Studio that matches the edition of SQL. That is, where SQL installs BIDS, install the full version of that Visual Studio.

Since you have SQL 2008 R2, that means using Visual Studio 2008.

The types are slightly different.

If you follow the instructions below its fairly easy. Samples in all three major .Net languages.

Doug BishopDatabase DeveloperAuthor Commented:

Again, the issue is the code to accomplish what I am wanting to do (preferrably in VB, as that is what I have the most experience with). I have both VS 2008 and 2010 and running SQL Server 2008 R2. I have been mainly a database developer for 5 years. Prior to that, I was an applicaiton developer in various versions of pre-.NET of VB, with a small amount of hands-on experience with VB.Net (I can muddle through it). As I indicated, manipulating bitmaps such as the problem I've identified above, is far from anything I've ever done before (not much bitmap manipulation in writing an HR system). I am afraid anything I write would be bloatware, very inefficient, and from a data conversion perspective (which is what I am involved with), executing such a function against several million records may bring the system to its knees.


1. Is this column used by application code?  If so, what does that code look like?

2. I have an uneasy feeling about the design/requirements.  Your bitmap may represent an entire 24 hour day, or just some part of it.  Normally, bitmaps are all based on a common origin so that bit-masking operations can be performed.

3. There is no guarantee that the bit-mask being passed in represents the same time span as the time parameters.

4. Do I understand correctly that 1 represents an unallocated slot and 0 represents an allocated slot?

5. Do I understand correctly that the sixth parameter represents the new value for the designated bits in the mask?

6. How 'mature' is this application?
Anthony PerkinsCommented:
Why not "normalize" that value into a cross-reference table that contains one entry for every enabled time range?
Doug BishopDatabase DeveloperAuthor Commented:
The application is mature. I am involved in data conversion. Thus, I have to take data from outside the environment and add it to the application. As part of the conversion, I have to make sure the bitmap is correctly set when an appointment is booked. I will very rarely, if ever, actually clear a booked appointment as part of data conversion, so that part is not really necessary.

A value of 1 does indicate an available slot and 0 indicates a slot that has been taken (#4 above).

In reply to #2 above, the bitmap represets up to 24 hours, but it is defined within a 'SLOT' which is a contiguously available block of time. That time might be 8:00 AM to 11:30 AM, and another slot may represent 12:30 PM to 5:00 PM. As such, there are the appropriate number of bits to handle the available 5-minute slots within the blocks. Since it "is" possible to define a block from 12:00 AM to 11:59 PM, there can be up to 288 bits. However, the first bit always represents the first 5-minute slot within the block.

For #3, I know the start and end time of the block and the bitmap should be the correct length. When the slot is created, the bitmap will be automatically intialized with all 1s.

For #5, the parameters are:
1) BlockStartTime
2) BlockEndTime
3) ApptStartTime
4) CurrentBitmapValue
5) LengthOfApptInMinutes
6) 1 = set the bitmap according to above (may not be required because data conversion shouldn't be clearing any slots).

#6, the application itself is pretty much set, at least in design and should not change.
If this is happening during data import, why not do this with SSIS code?
Doug BishopDatabase DeveloperAuthor Commented:
The import is over. It is now time to do data conversion. You know what, at this point, I don't care if it is done in COBOL as long as I can come up with the code to do it. What parts of ".. the issue is the code to accomplish what I am wanting to do (preferrably in VB, as that is what I have the most experience with)." and "...manipulating bitmaps such as the problem I've identified above, is far from anything I've ever done before (not much bitmap manipulation in writing an HR system). I am afraid anything I write would be bloatware, very inefficient, and from a data conversion perspective (which is what I am involved with), executing such a function against several million records may bring the system to its knees." is everyone having trouble understanding?
So this doesn't have to be a function inside the database?  

You would be satisfied as long as the column gets set after it is imported?

Please post the table definition and some (publicly viewable) table data.
Anthony PerkinsCommented:
>>What parts of "... " is everyone having trouble understanding?<<
Fair enough, I guess you don't need my help.

Good luck.
Doug BishopDatabase DeveloperAuthor Commented:
It needs to be a database function, either T-SQL, which I agree would likely be very difficult, or a CLR function.
I have the data staged into SQL Server, but it has not yet been written to the production database. The staged data is in a table that contains the appointments by individual (millions of rows). It is completely unnormalized.

It needs to be loaded into the production database, which is very normalized. There is a table of appointments. For each individual, it has the individual ID, start and end time of the appointment and appointment type.

There is also a slot table. It defines which appointment types are available during which time periods.

Thus, if I have an appointment type 'A' for individual 123 at 8:30 AM for 15 minutes at location 'B', I need to check the slot table to find the slots that service location 'B' for appointment type 'A' between 8:30 and 8:45 AM. Each slot row has the bitmap column Availablity, which is defined as VARBINARY(288). My function needs to be able to pass the parameters above to the 'candidate' slots and, return the value of the new bitmap value for the first one that has time available (no particular order, just one).

The only column of interest here is the Availibility VARBINARY column. The others will just be members of a JOIN statement or WHERE clause.

I also hate to admit it but this is probably going to have to be RBAR. It is possible to have 4 slots available within the same time period, and four appointments for that time period. When I go to create the second appointment, I need to know that the first appointment has already marked the bits in the first slot, thus it would have to grab the second available slot. If this is done as a single SET-based operation, that would not be known since the transaction has not yet been committed.

Again, the order in which the available slots are grabbed is unimportant. If four slots (rows) show as available for the same time period, I just need to populate one of them.
given your lack of experience and earlier comments, I would recommend that you look at the top25 experts list in this zone (SQL Server) and the .Net zone(s) (C#, VB.Net).  If they have a Hire Me button, use it to contact them about this work.

You are asking for a turnkey solution.  It is possible that you might get a solution in this thread, but you might want to explore your options.

You might want to contact some of the .Net developers in your shop and ask them to collaborate on the solution.  They would augment your limited experience.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Doug BishopDatabase DeveloperAuthor Commented:
I was already in the process of talkiung to one of our .net developers about writing the needed code. I believe that is how I am going to proceed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.