Querying Responses

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bszabo/queryresponse.asp


    Bruce Szabo, MCSE+I, MCDBA, MCSD

  • That was a good article on packing bits into integer fields, I just wanted to add a little something. Before I begin, though, I want to note that this is written with T-SQL for SQL Server in mind, I'm not 100% certain if Access would work the same way, or could be made to work using different operators that mean the same thing.

    When you're pulling the values back out of the packed integer field, you can use the bit-wise operator, "&", to determine which bits are set. So, if you have eight bits packed into an integer, as in your article, and you want to find out if the fifth bit is set, you can use the following code:

    -- assume @packedbits is an integer defined above and set to

    -- the integer you're storing in the database for the check boxes

    declare @box_5 bit

    select @box_5 = (@packedbits & 16)

    You can also use 2^x to select the bit fields, so (@packedbits & 16) is the same as (@packedbits & (2^4)), much like how you generated the listing of values for each bit position in the integer. Note that you must enclose (2^4) in parentheses since the bit-wise operator has a higher precedence in the order of operations.

    The above code does rely on an implicit conversion from int to bit; the actual value returned from (@packedbits & 16) when the fifth bit is set will be 16, but SQL will implicitly convert any non-zero numeric value to 1 before setting your bit variable.

    I think this makes the code a bit more compact and easier to read, particularly if you're setting all 32 bits available in your integer. 🙂

    Matthew Galbraith

  • Thanks for the informantion and the kind words. I never got to play with the bitwise operators as a matter of fact I think I found them after the fact. This project was originally geared towards Access and I added the T-SQL later to round out the article. I will have to spend some more time looking into the bitwise operators.

    Bruce Szabo, MCSE+I, MCDBA, MCSD


    Bruce Szabo, MCSE+I, MCDBA, MCSD

  • While interesting conceptually I think this approach creates more problems than it solves.

    Here are some of the drawbacks I see:

    1. Conceptually complicated - a junior person may not know how to generate a simple report

    2. Very hard to query - requires verbose and complex bitwise logic

    3. What happens if some of the options - say option 3 and 4 - are removed later on but the rest are kept?

    4. What happens if new options must be added in the middle? Sure we can position it as option3 on the form but in reality it's option9 in the database. Becomes confusing when querying.

    I'd love see your thoughts on these. Been looking myself for a good way to store questionnaires/surveys data.

  • I have to agree with your comments. They probably don't have rock solid answers, the answers are more esoteric. We spent some time looking into this topic and we really felt this was a solid approach for our survey so I will try and at least give my thoughts on your points.

    1. The junior person thing probably is a big debate. I actually thought this up and it was implemented by a junior DBA so they can be taught. 🙂 I was lucky he was really bright but for our task we really did an analysis of the data and then we were done. The data was not "at the finger tips" of anyone so it did not need a solid interface to query by example.

    2. I would have agreed with number 2 until I implemented it. It really was not as hard to query as I thought. Plus some of the other solutions I have seen to attack these problems bring up even bigger issues. For example, I have seen tables where there was a bit column for each answer. Although this might suffice your points 1 and 2 it has its drawbacks also.

    3. Point 3 seems the least likely to happen but what we found was nice is that our input routine would still work we would just load the "new" survey into a seperate database but all the supporting load routines would work.

    4. Totally agree with number 4 but the problem at hand did not lend itself to such a situation, thank goodness.

    I probably should give some explanation of what we were doing. We sent a survey via paper to 6000 companies and had them fax the information back. We bar coded the sureveys with their company ids. We were then able to Scan the survey using flatbed scanners and extract the data with some survey software. This extraction yielded a text file which we were then able to load into a database SQL database using Access. Because it was a survey we were not stuck with maintaining the data (i.e. sending out updates with fields in the middle). As you mention this would have made the problem a lot more complex.

    The nature of surveys, however, would not really allow you to change the format of the question and merge results. That in most cases skews the results. The techniques we build from the survey load could be adapted to changing surveys which was good enough in our case.

    I hope this helps. Believe me between getting beat up over scalability and now over the complexity of the queries I am suprised I was able to convince someone to use this method. 🙂 Just kidding it actually worked in our case and we were able to generate a really nice report.

    Bruce Szabo, MCSE+I, MCDBA, MCSD


    Bruce Szabo, MCSE+I, MCDBA, MCSD

  • I think that it's good to educate more people on using INT fields in this way.

    It's a great space saver.

    Although it is much less confusing to use bitwise operators (which I'm sure you'll be investigating soon). Take a look at the syslogins view in the master database and you'll see how it can be a little less confusing and perhaps more efficient.

  • Hey Dan,

    Not sure its a big space saver - doesnt SQL handle bits like nulls internally, uses one byte to store 8 values? Dont have my Inside SQL book handy to check.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • We actually use this bitwise operation concept to store and retrieve data. The concept isn't that difficult to understand for anyone coming from a well rounded CSCI degree. With the & operator, it helps to understand two's complement. For example, if you want to squeeze that 31st bit out of an int, you need to use -(2^31) as the "anding" bit, otherwise you will get an overflow. An easy way to get the binary representation is to convert an int into a varbinary value, assuming you can convert hex to bin. I personally think this method is fun, but may be a little obscure to some. One thing I found is that joining tables with the & operator is fast, but not as fast as a comparison operator (less operations to do the join).

    Eric

  • Wow I never thought about it but yes it seems as though it was a little more fun to do the project using this method. Seems like a strange answer but why not sometimes it is just fun to use a new concept to attack a problem.

    Bruce Szabo, MCSE+I, MCDBA, MCSD


    Bruce Szabo, MCSE+I, MCDBA, MCSD

  • Thank you so much for this method! I'm a brand new PHP/SQL programmer--just learning the ropes--and have spent the last 3 days trying to figure out how to store and query user inputs from checkboxes in a form using PHP and a MySQL database. Storing the users' selections as text strings, or even as separate integers (0,1,2,3,4, etc.), proved extremely difficult and frustrating to query.

    Though the comments made previously in this discussion seem valid, being able to store multiple user inputs as a single integer in the SQL DB makes life so much easier. I solved my problem of 3 days in about 10 minutes after reading your post.

    Many thanks again, and keep up the helpful comments--I could use them! (Though I realize this post is many years old, just thought I'd provide the feedback.)

    Robert Jellinek

    jellinek _AT_ fas _DOT_ harvard _DOT_ edu

Viewing 10 posts - 1 through 9 (of 9 total)

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