Things I am presently know is the fact that use bitwise and Int type to keep multiple selection value in Database.

Take Sql Server 2008 like a example, 'Int' key in sql server is 32bit, therefore it accepts 32 answer, I only may use 1,2,4,8,16, etc to represent the solution, because of I have to keep multiple selection into one value, and employ bitwise operation to split up them.

Int: -2^31 (-2,147,483,648) to two^31-1 (2,147,483,647) BigInt: -2^63 (-9,223,372,036,854,775,808) to two^63-1 (9,223,372,036,854,775,807)

No mater Int or BigInt, the amount of response is till possess a limit (32 or 64 or 128).

Same with there every other method to deal this case?

Thank you.

It's most likely a great deal simpler to keep these while using bit data type in SQL Server. MSSQL will compress multiple bit posts into bytes that contains as much as 8 bits. For instance, for those who have 9 bit posts, it will require up 2 bytes total.

But when you've got a really many bits, it will simply make for poor database design to interrupt them out into separate posts by which situation possibly use a fixed-length binary data type which you'll access like a byte array insInternet or VB.

Using bit fields as Josh indicates works, however, you can continue to theoretically achieve column limits.

An alternate is always to possess a separate table which has a row for every mixture of solutions. Each row would have a unique ID, along with a varchar column with a listing of solutions using whatever method you have to identify them - e.g a comma separated listing of answer IDs.

When placing/upgrading the primary table, first search for a row within the answer table that suits the choices. If found you will find the ID, otherwise place the brand new combination and grab the brand new ID. Keep ID within the primary table.

I would recommend for any bigger quantity of potential choices you produce a separate table having a foreign key to the initial table.


Table Customer

customer_id    customer_name
-----------    -------------
          1             Fred
          2           Barney

Table Customer_Options

  option_id   customer_id    option_value
  ---------   -----------    ------------
          1             1               1
          2             1               1
          1             2               0
          2             2               1

The kind of option_value is bit but might be a varchar or anything you need. By doing this the selectable options could be elevated with no massive schema change.