I was simply offering a truce on the subject Bruce rather than resigning. It was day 4 in your binary calendar (Wed) and I felt too tired to continue but now it's day 8 (Thur) and you're still going on so here's a quick recap.

Arguments against your bitwise approach:

Obfuscation: When a developer needs to write/debug some code that uses these sprocs they need to do the following steps

• look at the value of the variable being passed
• check what the procedure GetIDsOpenOEveryDayOf actually does when it receives 62
• translate 62 into binary 0111110
• translate 0111110 into days of the week
• read/write the extra code required to utilise the values returned

It's non generic: as your method is reliant on SQL Server functionality.

Long winded: I've probably called your sproc wrongly but it seems like a long winded way of finding out whether a market is open Mon-Fri when a single line of SQL is all that's required. Usually we write sprocs to encapsulate complex logic into a single call rather than create complex logic from, what should be, a simple bit of SQL.

Cost: The original programmer might find the whole concept straightforward but the code will be most likely be maintained by others during it's lifetime. The additional complexity and obfuscation will mean higher maintenance costs and more errors over time.

Arguments for your bitwise approach:

Space saving: In this example we might save 1 or 2 k of disk space. This equates to approx 0.0000002 pence which might not be a useful saving even in these hard times. The separate idea of storing the various opening periods in another table would mean that we'd only be storing 1/250 of the data anyway but the real saving in this case was in making the data easier to maintain.

Performance: The bitwise operations are indeed fast but I suppose that's offset by having to call a stored procedure, access a variable. The bit fields are also unlikely to ever get used as an index.

Job security: They'll need you there just to maintain that code

Have I missed anything? Here's my attempt at some example code using your sprocs but I've probably done this wrong as your methods are obviously very different to mine.

Code:

declare @Days table (
    id int,
    openSun bit,
    openMon bit,
    openTue bit,
    openWed bit,
    openThu bit,
    openFri bit,
    openSat bit,
    openToday bit
)

INSERT @Days (id,openSun,openMon,openTue,openWed,openThu,
              openFri,openSat,openToday)
       exec dbo.GetIDsOpenOEveryDayOf @DayOfWeekMask = 62

SELECT openMon,openTue,openWed,openThu,openFri FROM @Days

You have failed the assignment, young master Kite. Because you did not pay attention to the rubric.
Write four sprocs that mimic the functionality of the four sprocs I gave you, using either of your designs. Then, and only then, if your code is shorter than mine, may you label my method "long winded". I am willing to bet you cannot produce the same results with code as concise as mine.

Not that I think you have any chance of succeeding in this task, but if you do, then you may proceed to assignments two and three:

2) Verify that all the SQL Code in all of your databases is ASCI standard, after which I will allow your complaint that bitwise operators are not generic (MSSQL has them, Oracle has them, MySQL has them, DB2 has them, MS Access can implement them through VB...).

3) Remove all logic and code from your applications that require more than introductory knowledge of SQL, including refactoring all of your precious EAV databases as fact tables, after which your complaints that my bitwise are too obfuscated and costly may deserve a response.

There is absolutely nothing wrong with using bitwise operators in the scenario presented by the poster.

The whole point is that you don't need a sproc to do these things using the other methods:

Code:

-- GetIDsOpenOEveryDayOf(62)
select id from Market
where openMon and openTue and openWed and openThu and openFri

-- GetIDsOpenAnyDayOf(62)
select id from Market
where openMon or openTue or openWed or openThu or openFri

-- GetIDsClosedEveryDayOf(62)
select id from Market
where not ( openMon or openTue or openWed or openThu or openFri )

--GetIDsClosedAnyDayOf(62)
select id from Market
where not ( openMon and openTue and openWed and openThu and openFri )

Each is less code than you'd need just to call one of your sprocs and their logic can be seen at a glance. I can't see why you'd want variables here but if that is required then I'd need a table to pass the days in. Each example above works fine by itself, is easy to understand and doesn't require nearly 30 lines of SQL.

OMG - was there a real question that started this off? did anyone answer it?

I'd still say that the main issue is just the likelihood of errors creeping in using your method - for instance when you created the data in your example table with select floor(rand() * 256) aren't you setting 8 bits each time rather than 7. What does the 8th bit hold or was this just a bug? Would a days value of 62 be the same as 190?

PS How about I let you have one closing rant and then we call it quits

It has and it is...

A few contracts back I suggested using a bitmask for some flag settings (they flat-out refused to consider actual normalization) and I was told by the lead architect that I was one of two developers he'd met who knew how to use bitwise logic. At the time I figured that meant he needs to raise the bar on who he considers to be a developer. Since then I've discovered that understanding bit-anything is indeed becoming a lost art in the development community.

That said, I'd opt for the more traditional "table o' open days" approach for both maintenance and flexibility.

Flexibility: I'm sure all of us are used to battling scope creep... I'd see this requirement and immediately assume that something will eventually come down the pike requiring open and close times or some other day-specific attributes that I'm going to wish I had a way to hook up to my existing model. It smells like a future extensibility point.

Maintenance: I like bitmasks. They're performant, easy to pass around and offer some nice shortcuts to otherwise obtuse code in certain situations. However, it is absolutely true that bitmasks and bitwise logic in general are less than common knowledge. To me, being a developer without a basic understanding of bitmasks is borderline sacrilege. This has a lot to do with the person who brought me in to development. However, the fact of the matter is it's not necessary to learn bit-anything in order to produce something useful with today's languages and therefore many people simply don't bother.

Keeping this in mind, I avoid using bitmasks unless I can wrap them in something "meaningful" like an enumeration. If I were to adopt your bitmask idea on the app side of things, I'd create an enum containing all days of the week and their decimal representation so the developers can reference them as: DaysEnum.Monday, DaysEnum.Tuesday, DaysEnum... etc. For this particular requirement, I think resorting to this would be a suboptimal solution.

If I were tasked with writing high performance code at the expense of all else (doesn't happen often now days), then my opinion sways heavily towards bitmasks.

And that is my point. Without this you can be an utter whiz at bitmasks and bitwise operations but without a "key" the data means nothing.

So, you use enumerates client side to make sense of the data in the database. Brilliant - you now have self documented code. No need to worry whether the designer considered Monday or Sunday to be the first day of the week.
....Except... tight coupling between BE and FE. Should you lose your application then the data in the database makes no sense whatsoever.

My objection is that the meaning of the data is hidden, nothing to do with the operators that act on it.

Exactly what part of "do not hardcode" do you not understand? Following instructions is not your strong point, or you truly do not understand the single line sproc functionality that I posted.
I'll explain:
My sprocs work with ANY COMBINATION of days, because they take the list of days as a PARAMETER.

Your sql code does NOT take any combination of days, because (once again) you have hard-coded the days you want to search.

So, unless you intend to write a stored procedure for every possible combination of seven days of the week, try the exercise one more time, or have the dignity to admit that the bitmask-based solution is much more simple and concise then either of your two methods.