Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Querying Responses Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2002 12:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 12, 2009 9:06 AM
Points: 171, Visits: 6
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
Post #5070
Posted Thursday, June 27, 2002 10:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2008 12:28 PM
Points: 49, Visits: 4
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



Post #36398
Posted Thursday, June 27, 2002 12:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 12, 2009 9:06 AM
Points: 171, Visits: 6
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
Post #36399
Posted Thursday, June 27, 2002 6:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 4, 2004 2:00 PM
Points: 1, Visits: 1
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.











Post #36400
Posted Thursday, June 27, 2002 9:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 12, 2009 9:06 AM
Points: 171, Visits: 6
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
Post #36401
Posted Friday, June 28, 2002 9:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 27, 2012 10:43 PM
Points: 126, Visits: 56
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.




Post #36402
Posted Friday, June 28, 2002 10:20 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 7:55 AM
Points: 6,779, Visits: 1,858
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/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #36403
Posted Friday, June 28, 2002 1:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 20, 2012 12:51 PM
Points: 322, Visits: 351
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




Post #36404
Posted Friday, June 28, 2002 6:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 12, 2009 9:06 AM
Points: 171, Visits: 6
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
Post #36405
Posted Thursday, January 10, 2008 1:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 10, 2008 1:18 PM
Points: 1, Visits: 1
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
Post #441425
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse